CODEWITHSUNDEEP
mysqlinner-join
user1502852
user1502852

Reputation:

MySQL inner join help - want to include rows where there is no match

I have a few tables I am joining in a look-up.

(PET)

| id | owner_id |  pet_name   | size |
--------------------------------------
| 1  |    1     |   Sparky    |  L   |
| 2  |    1     |    Spot     |  L   |
| 3  |    3     |   FooFoo    |  M   |
| 4  |    3     |    Barky    |  L   |
| 5  |    3     |    Jeb      |  S   |

(OWNER)

| id |  owner_name  |
--------------------
| 1  |    Jeff      |
| 2  |   (Kathy)    |
| 3  |    Tom       |

When I do an inner join like this:

SELECT 

OWNER.id, OWNER.owner_name, PET.pet_name, PET.size

FROM OWNER

INNER JOIN PET ON PET.owner_id = OWNER.id

I get a result like this:

| id |  owner_name  |  pet_name   | size |
------------------------------------------
| 1  |     Jeff     |   Sparky    |  L   |
| 1  |     Jeff     |    Spot     |  L   |
| 3  |     Tom      |   FooFoo    |  M   |
| 3  |     Tom      |    Barky    |  L   |
| 3  |     Tom      |    Jeb      |  S   |

Kathy is not in the result. I understand why. But I want Kathy to be in the result. I want all owners to show up regardless of if they have a pet or not - and the ones that do merge as they have with this join.

How do I get all of the OWNER table to display all owners in a join with the PET table - regardless of if they have a pet in PET table?

Thanks everyone :)

Upvotes: 1

Views: 3203

Answers (4)

DiMono
DiMono

Reputation: 3368

You're identifying the tables in the wrong order. Start with the one you want all the results from, and then left join the other table to it. That way you're looking at owners and their dogs, rather than dogs and their owners.

FROM OWNER
LEFT JOIN PET
ON PET.owner_id = OWNER.id

Upvotes: 0

David
David

Reputation: 73564

Use an Outer Join

SELECT 

OWNER.id, OWNER.owner_name, PET.pet_name, PET.size

FROM OWNER

Outer JOIN PET ON PET.owner_id = OWNER.id

Upvotes: 0

David W
David W

Reputation: 10184

You need to make your join a LEFT OUTER JOIN rather than an INNER JOIN.

SELECT OWNER.id, OWNER.owner_name, PET.pet_name, PET.size
  FROM OWNER
  LEFT OUTER JOIN PET 
    ON PET.owner_id = OWNER.id

Remember that a left outer returns all records on the "left" side of your join even if there is no matching record on the "right" side of the join, returning "null" values for those field(s). An "inner" join, as you can see, only returns records where there are matches on both the left AND right sides.

Hope that gives you a push in the right direction!

Upvotes: 5

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Use LEFT JOIN instead of INNER

SELECT
  OWNER.id,
  OWNER.owner_name,
  PET.pet_name,
  PET.size
FROM OWNER
  LEFT JOIN PET
    ON PET.owner_id = OWNER.id

Upvotes: 8

Related Questions