Join returning 2 records for each record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Join returning 2 records for each record

Author

Topic

MKz71
Starting Member

30 Posts

Posted - 2011-10-31 : 16:06:11

I have this stored procedure that is returning 2 identicle records for every record in the DB table. Anyone have any ideas on how I can fix this?


PROCEDURE [TRK].[spGetReportData] 
	@StartDate datetime,
	@EndDate datetime

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT DISTINCT s.*, l.HEAT_NO
	FROM [TRK].InProcessStencil s JOIN
		[TRK].CoilRecords l ON SUBSTRING(l.LOT_NO,0,5) = SUBSTRING(s.Lot,0,5) 
	WHERE (s.StencilTime BETWEEN @StartDate AND @EndDate)
Order by s.StencilTime DESC
END

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-31 : 17:52:52

no it isn't - there's something different or the distinct would merge them.
joining on the substring means that there is probably a problem with the database design.

JUst take two of the rows and see why one isn't being eliminated.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-10-31 : 21:54:51

nigelrivett is correct in that there is a difference in the records, if they were the same the distinct would ensure only 1 record was shown. My assumption is simply that in your linking table COILRECORDS, there are two matching records with different HEAT_NO. To verify just use this query, and you should get 1 result.

SELECT DISTINCT s.*
FROM [TRK].InProcessStencil s
inner JOIN
[TRK].CoilRecords l ON SUBSTRING(l.LOT_NO,0,5) = SUBSTRING(s.Lot,0,5)
WHERE (s.StencilTime BETWEEN @StartDate AND @EndDate)

if that works, then your issue is simply to determine what Heat_NO you want to return, if it doesn't matter, just do


SELECT top 1 DISTINCT s.*, l.HEAT_NO
	FROM [TRK].InProcessStencil s JOIN
		[TRK].CoilRecords l ON SUBSTRING(l.LOT_NO,0,5) = SUBSTRING(s.Lot,0,5) 
	WHERE (s.StencilTime BETWEEN @StartDate AND @EndDate)
Order by s.StencilTime DESC

Or if you need other logic, and require assistance in how to achieve, just post the logic and I'm sure someone will help you.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Sachin.Nand

2937 Posts

Posted - 2011-11-01 : 02:35:22

Cant just an correlated subquery with an EXISTS do ?

If you have many to many relationship between InProcessStencil and CoilRecords,an Exists will return matching row for each row on the outer table but if you have one to many relationship then Exists will return a single row from the outer table i.e InProcessStencil for each matching row in CoilRecords irrespective of how many rows exists in the inner table.

PBUH

MKz71
Starting Member

30 Posts

Posted - 2011-11-01 : 07:15:28

Well... the thing is, there is no HEAT_NO in the InProcessStencil table. So I have to do the join to get a matching HEAT_NO based on the Lot from both tables. InProcessStencil is the table that has everything I am actually looking for, there may be multiple matches of the HEAT_NO in the CoilRecords table, but I only need to retrieve one. I can't do a TOP 1 DISTINCT because that would only yield a single record and the report I am writing may require multiple records from the InProcessStencil table. Anyone have any ideas on how I can achieve this?

MKz71
Starting Member

30 Posts

Posted - 2011-11-01 : 07:19:07

I would also like to add that the InProcessStencil table currently is not in production and has two dummy records in it for testing only. They are two records that are identical but with different time stamps. These two records are being doubled when they are returned by the stored procedure. Meaning |Record 1| |Record 1| |Record 2| |Record 2| is being returned.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-01 : 07:31:46

>> They are two records that are identical but with different time stamps
So not identical then.

The SUBSTRING(l.LOT_NO,0,5) = SUBSTRING(s.Lot,0,5)
are the same which is causing the duplication.

Now the question is - which rows do you want to join?
maybe
FROM
(select *, seq = row_number() over (partition by SUBSTRING(s.Lot,0,5) order by StencilTime) from [TRK].InProcessStencil s WHERE s.StencilTime BETWEEN @StartDate AND @EndDate) s
JOIN [TRK].CoilRecords l ON SUBSTRING(l.LOT_NO,0,5) = SUBSTRING(s.Lot,0,5)
WHERE s.seq = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MKz71
Starting Member

30 Posts

Posted - 2011-11-01 : 08:09:34

Yes the 'but' in the sentince was me identifying it as not 100% identical. This may be done without using a join at all. I simply want to return all of the records between a date range within a table and return the HEAT_NO from another table that corrisponds with the individual records. Is this possible?

sudo code example: (Where ID is a random incriment)
table 1:
ID Name Date

table 2:
ID Name Age

Return value:
Select table 1.*, table 2.Age

ID Name Date Age
.....|_________|
.............|
Age aligns with the matching name from table 1

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-11-01 : 10:15:44

pseudo answer:

select 
	t1.ID, t1.Name, t1.Date, t2.Age
from
	table1 t1
inner join
	table2 t2
on
	t1.ID = t2.ID
and
	t1.Name = t2.Name

Please mark this as sarcastic if it helps you.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

MKz71
Starting Member

30 Posts

Posted - 2011-11-01 : 10:41:16

Believe it or not... what you put did help. I removed the substring part of my original join and also added an additional comparison between the tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources