SQL statement Using UNION all

• Dee Dee-422077

  Ten Centuries

  Points: 1082

  More actions

  December 9, 2015 at 2:05 pm

  #322894

  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

• jshahan

  SSCarpal Tunnel

  Points: 4622

  More actions

  December 9, 2015 at 2:11 pm

  #1845054

  Do you want the results from both tables or only the results from table1 if they can be linked to table2?

• ZZartin

  SSC-Dedicated

  Points: 30921

  More actions

  December 9, 2015 at 2:18 pm

  #1845057

  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

• Dee Dee-422077

  Ten Centuries

  Points: 1082

  More actions

  December 9, 2015 at 3:58 pm

  #1845073

  hi jshahan,

  I would like everything from table1 plus any record in table 2 where id is match with table1.

  Thank you

• Dee Dee-422077

  Ten Centuries

  Points: 1082

  More actions

  December 9, 2015 at 4:29 pm

  #1845077

  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

• Jason A. Long

  SSC-Insane

  Points: 23790

  More actions

  December 9, 2015 at 10:03 pm

  #1845095

  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)

  ;