INNER JOIN and too many rows - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

INNER JOIN and too many rows

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-22 : 14:04:12

Getting too many rows back for this one.
It should only be 2 rows.

Here's the problem table structure:

CREATE TABLE [dbo].[ImportAlertTrackingNumbers] (
ImportAlertTrackNumID	INT			IDENTITY(1,1) CONSTRAINT PK_ImportAlertTrackingNumbers PRIMARY KEY NOT NULL,
ImportAlertID	INT	NOT NULL
		CONSTRAINT FK_ImportAlertID_ImportAlertTrackingNumbers FOREIGN KEY (ImportAlertID)
		REFERENCES ImportAlert (ImportAlertID),
InvImportAlertIDINT	NULL

The InvImportAlertID is populated by dbo.inv.ImportAlertID
And the ImportAlertID by the contraint above (ImportAlert)

Currently, the table ImportAlertTrackingNumbers has this:

1 2 1
2 2 1
3 2 1
4 2 1
5 2 1
6 2 1
7 3 1
8 3 1
9 3 1

The dbo.inv table has the ImportAlertID as I said
And populates the third column and can have namy entries.

There are only 2 distinct entries above for the second column.

The table ImportAlert has only the one entry and only one ImportAlertID

This is just bringing back too many rows...

FROM ImportAlert a
INNER JOIN ImportAlertTrackingNumbers itn ON a.ImportAlertID = itn.InvImportAlertID
	--AND inv.ImportAlertID = a.ImportAlertID

--INNER JOIN ImportAlertTrackingNumbers itn ON 
--inv.ImportAlertID = itn.InvImportAlertID
--itn.ImportAlertID = a.ImportAlertID
		
INNER JOIN invoicenotadvanced inv 
ON a.ImportAlertID = inv.ImportAlertID
AND inv.ImportAlertID = itn.InvImportAlertID

Need some suggestions here,

Thanks,

Zath

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-22 : 14:28:08

Are you trying to use other columns from your tables? That will help us provide you direction. But, you are join two tables so you are going to get all the matching rows between ImportAlert and ImportAlertTrackingNumbers, which makes sense. Maybe this will help?

SELECT *
FROM
(
	-- Do you need to join these tables?
	SELECT DISTINCT a.ImportAlertID
	FROM ImportAlert a
	INNER JOIN ImportAlertTrackingNumbers itn 
	ON a.ImportAlertID = itn.InvImportAlertID
) AS T
INNER JOIN 
	invoicenotadvanced inv 
	ON T.ImportAlertID = inv.ImportAlertID

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-07-22 : 14:34:08

Actually, that helps.

I can see where you are going with this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources