Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2006
    Posts
    14

    Unanswered: Retrieving data from 2 tables separately, but in the same query.

    The website I'm working on sells both digital and physical product, so you can either download it or get it delivered. I have a table for each of these (items) and then a table linking the two (sales). I need to pull each item sale and display on a month by month basis. I can't figure out how to grab them all.

    I'm using MySQL 4.1.21

    Here are the tables:
    Code:
    CREATE TABLE `sales` (
    `order_id` smallint(5) NOT NULL auto_increment,
    `customer_id` smallint(5) NOT NULL default '0',
    `date` varchar(10) NOT NULL default '',
    `address_id` tinyint(5) NOT NULL default '0',
    `status` tinyint(1) NOT NULL default '0',
    `ip_address` varchar(15) NOT NULL default '',
    `callback` tinyint(1) NOT NULL default '0',
    PRIMARY KEY (`order_id`)
    ) TYPE=MyISAM AUTO_INCREMENT=3 AUTO_INCREMENT=3 ;
    
    CREATE TABLE `sales_digital` (
    `id` smallint(5) NOT NULL auto_increment,
    `order_id` smallint(5) NOT NULL default '0',
    `cat_no` varchar(10) NOT NULL default '',
    `trk_no` tinyint(2) NOT NULL default '0',
    `disc_no` tinyint(1) NOT NULL default '0',
    `customer_id` smallint(5) NOT NULL default '0',
    `downloaded` tinyint(1) NOT NULL default '0',
    `item_price` decimal(50,2) NOT NULL default '0.00',
    `label_code` char(3) NOT NULL default '',
    `percentage` tinyint(1) NOT NULL default '0',
    `as_bundle` tinyint(1) NOT NULL default '0',
    PRIMARY KEY (`id`)
    ) TYPE=InnoDB AUTO_INCREMENT=31 ;
    
    CREATE TABLE `sales_physical` (
    `id` smallint(5) NOT NULL auto_increment,
    `order_id` smallint(5) NOT NULL default '0',
    `cat_no` varchar(10) NOT NULL default '',
    `customer_id` smallint(5) NOT NULL default '0',
    `quantity` tinyint(2) NOT NULL default '0',
    `item_price` decimal(50,2) NOT NULL default '0.00',
    `label_code` char(3) NOT NULL default '',
    `percentage` tinyint(1) NOT NULL default '0',
    PRIMARY KEY (`id`)
    ) TYPE=InnoDB AUTO_INCREMENT=3 ;
    At the moment I have this query:
    Code:
    $query = "(
    SELECT
    sales.order_id, sales.date,
    sales_digital.label_code, sales_digital.percentage, sales_digital.item_price, sales_digital.cat_no, sales_digital.trk_no,
    sales_digital.disc_no, sales_digital.as_bundle
    FROM
    sales, sales_digital
    WHERE
    sales.order_id = sales_digital.order_id AND sales.status = 2
    )
    UNION
    (
    SELECT
    sales.order_id, sales.date,
    sales_physical.label_code, sales_physical.percentage, sales_physical.item_price, sales_physical.quantity,
    sales_physical.cat_no, sales_physical.customer_id, sales_physical.order_id
    FROM
    sales, sales_physical
    WHERE
    sales.order_id = sales_physical.order_id AND sales.status = 2
    )
    ORDER BY date DESC ";
    But unfortunately that merges the tables together. Thank you so much to anyone who can help shed any light.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in a UNION query, you need to make sure that both SELECTS return exactly the same number and datatype of columns
    Code:
    select sales.order_id
         , sales.date
         , sales_digital.label_code
         , sales_digital.percentage
         , sales_digital.item_price
         , sales_digital.cat_no
         , sales_digital.customer_id
         , 1                           as quantity
         , sales_digital.trk_no
         , sales_digital.disc_no
         , sales_digital.as_bundle
      from sales
    inner
      join sales_digital
        on sales_digital.order_id = sales.order_id
     where sales.status = 2
    UNION ALL
    select sales.order_id
         , sales.date
         , sales_physical.label_code
         , sales_physical.percentage
         , sales_physical.item_price
         , sales_physical.cat_no
         , sales_physical.customer_id
         , sales_physical.quantity
         , null
         , null
         , null
      from sales
    inner
      join sales_physical
        on sales_physical.order_id = sales.order_id
     where sales.status = 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2006
    Posts
    14
    Many thanks for your reply, r937. I've placed it in my application but it's still merging together the results. As an example I have 1 digital sale and 1 physical sale -- only the digital sale is being returned. Any ideas?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can't tell without looking at the data

    try running the SELECTs separately to see why only one row is being returned

    and yes, the results will be "merged" as you say -- if you don't want this, then simply run separate queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by r937
    can't tell without looking at the data

    try running the SELECTs separately to see why only one row is being returned

    and yes, the results will be "merged" as you say -- if you don't want this, then simply run separate queries
    ...or consider a revised table physical design which groups together common elements and uses sub table(s) for unique information.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2006
    Posts
    14
    The selects work as single queries. I cannot split this query into two separate queries as I need to sort the data before retrieving it from the database.

    I've attached the data for you, if you would like to have a look.
    Attached Files Attached Files

  7. #7
    Join Date
    Nov 2006
    Posts
    14
    Quote Originally Posted by healdem
    ...or consider a revised table physical design which groups together common elements and uses sub table(s) for unique information.
    Yes, I could do that. I'm hoping I don't have to, because that would mean restructuring quite a few queries and then converting the live database. That scares me.

  8. #8
    Join Date
    Nov 2006
    Posts
    5

    MySQL newb question

    edit: oops...

  9. #9
    Join Date
    Nov 2006
    Posts
    14
    Would anybody be able to help me?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you said "the selects work as single queries"

    therefore they should work in the UNION
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2006
    Posts
    14
    Woah, it's working now! r937, genuis.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •