Rows dropping when I try to join data from two tables

Question

I have a fairly simple query I'm try to write.

If I run the following query:

SELECT parts.id, parts.type_id 
FROM parts 
WHERE parts.type_id=1 
  OR parts.type_id=2 
  OR parts.type_id=4 
ORDER BY parts.type_id;

I get all the rows I expect to be returned. Now when I try to grab the parent_unit from another table with the following query six rows suddenly drop out of the result:

SELECT parts.id, parts.type_id, sp.parent_unit 
FROM parts, serialized_parts sp 
WHERE (parts.type_id=1 OR parts.type_id=2 OR parts.type_id=4) 
  AND sp.parts_id = parts.id 
ORDER BY parts.type_id

In the past I've never really dealt with ORs in my queries so maybe I'm just doing it wrong. That said I'm guessing it's just a simple mistake. Let me know if you need sample data and I'll post some. Thanks.

In support of HLGEM's comment, see: http://stackoverflow.com/questions/2241991/in-mysql-queries-why-use-join-instead-of-where — OMG Ponies, Jun 08 '10 at 19:26

Benjamin Atkin · Answer 1 · 2010-06-08T19:28:49.577

To ensure that it has the same number of rows as without a join, use a left outer join.

http://dev.mysql.com/doc/refman/5.0/en/join.html

For example:

SELECT parts.id, parts.type_id, sp.parent_unit 
FROM parts
LEFT JOIN serialized_parts sp 
ON sp.parts_id = parts.id
WHERE (parts.type_id=1 OR parts.type_id=2 OR parts.type_id=4)
ORDER BY parts.type_id

The default type of join is an inner join. On an inner join, if there isn't a match between the joined fields in table A and table B, neither the contents of table A nor table B will be shown. The most likely scenario is that for six rows in the parts table, there is no serilized_parts row that has a parts_id that matches the parts row's id. If this is the case, doing a left outer join will reveal the rows that don't have a serialized_parts row to match them, by showing sp.parent_unit as NULL.

That would be my guess as well, that the other table does not havea record to match up to every record inteh first table. — HLGEM, Jun 08 '10 at 19:25

score 0 · Answer 2 · answered Jun 08 '10 at 19:23

Try this:

SELECT parts.id, parts.type_id, sp.parent_unit 
FROM parts
LEFT JOIN serialized_parts sp ON sp.parts_id = parts.id
WHERE parts.type_id IN (1,2,4)
ORDER BY parts.type_id

Eliminate the OR's with an IN clause, which is simpler to read and understand, and use the long form join which makes it clear whats being used in the join clause and whats being used in the where.

score 0 · Accepted Answer · answered Jun 08 '10 at 19:23

Check the value for parts.parts_id for the 6 missing rows. You probably don't have a corresponding value in sp.parts_id (or maybe the value for parts.parts_id is null). Note that it has to be an exact match for the join to work.

As an alternative to the OR string you can use parts.type_id IN (1,2,4). It works the same as the listed ORs but is easier to read.

score 0 · Answer 4 · answered Jun 08 '10 at 19:34

0

Seeing as I'm working on a development setup my database was a little messed from when I was testing stuff earlier and rows were missing.

answered Jun 08 '10 at 19:34

Ian Burris

6,325
21
59
80

Rows dropping when I try to join data from two tables

4 Answers4