0

I have three tables:

events:

id  |  name     
-------------------
1   |  Test
2   |  Another test

persons:

type            |  type_id  | name   |   user_id
------------------------------------------------
event_organizer |   318     |  NULL  |     22
event_owner     |   318     |  Rob   |     NULL 
event_owner     |   318     |  NULL  |     6

user:

id  |  forname  |  surname
--------------------------
6   |  Peter    |   Watson
7   |  Chris    |   Brown
22  |  Charlie  |   Teck

(Of course, the tables are much bigger than that, I just copied the relevant parts.)

This query:

SELECT event.*, 
  IF(persons.type='event_organizer', CONCAT_WS(', ', GROUP_CONCAT(forname, ' ', surname SEPARATOR ', '), GROUP_CONCAT(persons.name SEPARATOR ', ')), NULL) AS organizer_names, 
  IF(persons.type='event_owner', GROUP_CONCAT(forname, ' ', surname SEPARATOR ', '), NULL) AS owner_names 
FROM event 
LEFT JOIN persons ON persons.type_id = event.id AND (persons.type = 'event_owner' OR persons.type = 'event_organizer') 
LEFT JOIN user ON user.id = persons.user_id 
WHERE event.id=?

should output me all event data and the names of the owner and the organizer. The output I get is:

Array ( [id] => 318 [name] => Test [organizer_names] => Peter Watson, Rob, Charlie Teck [owner_names] => )

I don't get why owner_names is always empty. If I remove the organizer_names part:

SELECT event.*, 
  IF(persons.type='event_owner', GROUP_CONCAT(forname, ' ', surname SEPARATOR ', '), NULL) AS owner_names 
FROM event 
LEFT JOIN persons ON persons.type_id = event.id AND persons.type = 'event_owner' 
LEFT JOIN user ON user.id = persons.user_id 
WHERE event.id=?

Than I get the right owner (Rob and Peter Watson). I can also change it to:

SELECT event.*, 
  IF(persons.type='event_organizer', CONCAT_WS(', ', GROUP_CONCAT(forname, ' ', surname SEPARATOR ', '), GROUP_CONCAT(persons.name SEPARATOR ', ')), NULL) AS organizer_names, 
  IF(persons.type='event_owner', GROUP_CONCAT(forname, ' ', surname SEPARATOR ', '), NULL) AS owner_names 
FROM event 
LEFT JOIN persons ON persons.type_id = event.id AND persons.type = 'event_owner' 
LEFT JOIN user ON user.id = persons.user_id 
WHERE event.id=?

And this is working right too. So it seems that the second OR condition of the LEFT JOIN is destroying my owners :(

The reverse test (with same condition and without organizer_names):

SELECT event.*, 
  IF(persons.type='event_owner', GROUP_CONCAT(forname, ' ', surname SEPARATOR ', '), NULL) AS owner_names 
FROM event 
LEFT JOIN persons ON persons.type_id = event.id AND (persons.type = 'event_owner' OR persons.type = 'event_organizer') 
LEFT JOIN user ON user.id = persons.user_id 
WHERE event.id=?

brings also no owner_names output.

Where is my mistake?

Improve this question
4
  • 4
    I love MySQL for allowing aggregate functions without GROUP BY clauses, and the random output it then produces. Best database ever <3 Read this article please, from beginning to end. Especially the part about 'non-deterministic nonsensical queries'. Commented Jan 15, 2016 at 11:39
  • What would you change than? Commented Jan 15, 2016 at 11:41
  • 2
    Add an actualy GROUP BY clause as required by aggegrate functions. Commented Jan 15, 2016 at 11:43
  • Thanks for the link! I added a GROUP BY event.id. But it didn't change the output. What else does I have to group? Commented Jan 15, 2016 at 12:02

2 Answers 2

Reset to default
1

Using GROUP BY aggregate functions without a GROUP BY clause is valid SQL. The standard says a single group is created from all the selected rows.

For this specific question, the appropriate GROUP BY clause would be GROUP BY event.id. However, it doesn't help because the WHERE clause already filters only the rows having a single value for event.id.

To debug the query remove all the aggregate functions in the SELECT clause and check that the correct rows are selected:

SELECT event.*, persons.*, users.*
FROM event 
LEFT JOIN persons ON persons.type_id = event.id AND (persons.type = 'event_owner' OR persons.type = 'event_organizer') 
LEFT JOIN user ON user.id = persons.user_id 
WHERE event.id=?

Conceptually, this is the first step executed by MySQL when it runs your original query. In practice, some shortcuts are taken and optimizations are done and the result set you get is not generated completely; but this is how it works in theory.

Most probably, the rows are correctly joined and filtered and the problem is in the way the values are aggregated.

Let's try to analyze how MySQL computes this expression:

IF(
    persons.type='event_owner', 
    GROUP_CONCAT(forname, ' ', surname SEPARATOR ', '),
    NULL
) AS owner_names

It uses all the rows selected by the query displayed above to compute a single value for the above expression. If you run the query you can see it produces rows that contain both 'event_owner' and 'event_organizer' in the persons.type column.

From the inside out, it uses all the values returned in the forname and surname columns to compute GROUP_CONCAT(forname, ' ', surname SEPARATOR ', ') then it uses one value from the persons.type column to check the IF() condition. But which one?

It is entitled to pick whatever value it wants for it from all the possible values of the persons.type column selected on the previous step. If, by chance, it uses 'event_owner' it returns the value you expect and you think the query is correct. When it uses 'event_organizer', well, you are puzzled and ask on StackOverflow ;)

The correct expression of owner_names is:

GROUP_CONCAT(
  IF(                      # for each row
    persons.type='event_owner', 
    CONCAT(forname, ' ', surname),       # full name for owner
    NULL                                 # nothing for organizer
  )                        # owner name or NULL
  SEPARATOR ', '           # concatenate all using separator
) as owner_names

In a similar fashion you can compute the organizer names.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you very much! (Answer and your corrections) My query: SELECT event.*, GROUP_CONCAT(IF(persons.type='event_owner', CONCAT(forname, ' ', surname), NULL) SEPARATOR ', ') as owner_names, GROUP_CONCAT(IF(persons.type='event_organizer', IF(persons.user_id IS NULL, persons.name, CONCAT(forname, ' ', surname)), NULL) SEPARATOR ', ') as organizer_names FROM event LEFT JOIN persons ON persons.type_id = event.id AND (persons.type = 'event_owner' OR persons.type = 'event_organizer') LEFT JOIN user ON user.id = persons.user_id WHERE event.id=?
1

I can't test this - SQLFiddle appears broken right now - but I think you need to explicitly join organizers and owners as separate joins. Something like: 

SELECT event.*, 
organizers.*,  
IF(organizers.type='event_organizer', CONCAT_WS(', ', GROUP_CONCAT(forname, ' ', surname SEPARATOR ', '), GROUP_CONCAT(persons.name SEPARATOR ', ')), NULL) AS organizer_names, 
owners.* ,
IF(owners.type='event_owner', GROUP_CONCAT(forname, ' ', surname SEPARATOR ', '), NULL) AS owner_names 
FROM event 
LEFT JOIN persons organizers ON organizers.type_id = event.id AND organizers.type = 'event_organizer'
left join persons owners on owners.type_id = event.id and owners.type = 'event_owner'
LEFT JOIN user ON user.id = owners.user_id 
left join user u2 on u2.id = organizers.user_id
WHERE event.id=?;
Improve this answer

1 Comment

I had to give forname, surname and name the right table. Now I get the right names but they are all doubled -.- So f.e. organizer_names = {"Charlie Teck", "Charlie Teck"}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.