December 9, 2015 at 2:05 pm
Hi -
This might be so simple for expert, but I can figure out the way to do it.. Please advise..
I have 2 simple SQL
select id, studentname, student address
from table 1
select id, studentname, student address
from table 2
But now I want to combine those 2 by using union table1 and table2 BUT only if id table1.id = table2.id
select * from table1
UNION
select * from table 2
I searched but only find filter for each table but not if table1.id = table2.id
select * from table1 where table1.field ='something'
UNION
select * from table 2 where table2.field ='something'
Any advise is greatly appreciated.
Thanks
Dee
December 9, 2015 at 2:11 pm
Do you want the results from both tables or only the results from table1 if they can be linked to table2?
December 9, 2015 at 2:18 pm
You could do something like this if you want the data from each table on separate rows,
select table1.* from table1, table2 where table1.id =table2.id
UNION
select table2.* from table1, table2 where table1.id = table2.id
Or just this if you want the data from both tables on the same row.
select table1.*, table2.* from table1, table2 where table1.id =table2.id
December 9, 2015 at 3:58 pm
hi jshahan,
I would like everything from table1 plus any record in table 2 where id is match with table1.
Thank you
December 9, 2015 at 4:29 pm
Thanks ZZartin. The 1st suggestion is the one I need but when I run the query. It's extremely slow..i wait for 10 minutes and still executing even though data is not that big..
Table1 has 400 records and Table2 has 3000 records
when I just run each statement alone:
select table1.* from table1, table2 where table1.item =table2.item
it tool 13 seconds and result come out over 5000 records... (while Table1 has 400 records and Table2 has 3000 records)
Please advise.
Thanks
December 9, 2015 at 10:03 pm
Dee Dee-422077 (12/9/2015)
Thanks ZZartin. The 1st suggestion is the one I need but when I run the query. It's extremely slow..i wait for 10 minutes and still executing even though data is not that big..Table1 has 400 records and Table2 has 3000 records
when I just run each statement alone:
select table1.* from table1, table2 where table1.item =table2.item
it tool 13 seconds and result come out over 5000 records... (while Table1 has 400 records and Table2 has 3000 records)
Please advise.
Thanks
This should be a bit faster...
SELECT
t1.id,
t1.studentname,
t1.studentaddress
FROM
dbo.Table1 t1
UNION ALL
SELECT
t.id,
t.studentname,
t.studentaddress
FROM
dbo.Table1 t2
WHERE
EXISTS (SELECT 1 FROM dbo.Table1 t1 WHERE t2.id = t1.id)
;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply