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

Triune · #1 (**permalink**) 11-06-06, 04:24

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.

r937 · #2 (**permalink**) 11-06-06, 07:02

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

Triune · #3 (**permalink**) 11-06-06, 07:09

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?

r937 · #4 (**permalink**) 11-06-06, 07:32

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

healdem · #5 (**permalink**) 11-06-06, 07:38

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.

Triune · #6 (**permalink**) 11-06-06, 07:41

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.

Triune · #7 (**permalink**) 11-06-06, 07:43

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.