Union on two tables with a where clause in the one

Question

Currently I have 2 tables, both of the tables have the same structure and are going to be used in a web application. the two tables are production and temp. The temp table contains one additional column called [signed up]. Currently I generate a single list using two columns that are found in each table (recno and name). Using these two fields I'm able to support my web application search function. Now what I need to do is support limiting the amount of items that can be used in the search on the second table. the reason for this is become once a person is "signed up" a similar record is created in the production table and will have its own recno.

doing:

Select recno, name
  from production
UNION ALL
Select recno, name
  from temp

...will show me everyone. I have tried:

Select recno, name
  from production
UNION ALL
Select recno, name
  from temp
 WHERE signup <> 'Y'

But this returns nothing? Can anyone help?

I'm having some trouble understanding your question. Are you truly looking for a union, or are you looking for those record sin table 2 but not in table 1? — MJB, Apr 08 '10 at 18:51
Have you verified that there are actually records in the `temp` table matching your criteria? If you `SELECT recno, name, signup FROM temp`, what's in the `signup` column? — Aaronaught, Apr 08 '10 at 18:52
I think it's the same, MJB (assuming *I'm* understanding correctly). Any record in `temp` with `signup = 'Y'` will also be in `production`. So he just doesn't want the duplicates. — Chad Birch, Apr 08 '10 at 18:52

score 8 · Answer 1 · edited Dec 12 '11 at 19:27

8

For what you are asking, you could do it this style.

SELECT * FROM
(
    SELECT '1' as `col`
    UNION 
    SELECT '2' as `col`
) as `someAlias`
where `someAlias`.`col` = '1'

Put the entire union inside parenthesis, give it an alias, then give the condition.

edited Dec 12 '11 at 19:27

Chris Cashwell

22,308
13
63
94

answered Dec 12 '11 at 19:24

Matt

81
1
2

score 1 · Accepted Answer · answered Apr 08 '10 at 19:08

Not sure if I'm understanding what you want exactly. If you create records in the production table once they have signed up from the temp table, and you only want people who haven't signed up...you don't need to look in the production table at all. Simply:

SELECT recno, name FROM temp WHERE signup='N'

Or however you're trying to limit your search. If for some reason you do need a union but you're trying to eliminate duplicates you'd have to modify your statement to remove the ALL clause. Union ALL causes you to get duplicates. If you don't want duplicate values, you want to not use ALL in your UNION. You can read up on Unions here.

Union on two tables with a where clause in the one

2 Answers2

Linked