| Author |
 Topic  |
|
|
scm22ri
Starting Member
USA
3 Posts |
 Posted - 01/13/2013 : 09:54:56
|
Hi Everyone,
I'm new to this forum. I have a question regarding joining 3 tables. Two of the tables have a common ID but the other table does not.
What would be the best way to join these tables?
|
|
|
James K
Flowing Fount of Yak Knowledge
1511 Posts |
Posted - 01/13/2013 : 10:23:28
|
HOW you should join depends on WHY you are joining the tables (in particular, what the reason for joining to the third table is). To put it another way, for a given row in the first table (or a row in the result of the first table joined to the second table), what is the information that you are trying to get from the third table for that specific row?
If you post the DDL for the tables (column names etc.), sample data in the tables, and the corresponding output you are trying to get, that would make it easier to suggest how you should join the tables. |
 |
|
|
scm22ri
Starting Member
USA
3 Posts |
Posted - 01/13/2013 : 14:03:12
|
Hi James,
Ok, here's an example
My first table is called
tbl_car_description
this table has: id, year, make, model
members
this table has: id, username, password
tbl_car_selling
this table has: id, cardescription_id, caruser_id, price, exterior_color, interior_color, engine, mileage, transmission, gas_type, state, city
dealers (this is the table that nothing in common with the above three)
this table has: id, dealership, address, state, city, zip, phone, website
I know how to join two tables (below)
SELECT d.id, d.year, d.make, d.model, p.price, p.exterior_color, p.interior_color, p.engine, p.mileage, p.transmission, p.gas_type, p.state, p.city
FROM tbl_car_description d, tbl_car_prices p
WHERE (d.id = p.cardescription_id)
AND (p.state = 'state')
AND (p.city = 'city')
AND (d.year = 'year')
AND (d.make = 'make')
AND (d.model = 'model')
AND (p.approve = '1')
How would I join the "dealers" table onto the above table? |
|
|
|
Robowski
Starting Member
48 Posts |
Posted - 01/13/2013 : 14:09:47
|
quote:
Originally posted by scm22ri
Hi James,
Ok, here's an example
My first table is called
tbl_car_description
this table has: id, year, make, model
members
this table has: id, username, password
tbl_car_selling
this table has: id, cardescription_id, caruser_id, price, exterior_color, interior_color, engine, mileage, transmission, gas_type, state, city
dealers (this is the table that nothing in common with the above three)
this table has: id, dealership, address, state, city, zip, phone, website
I know how to join two tables (below)
SELECT d.id, d.year, d.make, d.model, p.price, p.exterior_color, p.interior_color, p.engine, p.mileage, p.transmission, p.gas_type, p.state, p.city
FROM tbl_car_description d, tbl_car_prices p
WHERE (d.id = p.cardescription_id)
AND (p.state = 'state')
AND (p.city = 'city')
AND (d.year = 'year')
AND (d.make = 'make')
AND (d.model = 'model')
AND (p.approve = '1')
How would I join the "dealers" table onto the above table?
If the Table 'Dealers' has nothing in common why are you trying to join it?
If you return the result as your above query is written, what dealer details per line do you want it to show?
If you are trying to return the car details and what dealer the car is in or came from it sounds like you need to create a FK from one of the tables into the dealer table and then join on that. |
|
|
|
James K
Flowing Fount of Yak Knowledge
1511 Posts |
Posted - 01/13/2013 : 14:27:09
|
Check in your database to see if there is a link table that can be used to associate cars with dealerships. I am expecting that as a car is delivered to a dealership, there would be an entry associating that car with the specific dealership in that table. So it would have at least two columns - the car_id and the dealership_id. There may be other columns - for example the date the car was delivered to the dealership etc.
If you have such a table, then you would join the car table to the link table using the car_id and then link that to the dealership using the dealership_id.
Of course, that is a simplified view of the world. Cars may be delivered from one dealership to another, may go to fleet sales directly from the manufacturer - in which case there would be no dealership involved etc. But I am digressing.
PS: I am NOT a used car salesman, and never been one. REALLY!! |
|
|
| |
Topic |
|
|
|
Joining Three Tables?
