Union on two tables with a where clause in one

Question

I currently have 2 tables, both tables have the same structure and will be used in the web application. two tables are production and pace. The temp table contains one additional column named [signed up]. I am currently creating one list using the two columns that are in each table (name and name). Using these two fields, I can support the web application search function. Now I will need to limit the number of elements that can be used in the search in the second table. the reason for this is that when a person is "registered", a similar record is created in the worksheet and will have its own recno.

doing:

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

... will show me all. I tried:

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

But does this return nothing? Can anyone help?

Answer 1

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

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

Or, nevertheless, you are trying to limit your search. If for some reason you need an alliance, but you are trying to eliminate duplicates, you will have to change your statement to remove the ALL clause. The ALL alliance forces you to receive duplicates. If you do not want to duplicate values, you do not want to use EVERYTHING in your UNION. You can read on Unions here .

Answer 2

For what you ask, you can do it in this style.

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

Put the whole union in brackets, give it an alias, then give a condition.