Excluding data using In Line view

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)

Excluding data using In Line view

Author

Topic

skelly73
Starting Member

1 Post

Posted - 2007-11-28 : 14:08:11

Issue:

Basically table E returns 2 results from TJ_Amounts, I want the outer query/ join to bring back all of data from TJ_Amounts EXCEPT the rows matching the ID returned in table E.

Query:

SELECT *
FROM dbo.TJ_Amounts D INNER JOIN
(SELECT dbo.TJ_Amounts.TransactionID AS ID1
FROM dbo.TJ_Amounts
WHERE (dbo.TJ_Amounts.AuthFlag = 'Y')) E ON D.TransactionID <> E.ID1

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-28 : 14:51:03

[code]
SELECT
d.*
FROM
dbo.TJ_Amounts D
where
D.TransactionID not in
(
SELECT
dbo.TJ_Amounts.TransactionID
FROM
dbo.TJ_Amounts
WHERE
dbo.TJ_Amounts.AuthFlag = 'Y'
)

[/code]

CODO ERGO SUM

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-28 : 15:33:32

left join is an alternative to subquery...
SELECT
d.*
FROM
dbo.TJ_Amounts D
LEFT JOIN
(
SELECT DISTINCT
dbo.TJ_Amounts.TransactionID
FROM
dbo.TJ_Amounts
WHERE
dbo.TJ_Amounts.AuthFlag = 'Y'
) AS E
ON E.TransactionID = D.TransactionID
WHERE
E.TransactionID IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources