Simple SQL STMT 1

[Mbroad]

I'm a newbie to SQL--I need to do the following. Perhaps you can help:

read table-A and match the zip5 and zip-4 fields against zip5 and zip4 fields of a Table-b (zip code table)--

If found on Table B, write out to table-c the entire table-A record PLUS a jurisidiction code from table-B


Any thoughts!?

thanks very much

Govt Programmer

Government Coder Mark

 

[Zathras]

Something like this:
[tt]
INSERT INTO TableC
SELECT A.*, B.JurisdictionCode
FROM TableA A
INNER JOIN Table B ON A.Zip5 = B.Zip5 AND a.Zip4 = B.Zip4
[/tt]

 

[Mbroad]

Thank you for your post. I coded my statement to follow your example. I am receiving a "correlation error" message (The correlation name 'JURYZIPS_SMALL' has the same exposed name as table 'JURYZIPS_SMALL').

Table names used: TABLEA = SOS_NO_PRIOR, TABLEB = JURYZIPS_SMALL

============================================================
here is the SQL :

INSERT INTO MAX_WITH_CC
SELECT SOS_NO_PRIOR.NAME, SOS_NO_PRIOR.ADDRESS,
SOS_NO_PRIOR.CITY,
SOS_NO_PRIOR.STATE,
SOS_NO_PRIOR.ZIP,
SOS_NO_PRIOR.ZIP4,
SOS_NO_PRIOR.DATEOFBIRTH,
SOS_NO_PRIOR.SEX,
SOS_NO_PRIOR.SID,
JURYZIPS_SMALL.JURIS
FROM SOS_NO_PRIOR JURYZIPS_SMALL
INNER JOIN JURYZIPS_SMALL ON
SOS_NO_PRIORS.ZIP = JURYZIPS_SMALL.ZIP
AND
SOS_NO_PRIOR.ZIP4 = JURYZIPS_SMALL.ZIP4
============================================================
ANY THOUGHTS???

Thanks ---


Government Coder Mark

 

[SQLDenis]

INSERT INTO MAX_WITH_CC
  SELECT SOS_NO_PRIOR.NAME,  SOS_NO_PRIOR.ADDRESS,  
                             SOS_NO_PRIOR.CITY,
                  SOS_NO_PRIOR.STATE,  
                             SOS_NO_PRIOR.ZIP,
                             SOS_NO_PRIOR.ZIP4,          
                            SOS_NO_PRIOR.DATEOFBIRTH,       
                 SOS_NO_PRIOR.SEX, 
                            SOS_NO_PRIOR.SID, 
                            JURYZIPS_SMALL.JURIS
     FROM SOS_NO_PRIOR 
     INNER JOIN JURYZIPS_SMALL ON 
                    SOS_NO_PRIORS.ZIP = JURYZIPS_SMALL.ZIP
                             AND 
                    SOS_NO_PRIOR.ZIP4 = JURYZIPS_SMALL.ZIP4

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Zathras]

That's why I prefer to use only one or two letters for the table_alias id. Something like this:
[tt]
insert into MAX_WITH_CC
select np.NAME, np.ADDRESS, np.CITY, np.STATE,
np.ZIP, np.ZIP4, np.DATEOFBIRTH,
np.SEX, np.SID, jz.JURIS
from SOS_NO_PRIOR np
inner join JURYZIPS_SMALL jz
on np.ZIP = jz.ZIP
and np.ZIP4 = jz.ZIP4
[/tt]
I find that all those underscores make the SQL statement very difficult to read. The computer won't mind, but it gives me a headache.

Also judicious use of case can help make the statement more readable. In the example above I used lower-case for key words and upper-case for table and column names. It could just as easily be the other way around. It really doesn't matter.