Mysql query with joins

Question

I am not able to make a query to get some crossed values. My knowledge of mysql is not so good to make this kind of things.

I have two tables, signedup and signedupLocal. Both with same fields: name, surname1, surname2, NIF, year,city, status and dateSigned.

I want to get all values from both tables together when the status is 0 in both ordered by surname1 and surname2 (A to Z). ALso I want to have a new field called for example "app" in which it says yes or no depending on which table the data came. If signedpupLocal, yes else no.

After that, I want to do the same thing when status is 1 ordered by dateSigned (older first).

I haven't tried to go so far to test a complete query because I have problems with just part of it.

I already tried something like:

SELECT signedupLocal.name,
       signedupLocal.surname1, 
       signedupLocal.surname2,
       signedupLocal.NIF,
       signedupLocal.year, 
       signedupLocal.city, 
       signedup.name, 
       signedup.surname1, 
       signedup.surname2, 
       signedup.NIF, 
       signedup.year, 
       signedup.city  
FROM signedup, signedupLocal
WHERE signedup.id_Event = 78 
AND signedupLocal.id_Event = 78
AND signedupLocal.status = 0
AND signedup.status = 0

But it fails.

Answer 1

SELECT name, surname1, surname2, NIF, year, city, status, 'no' as app,
       case when status = 1 then datesigned else '2099-12-31' end as datesigned_sort,
       case when status = 0 then surname1 else '' end as surname1_sort,
       case when status = 0 then surname2 else '' end as surname2_sort
FROM signedup
WHERE id_Event = 78 AND status in(0,1)

UNION ALL

SELECT name, surname1, surname2, NIF, year, city, status, 'yes' as app,
       case when status = 1 then datesigned else '2099-12-31' end as datesigned_sort,
       case when status = 0 then surname1 else '' end as surname1_sort,
       case when status = 0 then surname2 else '' end as surname2_sort
FROM signedupLocal
WHERE id_Event = 78 AND status in(0,1)

ORDER BY 7 asc, 9 desc, 10 asc, 11 asc

Note that when using UNION, you need to use column numbers, not column names in the ORDER BY. And I used UNION ALL as it is more efficient than UNION, as the latter remove duplicates - which causes effort for the database engine. So if you know there are none, you can avoid this.

For the sorting, I use a constant for the cases where the record should not be sorted by a sort column.

Answer 2

Use a UNION of 2 queries to concatenate the results.

SELECT * FROM (
SELECT *, "signedup" AS tablename FROM signedup WHERE …
UNION
SELECT *, "signedupLocal" AS tablename FROM signedupLocal WHERE …
) U
ORDER BY U.dateSigned DESC