SQLTeam.com | Weblogs | Forums

SQL join query with a where clause


I have 2 tables both of which contain the column part number I wan to return all lines from table 1 based on where the part number are equal but also where the LOC column in table 2 = 5D. Sounds simply but it is return too many rows. Here is the code I have started with and if someone could put me on the correct track I would be appreciated.

Select [Receiver Log].[Employee Name], [Receiver Log].[Part Number], [Receiver Log].Location, 
[Receiver Log].[Date and Time], [Receiver Log].[Override]
FROM [Receiver Log]
LEFT JOIN Locator on [Receiver Log].[Part Number] = Locator.[PART NUMBER]
WHERE [Locator].LOC = '5D'  


Without knowing your data, my guess is, you'd want to use "exists" - something like this:

select [Employee Name]
      ,[Part Number]
      ,[Date and Time]
  from [Receiver Log]
 where exists (select 1
                 from Locator
                where Locator.[PART NUMBER]=[Receiver Log].[Part Number]
                  and Locator.LOC='5D'

Edit: no need for table reference in select part.


That returns the same 61 rows as the original statement, there should be only 12 rows returned. I don't know what else to post to help in understanding.


Please provide:

  • database engine and version
  • table descriptions as create statements
  • sample data as insert statements
  • expected output from the sample data you provide


Does this mean you want ALL rows from table 1 or just the matching?


Table 1 (Receivers)is populated using an ASP web app in two locations Location 85 in MS and 5D in CT.

Table 2 (Locator) contains all part numbers (Part Number), part locations (LOCATOR) , and plant locations (LOC).

I want to return all rows from the receivers table that belong to the 5D plant. My thought process( obviously flawed) was to link the two tables together based on the Part Number field and return only those designated 5D in LOC column in the Locator table.


What happens if you change your LEFT JOIN to an INNER JOIN?


Same result returns 61 rows. There should only be 12


Could you provide sample input and output?


I don't know if this is helpful or not but the table on the top left is the locator file that has the designation LOC (5D or 85). Top right is a shot of the receivers log that contains the Name, part number, location, and the date and time is automatically determined and populated. The bottom is the results from the current query. I am only looking to return those rows that have a matching part number in the locator table receiver table that has a 5D designation.

With this test data there should only be 12 rows.


Generally we'd prefer a CREATE TABLE statement and some INSERT statements to populate it with some sample data. That way anyone where who wants to can cut & paste your sample and generate an answer AND easily test that it works OK. Pictures mean that each and every person that wants to help you has to do all that work, in duplicate.

For me, today, I don't have the time. You will probably find that most people are in that same boat, whereas with sample data I might only need a few seconds to generate an answer


I'm going to guess here - are you looking for newest [Reveiver Log] records only, in the sence that there can only be one partno per location, and you then want the newest?
If so, this might the you closer to a solution:

select [Employee Name]
      ,[Part Number]
      ,[Date and Time]
  from (select [Employee Name]
              ,[Part Number]
              ,[Date and Time]
              ,row_number() over(partition by Location
                                 order by [Date and Time] desc
               as rn
          from [Receiver Log]
         where exists (select 1
                         from Locator
                        where Locator.[PART NUMBER]=[Receiver Log].[Part Number]
                          and Locator.LOC='5D'
       ) as t
 where rn=1


As others have mentioned, it is difficult to really test without sample tables and data we can query.

From when you have given it looks like the Locator table is (possibly) unique for a given LOCATOR/PART NUMBER. Since you are given both of those in your Receiver Log table, you should use both in the join:

	rl.[Employee Name], 
	rl.[Part Number], 
	rl.[Date and Time], 
FROM [Receiver Log] rl
INNER JOIN Locator l
	ON rl.Location = l.LOCATOR
	AND rl.[Part Number] = l.[PART NUMBER]
	AND l.LOC = '5D'

Does that come closer to what you are looking for?

If, in your original query, you included the data from the Locator table as well, you should be able to see what was causing the duplicates.