CODEWITHSUNDEEP
mysqlsqljoinleft-joininner-join
Carlos2W
Carlos2W

Reputation: 2544

MySQL LEFT Join with multiple possibilities and Inner Join

I don't know if a similar question have been asked, but I looked fow more than hour on mysql in stackoverflow

My problem is, i have multiple tables and I need to join them with both left join and inner join in mysql

Entity table :

id (key) | entityName

1        |   john
2        |   harris
3        |   henry
4        |   mark
5        |   dom

Activity table 

id (key) | entityID  | status

1        |       1   |   1
2        |       2   |   0
3        |       4   |   1

Geodata table

id (key) | entityID  | moment (timestamps when the entry was done)

1        |       1   | 1429542320 (smaller)
2        |       1   | 1429542331 (bigger)
3        |       2   | 1429542320 (smaller)
4        |       2   | 1429542331 (biger)
5        |       4   | 1429542331 (bigger)

Info table

id (key) | entityID | infos | date 

   1     |      1   |  xxx  |   today
   2     |      1   |  xxx  |   yesterday
   3     |      2   |  xxx  |   today
   4     |      2   |  xxx  |   yesterday
   5     |      3   |  xxx  |   yesterday
   6     |      5   |  xxx  |   today
   7     |      5   |  xxx  |   yesterday
   8     |      5   |  xxx  |   tomorrow

So basically, I need every Entities that has an info for today Moreover, if their status is true (or 1) (from activity table), show me their date in geodata table.

So this is what i've got :

SELECT e.id, 
       e.entityName, 
       i.infos, 
       a.status, 
       MAX(g.moment) -- but the max only if status =1 
  FROM entities AS e
  LEFT JOIN activity AS a ON a.entityID = e.id
  LEFT JOIN geodata AS g ON g.entityID = e.id
 INNER JOIN infos AS i ON e.id = i.entityID
 WHERE i.date = 'today'
 GROUP BY e.id

I want every entities that has an info about today, but some of them have activity too, so i want to show it (if it doesn't just let the left join put NULL) If the status is 0, I don't need the moment, but if its true, I only need the bigger one (its numbers, so Max() should do it but it breaks)

The expected results is :

id (key) | entityName | infos |   status |   MAX(moment) | ..other columns

   1     |     john   |  xxx  |      1   | 1429542331 (the bigger one)
   2     |     harris |  xxx  |      0   | NULL
   5     |     dom    |  xxx  |     NULL | NULL

If someone can help me, I'll be very thankful :) PS.: Sorry for my english, it isn't my first language

Upvotes: 2

Views: 1388

Answers (1)

Uueerdo
Uueerdo

Reputation: 15951

You could change the MAX(g.moment) to IF(a.status<>1, NULL, MAX(g.moment))

or alternately change LEFT JOIN geodata AS g ON g.entityID = e.id to LEFT JOIN geodata AS g ON a.entityID = e.id AND a.status = 1

Which one is faster will probably depend on your actual data; the second may be faster as less records are joined, but the more complicated join condition it uses might slow down the joining.

Upvotes: 2

Related Questions