LEFT OUTER JOIN - 2 Tables, 2 Criteria

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

Transact-SQL (2000)

LEFT OUTER JOIN - 2 Tables, 2 Criteria

Author

Topic

ddouma
Starting Member

10 Posts

Posted - 2007-09-07 : 22:23:53

I have a query that's been giving my some trouble. I have the following two tables

TABLE 1
| DistCode | ProfileID |
------------------------
| 5X01 | 1 |
| 5X01 | 2 |
| 6F01 | 1 |
| 6D01 | 4 |

TABLE 2
| DistCode | ProfileID |
------------------------
| 5X01 | 1 |
| 6F01 | 1 |
| 6D01 | 4 |

I want to retrieve all the rows that exist in Table 1, but not in Table 2. Below is the query I have, but it's not working.

SELECT * FROM TABLE1
LEFT OUTER JOIN TABLE2 on TABLE1.distcode = TABLE2.distcode AND TABLE1.profile_id = TABLE2.profile_id
WHERE
TABLE2.distcode = null

Thanks, DD

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-07 : 22:34:58

[code]WHERE
TABLE2.distcode IS null[/code]

KH
[spoiler]Time is always against us[/spoiler]

ddouma
Starting Member

10 Posts

Posted - 2007-09-08 : 07:39:51

Oops, you got me there. But if I remove the where statement, the query is not returning any of the values from TABLE2 that don't match TABLE1.

ddouma
Starting Member

10 Posts

Posted - 2007-09-08 : 07:40:59

Got that backwards. The query doesn't return values from TABLE1 that don't match in TABLE2

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-08 : 09:42:34

[code]DECLARE @TABLE1 TABLE
(
DistCode varchar(4),
ProfileID int
)
INSERT INTO @TABLE1
SELECT '5X01', 1 UNION ALL
SELECT '5X01', 2 UNION ALL
SELECT '6F01', 1 UNION ALL
SELECT '6D01', 4

DECLARE @TABLE2 TABLE
(
DistCode varchar(4),
ProfileID int
)
INSERT INTO @TABLE2
SELECT '5X01', 1 UNION ALL
SELECT '6F01', 1 UNION ALL
SELECT '6D01', 4

SELECT *
FROM @TABLE1 t1
LEFT OUTER JOIN @TABLE2 t2
ON t1.DistCode = t2.DistCode AND t1.ProfileID = t2.ProfileID
WHERE t2.DistCode IS NULL

/*
DistCode ProfileID DistCode ProfileID
-------- ----------- -------- -----------
5X01 2 NULL NULL
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

ddouma
Starting Member

10 Posts

Posted - 2007-09-08 : 10:08:22

Thanks. You were right. That does work. Howerver, the sample data I provided was a little incorrect. I understand why the left outer join doesn't work in this situation, but what's the workaround?

DECLARE @TABLE1 TABLE
(
DistCode varchar(4),
ProfileID int
)
INSERT INTO @TABLE1
SELECT '5X01', 1 UNION ALL
SELECT '5X01', 2 UNION ALL
SELECT '5X01', 2 UNION ALL
SELECT '6F01', 1 UNION ALL
SELECT '6D01', 4

DECLARE @TABLE2 TABLE
(
DistCode varchar(4),
ProfileID int
)
INSERT INTO @TABLE2
SELECT '5X01', 1 UNION ALL
SELECT '5X01', 2 UNION ALL
SELECT '6F01', 1 UNION ALL
SELECT '6D01', 4

SELECT *
FROM @TABLE1 t1
LEFT OUTER JOIN @TABLE2 t2
ON t1.DistCode = t2.DistCode AND t1.ProfileID = t2.ProfileID
WHERE t2.DistCode IS NULL

/*
DistCode ProfileID DistCode ProfileID
-------- ----------- -------- -----------
*/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-08 : 10:11:18

what's the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

ddouma
Starting Member

10 Posts

Posted - 2007-09-08 : 12:54:58

The expected result would be. In that it didn't match one of the occurences in TABLE 1

DistCode ProfileID DistCode ProfileID
-------- ----------- -------- -----------
5X01 2 NULL NULL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-09 : 09:25:17

[code]DECLARE @TABLE1 TABLE
(
DistCode varchar(4),
ProfileID int
)
INSERT INTO @TABLE1
SELECT '5X01', 1 UNION ALL
SELECT '5X01', 2 UNION ALL
SELECT '5X01', 2 UNION ALL
SELECT '6F01', 1 UNION ALL
SELECT '6D01', 4

DECLARE @TABLE2 TABLE
(
DistCode varchar(4),
ProfileID int
)
INSERT INTO @TABLE2
SELECT '5X01', 1 UNION ALL
SELECT '5X01', 2 UNION ALL
SELECT '6F01', 1 UNION ALL
SELECT '6D01', 4

SELECT *
FROM (
SELECT DistCode, ProfileID, c = COUNT(*)
FROM @TABLE1
GROUP BY DistCode, ProfileID
) t1
LEFT OUTER JOIN
(
SELECT DistCode, ProfileID, c = COUNT(*)
FROM @TABLE2
GROUP BY DistCode, ProfileID
) t2
ON t1.DistCode = t2.DistCode AND t1.ProfileID = t2.ProfileID AND t1.c = t2.c
WHERE t2.DistCode IS NULL[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources