CODEWITHSUNDEEP
mysqlsql
Georg Leber
Georg Leber

Reputation: 3605

Select maximum from related table

In my application I want to select all data from table1 and the maximum id of the related table2. The problem is, that the SQL works fine if there is a entity in table2. But if not the restulset is filled with NULL:

SELECT table1.*, CONCAT(table1.number, '-', table1.item_id) AS IdNumber, MAX(table2.id) AS Table2Id
  FROM table1
  LEFT JOIN table2 ON (table1.ID = table2.EQUIP_ITEM_ID)
WHERE table1.GROUP_ID > 0
  AND table2.IN_PROGRESS = 1
ORDER BY table1.ID ASC
LIMIT 15

What do I need to change, to retrieve the correct result set?

Upvotes: 1

Views: 126

Answers (3)

user1514290
user1514290

Reputation: 41

SELECT table1.*, CONCAT(table1.number, '-', table1.item_id) AS IdNumber, MAX(table2.id) AS Table2Id

FROM table1

LEFT JOIN table2 ON (table1.ID = table2.EQUIP_ITEM_ID)

WHERE table1.GROUP_ID > 0

AND table2.IN_PROGRESS = 1

AND IS NOT NULL (table2.IN_PROGRESS)

ORDER BY table1.ID ASC

LIMIT 15

It must be ok ;)

Upvotes: 1

Sashi Kant
Sashi Kant

Reputation: 13465

Try this::

SELECT table1.*, CONCAT(table1.number, '-', table1.item_id) AS IdNumber, MAX(IFNULL(table2.id),0) AS Table2Id
  FROM table1
  LEFT JOIN table2 ON (table1.ID = table2.EQUIP_ITEM_ID)
WHERE table1.GROUP_ID > 0
  AND table2.IN_PROGRESS = 1
ORDER BY table1.ID ASC
LIMIT 15

Upvotes: 1

Holger Brandt
Holger Brandt

Reputation: 4354

The problem is with the second WHERE condition where table2.IN_PROGRESS = 1

If you still want table1 data to show up, then amend the SQL to

(table2.IN_PROGRESS = 1 OR table2.IN_PROGRESS IS NULL)

If you want to show 0 as a value then change MAX(table2.id) to COALESCE(MAX(table2.id),0)

Upvotes: 1

Related Questions