CODEWITHSUNDEEP
sql-server-2008selectstored-procedures
EagleFox
EagleFox

Reputation: 1367

joining two sql statements in a stored procedure - (no same columns)

I have a situation which deals with two sets of data with different columns. I know I could use UNION but UNION requires equal number of expressions in both tables. I am trying to join these two statements in my stored procedure. The first statement has one Extra Column 'Location'

Select 
  TableA.Name,
  TableB.Occupation,
  TableA.Location,
  'Group1' AS [groupBy]
From
 TableA,
 TableB
Where
 TableA.ID = 1

Select 
  TableA.Name,
  TableB.Occupation,
  'Group2' AS [groupBy]
From
 TableA,
 TableB
Where
 TableB.ID = 10

My result should look like this

Name    Occupation      GroupBy   Location
David   Doctor          Group1    USA
John    Pilot           Group1    Asia
Dwayne  Wrestler        Group2    NULL
Axel    RockStar        Group2    NULL

My Table structure

Table A

ID Name  Occupation Location 
1  David Doctor     USA
1  John  Pilot      Asia
2  Mike  Clerk      Europe


Table B 

ID  Name   Occupation
3   Wayne  Writer
4   Shane  Publisher
10  Dwayne Wrestler
10  Axel   Rockstar

Upvotes: 0

Views: 960

Answers (1)

Pieter Geerkens
Pieter Geerkens

Reputation: 11883

That's called a UNION; just manually add ", NULL" to the select list in the second query

Add as many of them as you need, whereever you need them. If you need additional columns in both tables, instances of NULL in the first table must be aliasd with the appropriate column name.

Upvotes: 1

Related Questions