Return multiple rows only - 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)

Return multiple rows only

Author

Topic

Looper
Yak Posting Veteran

68 Posts

Posted - 2009-10-02 : 10:17:09

Has anyone any idea how I could change the query belwo to select only rows where tb2.EId exists more than once and the Tdate is null


DECLARE @Username varchar(10)

SET @UserName = 'PAY'

SELECT 
	NoNumber, 
	NpNumber,
	Fname, 
	Sname, 
	DOB, 
	ISNULL(Address1,'') As HomeAddress, 
	ISNULL(AddressCode,'') As PostCode, 
	PNumber, 
	TDate 
FROM 
	dbo.t_Table1 tb1 
INNER JOIN 
	t_Table2 tb2 ON tb1.Id = tb2.EId
INNER JOIN
	t_Map Mps ON Mps.EmpId = tb2.EmpId
WHERE
	Mps.Username = @Username
ORDER BY
	NoNumber ASC

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-02 : 10:32:13

Maybe it is possible by making tb2 a derived table to join.
But in your select list we can't see which columns are coming from tb2...

No, you're never too old to Yak'n'Roll if you're too young to die.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-10-02 : 10:50:43

[code]
WHERE Mps.Username = @Username
AND EXISTS
(
SELECT T22.EId
FROM t_Table2 T22
WHERE T22.EId = tb1.Id
GROUP BY T22.EId
HAVING COUNT(*) > 1

)
AND TDate IS NULL
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 10:58:15

[code]DECLARE @Username varchar(10)

SET @UserName = 'PAY'

SELECT *
FROM
(
SELECT
NoNumber,
NpNumber,
Fname,
Sname,
DOB,
ISNULL(Address1,'') As HomeAddress,
ISNULL(AddressCode,'') As PostCode,
PNumber,
TDate,
COUNT(1) OVER (PARTITION BY tb2.EId) AS Occurance
FROM
dbo.t_Table1 tb1
INNER JOIN
t_Table2 tb2 ON tb1.Id = tb2.EId
INNER JOIN
t_Map Mps ON Mps.EmpId = tb2.EmpId
WHERE
Mps.Username = @Username
)t
WHERE Occurance>1
ORDER BY
NoNumber ASC[/code]

Looper
Yak Posting Veteran

68 Posts

Posted - 2009-10-02 : 11:06:28

Thanks everyone that is exactly what I needed :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 11:07:08

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources