SELECT matching records from 2 tables

_{Wednesday, October 12, 2011 8:04 AM}

Hi,

I Need to get matching records from 2 Tables,

I do an inner join but it returns only 2000 rows in need to return 4000

T1 has 2500 rec and T2 has 6000.

Thanks

_{Wednesday, October 12, 2011 4:02 PM ✅Answered}

I took diferent aproach,

insert All in one Table and did an inner join

any way

Thank u all 4 ur replyes 

_{Wednesday, October 12, 2011 8:14 AM}

Select * from Table T1, Table T2 where T1.Col1 = T2.col1

Mark as Answer If Reply Was Helpful Thanks Alok Seth

_{Wednesday, October 12, 2011 8:15 AM}

HI Deti !

You may any of the below query to get your desired output;

--1)
SELECT * 
FROM Table1 T1
INNER JOIN Table2 T2 ON T2.[KeyFiled] = T1.[KeyField]

Please let me know if this doesn’t work for you. Hope I have answered you correctly.
 
Thanks, Hasham

_{Wednesday, October 12, 2011 8:22 AM}

I have tryed it but still 2000 rows

_{Wednesday, October 12, 2011 8:28 AM}

Hi,

without seeing the data in your table, it is hard to answer why you have only 2000 instead of 4000. however if you used join as proposed above, the 2000 rows must be correct. try to check your data.

I hope it helps.

J. 

There are 10 type of people. Those who understand binary and those who do not.

_{Wednesday, October 12, 2011 8:41 AM}

T1

A111       00659

A111       00667

A111       00652

 T2

A111       00659       
A111        00667   
A111        00652

This is my data,  the select innerjoin returs 3rows i need all 6

_{Wednesday, October 12, 2011 8:49 AM}

HI Deti !

Please try to understand the result of the INNER JOIN is based on the joining condition nothing else. Let's see th difference in my first query and second one;

CREATE TABLE #Table1(TID VARCHAR(10),Value VARCHAR(10))
CREATE TABLE #Table2(TID VARCHAR(10),Value VARCHAR(10))

INSERT INTO #Table1
SELECT 'A111','00659' UNION ALL
SELECT 'A111','00667' UNION ALL
SELECT 'A111','00652' 

INSERT INTO #Table2
SELECT 'A111','00659' UNION ALL
SELECT 'A111','00667' UNION ALL
SELECT 'A111','00652' 

--1)
SELECT * 
FROM #Table1 T1
INNER JOIN #Table2 T2 ON T1.TID = T2.TID AND T1.Value = T2.Value
--TID Value TID  Value
--A111 00659 A111 00659
--A111 00667 A111 00667
--A111 00652 A111 00652

--2)
SELECT * 
FROM #Table1 T1
INNER JOIN #Table2 T2 ON T1.TID = T2.TID
--TID Value TID  Value
--A111 00659 A111 00659
--A111 00667 A111 00659
--A111 00652 A111 00659
--A111 00659 A111 00667
--A111 00667 A111 00667
--A111 00652 A111 00667
--A111 00659 A111 00652
--A111 00667 A111 00652
--A111 00652 A111 00652

Note : For First query only 3 rows satisfy the joining condition hence you get these 3 rows and you can get other columns for Table1 and Table2 in same row but not in other row.
Note : For Secound query all 3 rows of Table1 satisfy the joining condition with every row on Table2 resulting in 9 rows.

Please let me know if this doesn’t work for you. Hope I have answered you correctly.
 
Thanks, Hasham

_{Wednesday, October 12, 2011 9:36 AM}

TRY:

SELECT T1.tid,T1.value FROM T1
LEFT JOIN T2 ON 
T1.tid=T2.tid and T1.value=T2.value
WHERE T2.tid IS NOT NULL
UNION ALL
SELECT T2.tid,T2.value FROM T1
RIGHT JOIN T2 ON 
T1.tid=T2.tid and T1.value=T2.value
WHERE T1.tid IS NOT NULL

If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
Blog : My Blog

_{Wednesday, October 12, 2011 9:40 AM}

Hi,
Try below T-SQL it will meet your requirement.

DECLARE @Table1 TABLE(TID VARCHAR(10),Value VARCHAR(10))
DECLARE @Table2 TABLE(TID VARCHAR(10),Value VARCHAR(10))

INSERT INTO @Table1
SELECT 'A111','00659' UNION ALL
SELECT 'A111','00667' UNION ALL
SELECT 'A111','00652' 

INSERT INTO @Table2
SELECT 'A111','00659' UNION ALL
SELECT 'A111','00667' UNION ALL
SELECT 'A111','00652' 

SELECT A.TID,A.Value  FROM @Table1 A INNER JOIN @Table2 B
ON A.TID = B.TID AND A.Value = B.Value
UNION ALL
SELECT B.TID,B.Value  FROM @Table1 A INNER JOIN @Table2 B
ON A.TID = B.TID AND A.Value = B.Value

BTW,what is objective of this query?Shatrughna.

_{Wednesday, October 12, 2011 9:42 AM}

Hi,

To return matching records from two Tables you can use UNION ALL

eg:

SELECT column1,column2 FROM Table1

UNION ALL

SELECT column1,column2 FROM Table2

i hope may this helps you

PS.Shakeer Hussain

_{Wednesday, October 12, 2011 9:46 AM}

try:

SELECT T1.tid,T1.value FROM T1
LEFT JOIN T2 ON 
T1.tid=T2.tid and T1.value=T2.value
WHERE T2.tid IS NOT NULL
UNION ALL
SELECT T2.tid,T2.value FROM T1
RIGHT JOIN T2 ON 
T1.tid=T2.tid and T1.value=T2.value
WHERE T1.tid IS NOT NULL

If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
Blog : My Blog

_{Wednesday, October 12, 2011 3:59 PM}

Still returns 3 rows