CODEWITHSUNDEEP
sqlsql-serverselectunion
Alec
Alec

Reputation: 117

SQL Multiple Tables

I have 2 sets of 2 corresponding tables (4 tables total). The two sets only correspond in one column. I want the query to search through that one column in both joined tables, and be able to return the value in the selected column, even if that column doesn't exist in the other table.

Currently, my query looks like:

select z
FROM A1
INNER JOIN A2 
ON A1.x=A2.x
WHERE A1.x= '25'
UNION
select z
FROM B1
INNER JOIN B2
ON B1.x=B2.x
WHERE B1.x= '25'

UNION works as long as I am looking for a column which both (joined) tables A and B have in common. I'd like to be able to get values from columns which only exist in A or in B, but not necessarily both of them.

Thanks in advance.

Edit: Basically, I want two completely separate queries on separate tables, but in one query, with only one select.

Example:

  Table A1
  x | y
  ------
  1 | a
  2 | b

  Table A2
  x | z
  ------
  1 | c
  2 | d

  Table B1
  x | v
  ------
  3 | e
  4 | f

 Table B2
  x | w
  ------
  3 | g
  4 | h

So I want look for a column variable (such as v,w,y, or z), with a specified value of x. e.g. select w where x=4 should give me h; select z where x=2 should give me d.

Upvotes: 1

Views: 286

Answers (5)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

You must have the same number of columns in each select statement when using UNION. You can add constant values for columns that are missing. You must also have the same data type for each column.

select Column1, null as Column2
from T1
union
select '', Column2
from T2

As per comment by @Adrian, you can of course use null as a constant value. Otherwise you need to pick a constant value of the data type that is used for the column.

select Column1, null as Column2
from T1
union
select null, Column2
from T2

Use the sample data provided in the question:

select A1.x, A2.z as 'y'
from A1
  inner join A2 
    on A1.x=A2.x
where A1.x= 4
union
select B1.x, B2.w as 'y'
from B1
  inner join B2
    on B1.x=B2.x
where B1.x= 4

Result:

x   y
4   h

With a 2 instead of a 4

Result:

x   y
2   d

Upvotes: 3

KeithS
KeithS

Reputation: 71591

Well, UNION's only requirement is that the number and type of the fields in each result set being UNIONed must match. So, if B has a Column2 and A doesn't, you can select Column1 from the joined As and Column2 from the joined Bs, and provided A.Column1 and B.Column2 are the same type (you can CAST or CONVERT if necessary) the statement will still work. Maybe I'm not understanding the problem, but I think it's pretty easy to solve.

Upvotes: 0

Phil
Phil

Reputation: 179

So you basically want to do:

select Column1
FROM A1
INNER JOIN A2 
ON A1.ID=A2.ID
WHERE A1.ID= '25'
UNION
select Column2
FROM B1
INNER JOIN B2
ON B1.ID=B2.ID
WHERE B1.ID= '25'

right?

If so, your problem is probably just that Column1 and Column2 are different types. So, do something like

select cast(Column1 as varchar(255))

and

select cast(Column2 as varchar(255))

Should work!

Upvotes: 1

Steve Prentice
Steve Prentice

Reputation: 23514

When using UNION, make sure all queries return result sets that have the same number of columns with the same type. If you want to return a column from the second query in a union that the first query doesn't have, you can modify your first query to have a NULL value for that column.

Upvotes: 1

Adriano Carneiro
Adriano Carneiro

Reputation: 58665

It would be something like that:

select Column1, ColumnThatOnlyExistsInContextA, null as ColumnThatOnlyExistsInContextB
FROM A1
INNER JOIN A2 
ON A1.ID=A2.ID
WHERE A1.ID= '25'

UNION

select Column1, null, ColumnThatOnlyExistsInContextB
FROM B1
INNER JOIN B2
ON B1.ID=B2.ID
WHERE B1.ID= '25'

Upvotes: 2

Related Questions