MySQL Select Data from Multiple Unrelated Tables

Question

I have a page on my site where I would like to list out the separate instances in which a customer ordered a part or brought their bike in for service. The data for each of these is contained in separate tables. Ultimately I'll be sorting these by date and using ORDER BY and COALESCE to make this work, but first I have to pull the data and I can't figure out how to do it.

Since the two tables are unrelated I won't be using a JOIN to combine data and I thought I might use a UNION but then learned that the data and number of columns for unions needs to be similar. I saw someone somewhere just throwing up a comma but I can't get mine to work.

Here's what I've got:

$result = mysql_query("SELECT 
        p.part_id,
        p.part_num,
        p.descr,
        p.vendor,
        p.date_entered,
        p.date_ordered,
        p.date_rcvd,
        s.serv_id,
        s.make,
        s.model,
        s.yr,
        s.vin,
        s.mileage,
        s.in_date,
        s.out_date
    FROM parts p, services s 
    WHERE cust_id = '$cust_id'");

if (mysql_num_rows($result) == 0) {
    $transactions = array();
} else {
    while ($row = mysql_fetch_assoc($result)) {
        $transactions[] = $row;
    }
}

Later on

<? foreach ($transactions as $transaction): ?>
    <? if($transaction['part_id'] && $transaction['part_id'] != "") { ?>
        [DISPLAY PART INFO]
    <? } elseif($transaction['serv_id'] && $transaction['serv_id'] != "" { ?>
        [DISPLAY SERVICE INFO]
    <? } ?>
<? endforeach; ?>

Any ideas?

I would advise against short tags. http://stackoverflow.com/questions/200640/are-php-short-tags-acceptable-to-use — Kermit, Jul 26 '12 at 18:05
Also, as far as I can tell, those two tables are completely unrelated. Your current query will give you the Cartesian product, which is not what you want. I would try to find a common key to join on. — Kermit, Jul 26 '12 at 18:07
Without a sample dataset to go on, we're stabbing in the dark. But what's wrong with just doing two separate queries if the tables are unrelated? — lc., Jul 26 '12 at 18:08
It looks like a transaction can either be for a part or a service. Is there a `cust_id` column in both `parts` and `services` tables? — Andrew, Jul 26 '12 at 18:09
Please, don't use `mysql_*` functions for new code. They are no longer maintained and the community has begun the [deprecation process](http://goo.gl/KJveJ). See the [**red box**](http://goo.gl/GPmFd)? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide, [this article](http://goo.gl/3gqF9) will help to choose. If you care to learn, [here is good PDO tutorial](http://goo.gl/vFWnC). — Madara's Ghost, Jul 26 '12 at 18:17
Not 'similar'... equal. The column count must be equal... also, it would be good to be careful that the data type of each column is also the same — Barranka, Jul 26 '12 at 18:29
@Vatev the result is supposed to list out the service and parts transactions by date, which is why I was attempting to use a single query. — Nick Hogan, Jul 26 '12 at 18:55
@barranka Yes. Column count has to be equal data has to be similar type. phrasing on that took me a little off course. my bad — Nick Hogan, Jul 26 '12 at 19:00

score 2 · Answer 1 · answered Jul 26 '12 at 18:18

2

Waht about using null columns ? Ih the first table has columns name, date, pieces and the second name_cust, date_bike_entered you could do

Select name, date, pieces, null, null from table1
Union All
Select null, null, null, name_cust, date_bike_entered from table2

answered Jul 26 '12 at 18:18

Carlos

21
1

This is intriguing. Two questions: 1) are there any downsides to this? would it be considered sloppy? 2) How would I got about sorting the combined data by date? – Nick Hogan Jul 26 '12 at 19:01

score 0 · Answer 2 · answered Jul 26 '12 at 18:13

Solution A

You will need two queries to handle a request for (1) parts and (2) services. You will need some logic to differentiate the two.

Solution B

SELECT COUNT(*) FROM parts WHERE part_id = $transaction['part_id']
SELECT COUNT(*) FROM services WHERE serv_id = $transaction['serv_id']

This will give you the number of results for each. Add logic to pull data based on the result. However, you'll need logic to handle if you have results in both queries.

Solution C

Have a radio form that allows only part_id or serv_id, and query based on the user's selection.

MySQL Select Data from Multiple Unrelated Tables

2 Answers2