CODEWITHSUNDEEP
mysql
anche
anche

Reputation: 2884

MYSQL results need to combine tables

I'm scripting a tool with PHP and I've illustrated a situation with MYSQL. I have two tables which I need to combine to get the results I want.

I came up with this:

SELECT * FROM names, dogs WHERE dog_id = dogs.id;

but when I do this, the results do NOT include the rows where dog_id = NULL. And I need those results too.

TABLE NAMES
|  *id*  | *name*  |  *dog_id* |  
|   1    |  name1  |    NULL   |
|   2    |  name2  |     1     |
|   3    |  name3  |     NULL  |
|   4    |  name4  |     2     |  

TABLE DOGS  
|  *id*  |  *dog*  | 
|   1    |  dog1   |  
|   2    |  dog2   |

How to get these results:

RESULTS
|  *id*  |  *name*  |  *dog_id* |  *dog*  |
|   1    |   name1  |    NULL   |   NULL  |
|   2    |   name2  |    1      |   dog1  |
|   3    |   name3  |    NULL   |   NULL  |
|   4    |   name4  |    2      |   dog2  |

Upvotes: 2

Views: 57

Answers (1)

Mark Byers
Mark Byers

Reputation: 838176

Use an outer join:

SELECT names.id, names.name, names.dog_id, dogs.dog
FROM names
LEFT JOIN dogs
ON dog_id = dogs.id;

Upvotes: 2

Related Questions