sql query redundant returns

spoody_goon · 03-18-2004, 08:39 PM

I need help with a mysql query I am not getting the results I want.
I have two tables
booths(booths , dealer_id)
dealer(dealer_id , first_name, last_name, phone, email)

booths.dealer_id and dealer.dealer_id are not dependant on each other.
In other words there can be a booths with out a dealer_id.

I a query to get all the booths and where there are dealer that goes with it I want to print out that info too.

e.g.
booths1, dealer1, dealer name
booths2, dealer2, dealer name
booths3, NULL
booths4 NULL

I don't mind doing this with two queries I just don't know how to do this without getting redundant infor.
My current queries are:
SELECT booths.booths, dealer.dealer_id, dealer.first_name, dealer.last_name FROM booths,dealer WHERE dealer.dealer_id=booths.dealer_id
and
SELECT * FROM booths

This give redundant info like this:
booths1, dealer1, dealer name
booths2, dealer2, dealer name
booths1, dealer1
booths2, dealer2
booths3, NULL
booths4 NULL

Thanks for your help

vi0lat0r · 03-19-2004, 04:28 AM

After selecting them all you can assort them into an array... and then do something like...

Code:

echo $i[1];
echo $i[3];
echo $i[5];

spoody_goon · 03-19-2004, 08:28 PM

Thanks vi0lat0r but I was hoping for something in a sql query language solution. I will try a couple other ideas and if I come accross something worth while I will post it.
Any one else have an idea?

crabboy · 03-19-2004, 09:11 PM

You need an outer join:

Code:

mysql> select t1.booths, t2.dealer_id, t2.first_name from booths t1 left outer join dealers t2 using (dealer_id);
+---------+-----------+------------+
| booths  | dealer_id | first_name |
+---------+-----------+------------+
| booths1 | dealer1   | dealer1    |
| booths2 | dealer2   | dealer2    |
| booths3 | NULL      | NULL       |
+---------+-----------+------------+
3 rows in set (0.00 sec)

spoody_goon · 03-19-2004, 09:44 PM

Excellent crabboy thanks for the sql lesson!