In Select statement? 3

[StayAtHomeMom]

I’m confusing myself! All I’m trying to do is a Select Query from a large table with a matching field in another table. My large table is SalesDetail, and my other table is MexicanZips. There are too many Mexican Zips for me to place in the criteria row of my Select Query, so I put them in a separate table. I’m trying to select records from the SalesDetail table where the Zip field matches one of the zips in the MexicanZip table. I believe I need to use some kind of “In (SELECT …) statement in my query, but I’m messing the syntax up and I can’t find help in the Help Window.

Got any suggestions? THANK YOU!!

 

[SkipVought]

Please post your SQL.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[StayAtHomeMom]

Here is my SQL, even though I know it's wrong:

SELECT [SalesDetail].TerrNo AS [Terr No]
FROM [SalesDetail] INNER JOIN MexicanZips ON [SalesDetail].CustZIP = MexicanZips.BorderZip
WHERE ((([SalesDetail].CustZIP) In (SELECT [BorderZip] FROM [MexicanZips] As Tmp )))
GROUP BY [SalesDetail].TerrNo;

 

[SkipVought]

SELECT [SalesDetail].TerrNo AS [Terr No]

FROM  [SalesDetail] 
INNER JOIN MexicanZips 
   ON [SalesDetail].CustZIP = MexicanZips.BorderZip

GROUP BY [SalesDetail].TerrNo;

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

SELECT [SalesDetail].TerrNo AS [Terr No]

FROM  [SalesDetail] 
INNER JOIN MexicanZips 
   ON [SalesDetail].CustZIP = MexicanZips.BorderZip

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lespaul]

by doing the INNER JOIN you are effectively saying "bring me all the records from SalesDetail that have a CustZIP equal to MexicanZips.BorderZip":


all you need is:

SELECT [SalesDetail].TerrNo AS [Terr No]
FROM [SalesDetail] INNER JOIN MexicanZips ON [SalesDetail].CustZIP = MexicanZips.BorderZip

be sure to read the Understanding SQL Join link PHV posted above.

Leslie

Have you met Hardy Heron?

 

[StayAtHomeMom]

It worked!! Thank you for clearing the cobwebs!! And thank you PHV for the link about Joins. I will bookmark it for future reference.

You guys are THE BEST!!