Join on multiple columns?

[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.

 

[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.