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.

Why are you using comma-join syntax. That's SO old-fashioned! — Strawberry, Jan 23 '14 at 20:09
May I lead you to the great explanation page for joins? http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-join/38578#38578 — AIXroot, Jan 23 '14 at 20:12

FrankPl · Accepted Answer · 2014-01-23T20:16:39.667

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.

Is it possible to concatenate 2 joins? I mean, I want the users with status = 0 be ordered by surname but users with status = 1 get ordered by dateSigned. I think I have to make two queries to get status 0 and status 1 — Biribu, Jan 23 '14 at 20:06

Fabien Warniez · Answer 2 · 2014-01-23T20:10:19.760

1

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

edited Jan 23 '14 at 20:10

answered Jan 23 '14 at 19:57

Fabien Warniez

2,731
1
21
30

That works. DO you know how to add a new field with diferent value each part of the join? the yes/no field – Biribu Jan 23 '14 at 20:00
@FabienWarniez You are missing the sorting as described in the question. – FrankPl Jan 23 '14 at 20:05

Mysql query with joins

2 Answers2