SELECT UNION ALL WHERE statement

[dangari]

Hi all.

Kindly assist me by showing me the right way to achieve the following.
1. I have the following SQL statement which works fine

SELECT x,y,z FROM t1 WHERE x LIKE CONCAT('%',y,'%') UNION ALL SELECT a,b,c FROM t2 WHERE a =c)

Now what I want is to achieve a WHERE statement for the united resultset..something like this:

(SELECT x AS A,y,z FROM t1 WHERE x LIKE CONCAT('%',y,'%')) UNION ALL (SELECT a AS A,b,c FROM t2 WHERE a =c)) WHERE A>='a' ORDER BY A

Note that fields x and a are of the same data type.
Please assist and revert.
Thanks in advance

 

[towerbase]

Try something like

SELECT fieldnames
  FROM
    ( SELECT fieldnames
        FROM table1
       WHERE condition1
    UNION ALL
       SELECT fieldnames
         FROM table2
        WHERE condition2
     ) AS table3name
 WHERE contition
 ORDER orderlist

If you want to get a good understanding of SQL then I recommend that you read Simply SQL by Rudy Limeback. I didn't know how to do this kind of query before I read his book.

Andrew
Hampshire, UK

 

[r937]

thanks andrew

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[dangari]

towerbase!
This is ingenious and I thank you so so much because it worked like a charm.
Thanks again.