SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Joining Three Tables?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

scm22ri
Starting Member

USA
3 Posts

Posted - 01/13/2013 :  09:54:56  Show Profile  Reply with Quote
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

3334 Posts

Posted - 01/13/2013 :  10:23:28  Show Profile  Reply with Quote
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

USA
3 Posts

Posted - 01/13/2013 :  14:03:12  Show Profile  Reply with Quote
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 - 01/13/2013 :  14:09:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3334 Posts

Posted - 01/13/2013 :  14:27:09  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000