April 8, 2009 at 12:25 pm
one of the developers asked me a question I can't answer. if i have 2 tables and one has one more row than the other - what kind of join (or sql) will display only the row that is different?
April 8, 2009 at 12:40 pm
Sounds like an interview question, actually.
Do you have the tables he's using? Do you understand the differences between inner, outer and cross joins?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 8, 2009 at 12:45 pm
It's not an interview question.. and I suppose I don't understand joins as well as I should. I thought it would be a left outer join but everytime I try it it returns all rows.... I've been building a new UDB Server all day and my brain hurts!!!!
April 8, 2009 at 12:51 pm
Try full outer, instead of left, and you should be able to include an "is null" in the Where clause that will filter for unmatched rows, you just have to pick a column that can't be null.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 8, 2009 at 1:25 pm
awesome! Thnk you!
April 8, 2009 at 2:12 pm
Jpotucek (4/8/2009)
It's not an interview question.. and I suppose I don't understand joins as well as I should. I thought it would be a left outer join but everytime I try it it returns all rows.... I've been building a new UDB Server all day and my brain hurts!!!!
Yes, you are looking for an outer join - and to return unmatched rows you need to include a check for one of the columns in the outer table to be null.
Pseudo-code:
SELECT {columns}
FROM dbo.PreservedTable p
LEFT JOIN dbo.UnPreservedTable up ON up.KeyColumn = p.KeyColumn
WHERE up.KeyColumn IS NULL
The preserved table is the table that has the extra row - checking with IS NULL will find find the one row that does not have a matching row in the unpreserved table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 9, 2009 at 10:08 am
So here is my code.. This returns
select a.towid,a.update_date,a.update_action,b.towid,b.presentdate
from modify_title.dbo.mat_audit_nbdoc as a left join towerdb.tower.nbdocdeletedrecords as b
on (a.towid = b.towid)
where
a.Update_Date >= '04/07/2009'
and a.Update_Date < '04/08/2009'
and b.presentdate>= '04/07/2009'
and b.presentdate<= '04/08/2009'
order by a.towid
This returns 473 rows - in other words .. everything BUT the row that's different. dbo.mat_audit_nbdoc has the extra row.. I tried coding your "where is null" but I can't seem to get it right..
I tried coding your
April 9, 2009 at 10:43 am
Jpotucek (4/9/2009)
So here is my code.. This returns
select a.towid,a.update_date,a.update_action,b.towid,b.presentdatefrom modify_title.dbo.mat_audit_nbdoc as a left join towerdb.tower.nbdocdeletedrecords as b
on (a.towid = b.towid)
where
a.Update_Date >= '04/07/2009'
and a.Update_Date < '04/08/2009'
and b.presentdate>= '04/07/2009'
and b.presentdate<= '04/08/2009'
order by a.towid
This returns 473 rows - in other words .. everything BUT the row that's different. dbo.mat_audit_nbdoc has the extra row.. I tried coding your "where is null" but I can't seem to get it right..
I tried coding your
When you added the criteria for the unpreserved table (towerdb.tower.nbdocdeletedrecords) in the where clause, you effectively turned the outer join into an inner join. That is because the row that does not match will have null in the presentdate column - and nulls are not equal to anything. A null value will not be less than your date, or greater than your date.
To get this to work - there are a couple of things you can do. You could move the criteria for the unpreserved table up into the join, as in:
left join towerdb.tower.nbdocdeletedrecords as b
on a.towid = b.towid
and b.presentdate >= '20090407'
and b.presentdate < '20090408'
where
a.Update_Date >= '04/07/2009'
and a.Update_Date < '04/08/2009'
Or, you could include a check for the column being null in the where clause as in:
left join towerdb.tower.nbdocdeletedrecords as b
on (a.towid = b.towid)
where
a.Update_Date >= '04/07/2009'
and a.Update_Date < '04/08/2009'
and (b.towid is null
or (b.presentdate >= '04/07/2009'
and b.presentdate <= '04/08/2009'))
My preference would be to include the check in the join criteria.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply