Simmple SQL Join Qn

[callnishant]

Hi,
I am trying to make a join between two tables but finding it tough.
Tables are like:

FIPS Table:
-------------
State CountyCode
PA 12345
MI 77777

RULES Table:
--------------
State CountyCode
PA 88888
PA 00000


Now, when I try to make a join between the Rules and Fips table on columns State and CountyCode, since there is no countycode of 12345 in Rules table for the State PA, it doesn't make a match. In this situation, I would like it to join automatically using the CountyCode value of 00000, (Which is the code for -"Rest of the Counties") as seen in the Rules Table.

Could someone please help me with this?

Thanks in advance!

Nishant

 

[rac2]

SELECT f.*, r.*
FROM FIPS f 
JOIN Rules r ON  r.State = f.State AND r.CountyCode = f.CountyCode 

UNION

SELECT f.*, r2.*
FROM FIPS f 
LEFT JOIN Rules r ON r.State = f.State AND r.CountyCode = f.CountyCode 
JOIN Rules r2 ON r.State = 'PA' AND r.CountyCode = '00000' )
WHERE r.id IS NULL

Why would this work?
The first query gives the easy answers where there is a rule for the county.
The second query might work like this.
The LEFT JOIN will give a row for every county.
The WHERE clause limits the result to counties which have no rules. I used r.id to stand for any column in the Rules table.
The second JOIN to the default row in the Rules table gives that row for any counties without rules.

Just an idea. Let me know what you find.

 

[rac2]

Oops. That second JOIN should be

JOIN Rules r2 ON r2.State = 'PA' AND r2.CountyCode = '00000' )