|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Laplink Gold 2008 - $15 Off with Coupon Code CJM9NE
|
|
#1
May 27th, 2004, 01:11 PM
|
|||
|
|||
|
Question
I have col1 and col2 in table1
Table1 col1 col2 1234 IM2 1234 M4 3456 IV5 6789 E98 Table2 col1 col2 1234 M325 3456 IV5 6789 E98 Record 1234 in table2 is missing IM2 and M4 values from table1. I need to write a query that will bring back records where table1.col1 = table2.col1 and table1.col2 not in table2.col2. I need to solve it as soon as possible. Please help. Thanks.
|
|
#2
May 28th, 2004, 12:36 AM
|
|||
|
|||
|
give this a shot...
Code:
select * from Table1 T1 inner join Table2 T2 on T1.col1 = T2.col1 where T1.col2 not in (select col2 from Table2)
|
|
#3
May 28th, 2004, 11:46 PM
|
||||
|
||||
|
null, that won't work in all cases
what if there's a row in table2 with 3456 M4 then when you join the 1234 rows, 1234 M4 will fail because M4 is in the subquery, however, 1234 M4 isn't in table2
|
|
#4
May 29th, 2004, 10:36 PM
|
|||
|
|||
|
[assuming the record you brought up in your reply exists in T2]
What do you mean when you say "1234 M4 will fail"? From what I took from the posters request, this record shouldn't return b/c the col2 value (M4) is in (select col2 from T2)...so this is excluded. Are you saying I should've responded w/something like this?... Code:
select * from Table1 T1 inner join Table2 T2 on T1.col1 = T2.col1 and T1.col2 <> T2.col2
|
|
#5
May 29th, 2004, 10:56 PM
|
||||
|
||||
|
if we have the following data
Table1 col1 col2 1234 IM2 1234 M4 3456 IV5 6789 E98 Table2 col1 col2 1234 M325 3456 M4 3456 IV5 6789 E98 then your subquery returns M325, M4, IV5, E98 but when you join table1's two 1234 rows to table2's single 1234 row, table1's IM2 isn't in the subquery results but M4 is your revised query has problems too, since multiple matching rows in both tables will have a cross-join effect
|
|
#6
June 6th, 2004, 12:14 AM
|
|||
|
|||
|
I'm confused...bummer
 Can you post your solution so I can study it?
|
|
#7
June 6th, 2004, 10:23 PM
|
|||
|
|||
|
Hi Inka,
I'm not very sure whether the following query is what u expect but u can give a try to see the result. Quote:
__________________
Regards,  Brightlight
|
|
#8
June 7th, 2004, 09:22 AM
|
||||
|
||||
|
Quote:
simple left outer join Code:
select Table1.col1
, Table1.col2
from Table1
left outer
join Table2
on Table1.col1
= Table2.col1
and Table1.col2
= Table2.col2
where Table2.col2 is null
Result:
col1 col2
1234 IM2
1234 M4
|
|
#9
June 7th, 2004, 12:24 PM
|
|||
|
|||
|
Ah, but you see I take this...
Quote:
...as something totally different than... where table1.col1 = table2.col1 and table1.col2 = table2.col2 ....which is what your query is using. You're result produces the record... Code:
col1 col2 1234 M4 ...based off my interpretation, I disagree.
|
|
#10
June 7th, 2004, 01:54 PM
|
||||
|
||||
|
my result produces two rows, not just the one that you say it does
and you are right, it is totally based on interpretation go back to the original post: "Record 1234 in table2 is missing IM2 and M4 values from table1." but if you look at the data with your interpretation, 1234 is also missing IV5 and E98 i'm pretty sure your interpretation is wrong like the saying goes, "your mileage may vary; mine doesn't"
|
|
#11
June 7th, 2004, 05:26 PM
|
|||
|
|||
|
Let's use the tables you brought up (which is what I've been using all along)...
Code:
Table1 col1 col2 1234 IM2 1234 M4 3456 IV5 6789 E98 Table2 col1 col2 1234 M325 3456 M4 3456 IV5 6789 E98 ..and yes, your result does return 2 rows, I was just using that one though. Code:
Result: col1 col2 1234 IM2 1234 M4 while 1234 is in table2, "m4" is in table2.col2, so I take that the record shouldn't return. I fully understand your solution though, just trying to show you where I was coming from, that's all. Good day Rudy.
|
|
#12
June 7th, 2004, 11:00 PM
|
|||
|
|||
|
Hi Inka,
Just a suggestion to you. If you would have stated the required result in addition to the explanation (table1.col1 = table2.col1 and table1.col2 not in table2.col2...) it would have reduced the misinterpretations and saved the time.
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Question |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
