July 23, 2013 at 12:10 am
Hello All,
I need your help in writing a query for below scenario,
Lets assume there are two tables,
Table A has 4 coulmns and Table B has 4 coulms
Table A Definition and Value
Col1 Col2 Col3 Col4
1 2 3 A
1 2 3 B
1 2 3 C
Table B Definition and Value
Col1 Col2 Col3 Col4
1 2 3 B
1 2 3 A
If I write a inner join on Table A and B to compare like
a.col1 = b.col1 and a.col2 = b.col2 and a.col3 = b.col3 and a.col4 != b.col4
It is not fetching the odd record in Table A which is having value 'C' in Col4, though it will fetch all the records in Table A.
Ideally I should get all the records where all the three columns (col1, col2, col3) in Table A and B are same and though Col4 is different (regardless of order of the record).
Is it possible to write a TSQL -Query ?
Thanks in advance
KJ
July 23, 2013 at 12:26 am
Hi ,
a little help in setting up the sample could have helped a lot. Like this:
IF OBJECT_ID('tempdb..#TableA') IS NOT NULL
DROP TABLE #TableA
IF OBJECT_ID('tempdb..#Tableb') IS NOT NULL
DROP TABLE #TableB
CREATE TABLE #TableA
(
Col1 INT,
Col2 INT,
Col3 INT,
Col4 CHAR(1)
)
CREATE TABLE #TableB
(
Col1 INT,
Col2 INT,
Col3 INT,
Col4 CHAR(1)
)
INSERT INTO #TableA ( Col1 , Col2 , Col3 , Col4 )
VALUES ( 1 , 2 , 3, 'A')
,( 1 , 2 , 3, 'B')
,( 1 , 2 , 3, 'C')
INSERT INTO #TableB ( Col1 , Col2 , Col3 , Col4 )
VALUES ( 1 , 2 , 3, 'A')
,( 1 , 2 , 3, 'B')
There are multiple ways of doing this and i am going to demonstrate one method of it. This may not be the best performing solution, but a good starting point for you to learn
; WITH CTE (Col1 , Col2 , Col3 , Col4) AS
(
SELECT Col1 , Col2 , Col3 , Col4
FROM #TableA
UNION ALL
SELECT Col1 , Col2 , Col3 , Col4
FROM #TableB
)
SELECT
C.Col1 , C.Col2 , C.Col3 , C.Col4
FROM CTE C
GROUP BY
C.Col1 , C.Col2 , C.Col3 , C.Col4
HAVING COUNT(*) = 1
Try that and let me know if that works for you.
July 23, 2013 at 6:53 pm
Isn't that just a LEFT JOIN?
SELECT a.Col1, a.Col2, a.Col3, a.Col4
FROM #TableA a
LEFT JOIN #TableB b
ON a.Col1 = b.Col1 AND a.Col2 = b.Col2 AND
a.Col3 = b.Col3 AND a.Col4 = b.Col4
WHERE b.Col4 IS NULL;
Welcome back ColdCoffee! Haven't seen you around in awhile.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 23, 2013 at 10:39 pm
Hello ColdCoffee / Dawain.c
Thanks for your prompt reply,
I tried both options CTE and Left join, it both works except for below condition,
INSERT INTO #TableA ( Col1 , Col2 , Col3 , Col4 )
VALUES ( 1 , 2 , 3, 'A')
,( 1 , 2 , 3, 'B')
,( 1 , 2 , 3, 'C')
,( 1 , 2 , 4, 'A') --> odd record
,( 1 , 3 , 3, 'A') --> odd record
INSERT INTO #TableB ( Col1 , Col2 , Col3 , Col4 )
VALUES ( 1 , 2 , 3, 'B')
,( 1 , 2 , 3, 'A')
The additional records in #TableA should be excluded, since the logic should consider all three columns Col1, Col2, Col3 as same and find a mismatch in Col4 comparing both #TableA and # TableB.
Any thoughts ?
Thanks
Vijay
July 23, 2013 at 11:01 pm
SELECT a.Col1, a.Col2, a.Col3, a.Col4
FROM #TableA a
LEFT JOIN #TableB b
ON a.Col1 = b.Col1 AND a.Col2 = b.Col2 AND
a.Col3 = b.Col3 AND a.Col4 = b.Col4
WHERE b.Col4 IS NULL AND EXISTS (
SELECT 1
FROM #TableB c
WHERE a.Col1 = c.Col1 AND a.Col2 = c.Col2 AND a.Col3 = c.Col3);
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 24, 2013 at 12:28 am
Hello Dawain,
Excellent, Thanks for sharing the query.
It works like a charm !!!
Thanks
KJ
July 24, 2013 at 12:32 am
Glad that it worked. I hope you understand the power of providing ready-to-use sample data. It makes the life of forum members a lot easier to work on the solution right away.
Nice work there Dwain.Thanks for your welcome Dwain. Been busy with the project works.. Got a free 10 hr window yesterday so i jumped onto SSC. How're u doing?
July 24, 2013 at 1:03 am
ColdCoffee (7/24/2013)
Glad that it worked. I hope you understand the power of providing ready-to-use sample data. It makes the life of forum members a lot easier to work on the solution right away.Nice work there Dwain.Thanks for your welcome Dwain. Been busy with the project works.. Got a free 10 hr window yesterday so i jumped onto SSC. How're u doing?
I'm doing pretty good. Thanks for asking. Been pretty busy myself of late. Only returned to forum posting in the last few weeks. Have a big project that has me shuttling back and forth between Bangkok and Papua New Guiness that keeps me hopping.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 24, 2013 at 1:04 am
dwain.c (7/24/2013)
ColdCoffee (7/24/2013)
Glad that it worked. I hope you understand the power of providing ready-to-use sample data. It makes the life of forum members a lot easier to work on the solution right away.Nice work there Dwain.Thanks for your welcome Dwain. Been busy with the project works.. Got a free 10 hr window yesterday so i jumped onto SSC. How're u doing?
I'm doing pretty good. Thanks for asking. Been pretty busy myself of late. Only returned to forum posting in the last few weeks. Have a big project that has me shuttling back and forth between Bangkok and Papua New Guiness that keeps me hopping.
Thanks nice. and tiresome too. i envy as well pity you, dwain 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply