Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Joining Three Tables?

Author  Topic 

scm22ri
Starting Member

3 Posts

Posted - 2013-01-13 : 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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-13 : 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.
Go to Top of Page

scm22ri
Starting Member

3 Posts

Posted - 2013-01-13 : 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?
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 2013-01-13 : 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.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-13 : 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!!
Go to Top of Page
   

- Advertisement -