August 11, 2009 at 8:55 am
Hello
I have 2 tables TableA and TableB. I want to display all the records of TableA that are not present in TableB.
General syntax to do this is:
SELECT *
FROM TableA
LEFT JOIN TableB
ON TableA.ID1=TableB.ID1
WHERE TableA.ID1 is NULL
Now I have to match two ID's so now the general syntax becomes:
SELECT *
FROM TableA
LEFT JOIN TableB
ON (TableA.ID1=TableB.ID1 AND TableA.ID2=TableB.ID2)
WHERE (TableA.ID1 is NULL AND TableB.ID2 IS NULL)
This much seems to be working fine. Now the issue is I dont want all the records from TableA and TableB to be joined. I want TableA to be filtered on Status='A' and TableB to be filtered on AnotherStat='B' and I want to LEFT JOIN the two resulting TableA and TableB tables. If I put these conditions in the above where clause, the query doesnt seem to be working correctly. Any ideas on how to do it?
August 11, 2009 at 8:59 am
novice_coder (8/11/2009)
HelloI have 2 tables TableA and TableB. I want to display all the records of TableA that are not present in TableB.
General syntax to do this is:
SELECT *
FROM TableA
LEFT JOIN TableB
ON TableA.ID1=TableB.ID1
WHERE TableB.ID1 is NULL
Now I have to match two ID's so now the general syntax becomes:
SELECT *
FROM TableA
LEFT JOIN TableB
ON (TableA.ID1=TableB.ID1 AND TableA.ID2=TableB.ID2)
WHERE (TableB.ID1 is NULL OR TableB.ID2 IS NULL)
This much seems to be working fine. Now the issue is I dont want all the records from TableA and TableB to be joined. I want TableA to be filtered on Status='A' and TableB to be filtered on AnotherStat='B' and I want to LEFT JOIN the two resulting TableA and TableB tables. If I put these conditions in the above where clause, the query doesnt seem to be working correctly. Any ideas on how to do it?
Made some corrections to the query mentioned above
August 11, 2009 at 9:01 am
Add filter conditions for the main table (TableA) in the WHERE clause, then add filters for TableB in the JOIN conditions.
SELECT *
FROM TableA
LEFT JOIN TableB
ON (TableA.ID1=TableB.ID1 AND TableA.ID2=TableB.ID2 AND TableB.AnotherStat='B' )
WHERE (TableA.ID1 is NULL AND TableB.ID2 IS NULL)
AND TableA.Status='A'
Hope this helps
Gianluca
-- Gianluca Sartori
August 11, 2009 at 9:04 am
Gianluca Sartori (8/11/2009)
Add filter conditions for the main table (TableA) in the WHERE clause, then add filters for TableB in the JOIN conditions.
SELECT *
FROM TableA
LEFT JOIN TableB
ON (TableA.ID1=TableB.ID1 AND TableA.ID2=TableB.ID2 AND TableB.AnotherStat='B' )
WHERE (TableA.ID1 is NULL AND TableB.ID2 IS NULL)
AND TableA.Status='A'
Hope this helps
Gianluca
Thanks for the reply. Whats the reason for putting one filter in ON cluase and the other filter in WHERE clause? why not both in ON or both in WHERE?
August 11, 2009 at 9:05 am
Gianluca Sartori (8/11/2009)
Add filter conditions for the main table (TableA) in the WHERE clause, then add filters for TableB in the JOIN conditions.
SELECT *
FROM TableA
LEFT JOIN TableB
ON (TableA.ID1=TableB.ID1 AND TableA.ID2=TableB.ID2 AND TableB.AnotherStat='B' )
WHERE (TableA.ID1 is NULL AND TableB.ID2 IS NULL)
AND TableA.Status='A'
Hope this helps
Gianluca
You could also put the additional filter of both tables in the JOIN criteria:
select
*
from
dbo.TableA ta
left outer join dbo.TableB tb
on (ta.ID1 = tb.ID2
and ta.Status = 'A'
and tb.AnotherStat = 'B')
where
tb.ID is null -- show all records with no match in TableB
August 11, 2009 at 9:27 am
novice_coder (8/11/2009)
Gianluca Sartori (8/11/2009)
Add filter conditions for the main table (TableA) in the WHERE clause, then add filters for TableB in the JOIN conditions.
SELECT *
FROM TableA
LEFT JOIN TableB
ON (TableA.ID1=TableB.ID1 AND TableA.ID2=TableB.ID2 AND TableB.AnotherStat='B' )
WHERE (TableA.ID1 is NULL AND TableB.ID2 IS NULL)
AND TableA.Status='A'
Hope this helps
Gianluca
Thanks for the reply. Whats the reason for putting one filter in ON cluase and the other filter in WHERE clause? why not both in ON or both in WHERE?
Let's look at what you were asking, and I'll paraphrase:
1. I want all records from TableA where Status = 'A'
2. I want all records from TableB where AnotherStat = 'B'
3. I want all records from TableA with no match in TableB
With the requirements on 1 and 2 above, you want to filter the records on these fields as part of the join, not after the join.
Does this help?
August 11, 2009 at 9:59 am
Whats the reason for putting one filter in ON cluase and the other filter in WHERE clause? why not both in ON or both in WHERE?
If you put both conditions in the WHERE clause, you would make it behave as an INNER JOIN.
This means that the query wouldn't return rows from TableA because they don't match your criteria on TableB.
-- Gianluca Sartori
August 11, 2009 at 10:00 am
Lynn Pettis (8/11/2009)
novice_coder (8/11/2009)
Gianluca Sartori (8/11/2009)
Add filter conditions for the main table (TableA) in the WHERE clause, then add filters for TableB in the JOIN conditions.
SELECT *
FROM TableA
LEFT JOIN TableB
ON (TableA.ID1=TableB.ID1 AND TableA.ID2=TableB.ID2 AND TableB.AnotherStat='B' )
WHERE (TableA.ID1 is NULL AND TableB.ID2 IS NULL)
AND TableA.Status='A'
Hope this helps
Gianluca
Thanks for the reply. Whats the reason for putting one filter in ON cluase and the other filter in WHERE clause? why not both in ON or both in WHERE?
Let's look at what you were asking, and I'll paraphrase:
1. I want all records from TableA where Status = 'A'
2. I want all records from TableB where AnotherStat = 'B'
3. I want all records from TableA with no match in TableB
With the requirements on 1 and 2 above, you want to filter the records on these fields as part of the join, not after the join.
Does this help?
hmmm makes sense. Thanks for the explanation. I have another problem: TableB.ID2 is NULL for some records and I am not getting the correct result because of this. I put the condition TableB.ID2 IS NOT NULL in the ON clause but doesn't seem to be working. How do I fix this issue?
August 11, 2009 at 10:09 am
Okay, now I'm slightly confused. One, what are your requirements? We also could use the DDL for your tables, sample data for your tables, and the expected results based on the sample data.
Please read the first article I have referenced in my signature block. If you follow the instructions in that article regarding what you should post you will get much better (and tested) answers in return for the extra effort on your part.
August 11, 2009 at 10:47 am
Lynn Pettis (8/11/2009)
Okay, now I'm slightly confused. One, what are your requirements? We also could use the DDL for your tables, sample data for your tables, and the expected results based on the sample data.Please read the first article I have referenced in my signature block. If you follow the instructions in that article regarding what you should post you will get much better (and tested) answers in return for the extra effort on your part.
OK. I have understood the filtering part of the tables (TableA.status = 'A' and TableB.AnotherStat = 'B') So I am not including that here. Heres the code to generate the table and data:
use Test
--===== If the test table already exists, drop it
IF OBJECT_ID('TableA','U') IS NOT NULL
DROP TABLE TableA
IF OBJECT_ID('TableB','U') IS NOT NULL
DROP TABLE TableB
--===== Create the test table with
CREATE TABLE TableA
(
month varchar(10),
ID varchar(15)
)
CREATE TABLE TableB
(
month varchar(10),
ID varchar(15)
)
--===== Insert the test data into the test table
INSERT INTO TableA
(month, ID)
SELECT '200901','1' UNION ALL
SELECT '200902','2' UNION ALL
SELECT '200903','3' UNION ALL
SELECT '200904','4'
INSERT INTO TableB
(month, ID)
SELECT '200901','1' UNION ALL
SELECT '200902',NULL UNION ALL
SELECT '200903','3' UNION ALL
SELECT '200905','4'
Now I want the result
TableA.month TableA.ID TableB.month TableB.ID
200904 4 NULL NULL
But I am getting:
TableA.month TableA.ID TableB.month TableB.ID
200902 2 NULL NULL
200904 4 NULL NULL
I have tried the query:
select
*
from
dbo.TableA ta
left outer join dbo.TableB tb
on (tb.ID is NOT NULL and ta.ID = tb.ID and ta.month = tb.month)
where
tb.ID is null and tb.month is null
But still I am getting the wrong result. Any ideas on this?
August 11, 2009 at 11:16 am
Based on your sample data, the only way I see to do this is with the following code:
select
*
from
dbo.TableA ta
left outer join dbo.TableB tb
on (ta.month = tb.month)
where
tb.month is null;
August 11, 2009 at 11:20 am
Lynn Pettis (8/11/2009)
Based on your sample data, the only way I see to do this is with the following code:
select
*
from
dbo.TableA ta
left outer join dbo.TableB tb
on (ta.month = tb.month)
where
tb.month is null;
I need to match ID also. Should replace the ID in TableB with 0 and then try to execute the query you gave. Its a work around but should work I guess. Or is there a way to get the desired result without replacing TableB.ID having NULL values with 0?
August 11, 2009 at 11:41 am
You could use isnull(tb.ID,0), but that still doesn't match with anything in your sample data.
August 11, 2009 at 1:24 pm
Lynn Pettis (8/11/2009)
You could use isnull(tb.ID,0), but that still doesn't match with anything in your sample data.
ohhh I didnt realise that changing NULL to 0 wont help either. so what shd I do for this problem?
August 11, 2009 at 1:35 pm
novice_coder (8/11/2009)
Lynn Pettis (8/11/2009)
You could use isnull(tb.ID,0), but that still doesn't match with anything in your sample data.ohhh I didnt realise that changing NULL to 0 wont help either. so what shd I do for this problem?
OK Thankfully, I dont have to worry about it anymore. I am getting the result that I wanted.
Thanks so much for the help
Really appreciate it.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply