Manimalis Manimalis - 6 months ago 8
SQL Question

How can I find a value in 4 tables?

I have 4 tables in sql developer (TABLE, TABLE1, TABLE2 and TABLE3) and I would like to know if a certain value exist in one of this 4 tables (for example TIE = 100).

The 4 tables have the same structure with "TIE" column.

I would like to know in which table the TIE value equals 100.

I have written this in sql, but it doesn't work :

SELECT TIE ,
instr
FROM
(SELECT TABLE1.TIE,
'terre' instr
FROM TABLE,
TABLE1
)
UNION
(SELECT TABLE2.TIE,
'air' instr
FROM TABLE,
TABLE2
)
UNION
(SELECT TABLE3.TIE,
'mer' instr
FROM TABLE,
TABLE3
)
WHERE TIE = '100';


Any help would be appreciated...

Answer

Below query might give the required result :-

 select TIE,instr
 from
 (
 SELECT TIE,'terre' instr
 FROM TABLE1

 UNION

 SELECT TIE,'terre' instr
 FROM TABLE1

 UNION

 SELECT TIE, 'air' instr
 FROM  TABLE2

 UNION

 SELECT TIE,'mer' instr
 FROM  TABLE3

 ) A where TIE=100
Comments