Joining with Nulls
-
I hate asking questions when I know there are 42,000 answers out there already, but I can't seem to place my finger on any of them.
I have a top table. I need to get records from the top table that do not have 4321 in child table 2, but do have 1234 in child table 1. The problem is the potential for no records in child table 2.
SELECT
t.*
FROM
dbo.top t
JOIN dbo.child1 c1 ON c1.t_id = t.id
WHERE
c1.number = 1234
AND t.id NOT IN (SELECT c2.t_id FROM dbo.child2 c2 WHERE c2.number = 4321)
There's a more efficient way of doing that. In fact, I've done it, numerous times. Just not recently, and I just need a head slap.
-
If you do not have any records in child table, then you wont get any rows in resultset. Can you specify your requiremtn exactly? some sample data and expected result will do good
-
CREATE TABLE [dbo].[top](
[id] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[child1](
[number] [int] NOT NULL,
[t_id] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[child2](
[number] [int] NOT NULL,
[t_id] [int] NOT NULL
) ON [PRIMARY]
insert [top] (id)
select 1 union
select 2 union
select 3 union
select 4
insert child1 (number, t_id)
select 1234, 1 union
select 1234, 2 union
select 5454, 3 union
select 1234, 4
insert child2 (number, t_id)
select 6456, 1 union
select 4321, 2
I expect to find records from the top table id's 1 and 4.
My query works fine, I just believe there is a better (more efficient) syntax.
-
My first instinct is to use left join here but I'm not sure I understand what you need.
So all in all your query works fine but you want it to run faster?
Please post the actual execution plan.
-
This was actually a hypothetical - I'm trying to get it right before I get that far down the road. The solution I eventually decided on was:
SELECT
t.*
FROM
dbo.[top] t
WHERE
EXISTS (SELECT 1
FROM dbo.child1 c1
WHERE c1.number = 1234
AND c1.t_id = t.id)
AND NOT EXISTS (SELECT 1
FROM dbo.child2 c2
WHERE c2.number = 4321
AND c2.t_id = t.id)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply