Join on multiple columns?

N

N Lee

Greetings,

I have two tables of about 10 columns each, which share 5 common
columns.

Is there a way to join the tables together, and join then on those 5
common columns?
I have one table of 12 columns, and one table of 8 columns. 5 of those
columns are the same. I would want a query that would give me an
output with 15 columns.

Here's a small example, to be viewed in a monospaced font:

Table 1
+------------+------------+------------+------------+------------+
| City | State | Street | License # | Car Type |
+------------+------------+------------+------------+------------+
| Cincinnati | Ohio | Vine St. | RT555000 | Volvo |
| Syracuse | New York | Main St. | VM8004220 | Ford |
| San Jose | California | Alameda | 8209031 | Jeep |
+------------+------------+------------+------------+------------+

Table 2
+------------+------------+------------+------------+
| License # | Car Type | Infraction | Fine |
+------------+------------+------------+------------+
| VM8004220 | Ford | Speeding | $50 |
| 8209031 | Jeep | Expired Lic| $100 |
| QQ5002 | Lincoln | No signal | $25 |
+------------+------------+------------+------------+


Desired Query Output
+------------+------------+------------+------------+------------
+------------+------------+
| City | State | Street | License # | Car Type |
Infraction | Fine |
+------------+------------+------------+------------+------------
+------------+------------+
| Cincinnati | Ohio | Vine St. | RT555000 | Volvo |
(null) | (null) |
| Syracuse | New York | Main St. | VM8004220 | Ford |
Speeding | $50 |
| San Jose | California | Alameda | 8209031 | Jeep |
Expired Lic| $100 |
| (null) | (null) | (null) | QQ5002 | Lincoln | No
signal | $25 |
+------------+------------+------------+------------+------------
+------------+------------+


The join is done on "License #" and "Car Type".

Thanks for any help.
 
D

Dale Fye

I could understand joining on License# and State, but car type? The first
question I would ask is, which table contains the data you want to display,
if there is no matching data in the other table? I'll assume it is Table1 in
your example.

SELECT Table1.City, Table1.State, Table1.Street, Table1.License#,
Table1.Car_Type, Table2.Infraction, Table2.Fine
FROM Table1 LEFT JOIN Table2
ON Table1.License# = Table2.License#
AND Table1.Car_Type = Table2.Car_Type

This would give you a list of all of the cars, regardless if they had an
infraction, but would display NULLs in the Infraction and Fine columns if
they had none. If you only want to identify those vehicles with an
infraction, change "Left JOIN" above to "INNER JOIN".

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
N

N Lee

I could understand joining on License# and State, but car type? The first
question I would ask is, which table contains the data you want to display,
if there is no matching data in the other table? I'll assume it is Table1 in
your example.

SELECT Table1.City, Table1.State, Table1.Street, Table1.License#,
Table1.Car_Type, Table2.Infraction, Table2.Fine
FROM Table1 LEFT JOIN Table2
ON Table1.License# = Table2.License#
AND Table1.Car_Type = Table2.Car_Type

This would give you a list of all of the cars, regardless if they had an
infraction, but would display NULLs in the Infraction and Fine columns if
they had none. If you only want to identify those vehicles with an
infraction, change "Left JOIN" above to "INNER JOIN".

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
Click to expand...

That's not quite what I'm looking for - that solution does give all of
the Table 1 entries (city, state, street, license #, car type), but it
does not give all of the Table 2 entries. It did lead me in the right
direction, though. I had to do an INNER JOIN, and UNION that with a
LEFT JOIN and a RIGHT JOIN, like you did above:

SELECT Table1.City, Table1.State, Table1.Street, Table1.Lic,
Table1.Car, Table2.Infraction, Table2.Fine
FROM Table2 INNER JOIN Table1 ON (Table2.Lic=Table1.Lic) AND
(Table2.Car=Table1.Car)
UNION ALL
SELECT Table1.City, Table1.State, Table1.Street, Table1.Lic,
Table1.Car, Table2.Infraction, Table2.Fine
From Table1 LEFT JOIN Table2 ON (Table1.Lic=Table2.Lic) AND
(Table1.Car=Table2.Car)
WHERE Table2.Infraction IS NULL
UNION ALL SELECT Table1.City, Table1.State, Table1.Street, Table2.Lic,
Table2.Car, Table2.Infraction, Table2.Fine
FROM Table1 RIGHT JOIN Table2 ON (Table1.Lic=Table2.Lic) AND
(Table1.Car=Table2.Car)
WHERE Table1.City IS NULL;
 
D

Dale Fye

Actually, if you want to identify all the records from Table1 and Table2, and
where they match, you only need the second and third queries from the query
you wrote below. The one with the left Join will give you all of the records
from Table1, and the matching values from Table2. The Third on will give you
all of the records in Table2 that don't have a match in Table1.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Top