MYSQL - SubSelect when FK does and doesnt exists

Question

Situation Overview

The current question is a problem about selecting values from two tables table A (material) and table B (MaterialRevision). However, The PK of table A might or Might not exist in Table B. When it doesnt exists, the query described in this question wont return the values of table A, but IT SHOULD. So basically here's whats happening :

The query is only returning values when A.id exists in B.id, when In fact, I need it to return values from A when A.id ALSO dont exist in B.id.

Problem:

Suppose two tables. Table Material and Table Material Revision. Notice that the PK idMaterial is a FK in MaterialRevision.

Current "Mock" Tables

Query Objective

Obs: remember these two tables are a simplification of the real tables.

For each Material, print the material variables and the last(MAX) RevisionDate from MaterialRevision. In case theres no RevisionDate, print BLANK ("") for the "last revision date".

What is wrongly happening

For each Material, print the material variables and the last(MAX) RevisionDate from MaterialRevision. In case theres no Revision for the Material, doesnt print the Material (SKIP).

Current Code

SELECT 
    Material.idMaterial,
    Material.nextRevisionDate,
    Material.obsolete,
    lastRevisionDate
FROM Material,

 (SELECT MaterialRevision.idMaterial, max(MaterialRevision.revisionDate) as "revisionDate" from MaterialRevision  
    GROUP BY  MaterialRevision.idMaterial
 ) AS Revision

 WHERE (Material.idMaterial = Revision.idMaterial AND Material.obsolete = 0)

References and Links used to reach the state described in this question

Why is MAX() 100 times slower than ORDER BY ... LIMIT 1?

MySQL get last date records from multiple

MySQL - How to SELECT based on value of another SELECT

MySQL Query Select where id does not exist in the JOIN table

PS I hope this question is correctly understood since it took me a lot of time to build it. I researched a lot in stackoverflow and after several failed attempts I had no option but to ask for help.

Jean-Marc Zimmer · Accepted Answer · 2018-10-31T14:49:52.347

1

You should use JOIN :

SELECT m.idMaterial, m.nextRevisionDate, mr.revisionDate AS "lastRevisionDate"
FROM Material m
LEFT JOIN MaterialRevision AS mr ON mr.idMaterial = m.idMaterial AND mr.revisionDate = (
    SELECT MAX(ch.revisionDate) from MaterialRevision ch
    WHERE mr.idMaterial = ch.idMaterial)
WHERE m.obsolete = 0

Here is an explanation of what INNER JOIN, LEFT JOIN and RIGHT JOIN are. (You will love them if you often cross tables in your queries)

As m.obsolete will always be true, I ommited it in the SELECT clause

edited Oct 31 '18 at 14:49

answered Oct 30 '18 at 16:21

Jean-Marc Zimmer

537
6
20

Thanks for the reply. But this query only returns the material with the MAX revision Date. When Actually I need to return everyMaterial and only the revision with the MAX revisionDate. – KenobiBastila Oct 30 '18 at 16:29
Strange. It shall return all obsolete `Material` that have at least one `MaterialRevision`... – Jean-Marc Zimmer Oct 30 '18 at 16:34
It must return materials that also doesnt material Revisions – KenobiBastila Oct 30 '18 at 16:37
OK, so you might replace `INNER JOIN` with `LEFT JOIN`. My bad, I misunderstood your problem. – Jean-Marc Zimmer Oct 30 '18 at 16:38
Still only returns 1 value. – KenobiBastila Oct 30 '18 at 17:01
The problem seems to be " MAX(...)" – KenobiBastila Oct 30 '18 at 17:10
1

I figured out the issue. I Will mark your answer as correct after you edit it. I will post my answer so you can edit it. I really appreciate your answer. Without you, I wouldnt be able to solve it. Thanks a lot. Kudos for you Sir. – KenobiBastila Oct 30 '18 at 17:18

Alessandro Grassi · Answer 2 · 2018-10-31T12:47:47.377

1

You should use the left outer join instead of using the cross product.

You're query should be something like this:

SELECT idMaterial, nextRevisionableDate, obsolete, 
revisionDate AS lastRevisionDate
FROM Material
LEFT OUTER JOIN MaterialRevision AS mr On 
Material.idMaterial = MaterialRevision.id
AND mr.revisionDate = (SELECT MAX(ch.revisionDate) from MaterialRevision ch 
WHERE mr.idMaterial = ch.idMaterial)
WHERE obsolete = 0;

Here you can find some documentation about types of join.

edited Oct 31 '18 at 12:47

answered Oct 30 '18 at 16:25

Alessandro Grassi

139
9

Sorry but this only returns one value. – KenobiBastila Oct 30 '18 at 17:01
Yes my mistake this only returns the row with the highest revisionDate. – Alessandro Grassi Oct 31 '18 at 12:40
I should use the max in the join as you did in your answer, now I edit mine – Alessandro Grassi Oct 31 '18 at 12:43

MYSQL - SubSelect when FK does and doesnt exists

2 Answers2