CODEWITHSUNDEEP
mysqlsql
Febry Fairuz
Febry Fairuz

Reputation: 549

How to join 2 table if the second table has no matching information

Hy, i have a table like this :

Table colors :
----------------------------
| colorsid   code    name  |
----------------------------
|   1         aaa    yellow|
|   2         bbb    red   |
|   3         ccc    green |
----------------------------

Table pictures :
---------------------------
| picid   file    typeid  |
---------------------------
|   5     ooo.png    1    |
|   6     ccc.jpg    2    |
---------------------------

Result should like this :

------------------------------------------------------
| colorsid   code    name     picid   file     typeid|
------------------------------------------------------
|   1         aaa    yellow     5      ooo.png    1  |
|   2         bbb    red        6      ccc.png    2  |
|   3         ccc    green                           |
------------------------------------------------------

I tried to use sql join (left,right,cross,inner) but the result always showing 2 record. Can anyone help me ?

Upvotes: 0

Views: 37

Answers (2)

Mike Robinson
Mike Robinson

Reputation: 8945

To clarify the above:

JOIN, by itself, is synonymous with *INNER* JOIN. The result is to consist only of matching rows. Only rows which have a value in both tables will be returned.

There are, however, two flavors of *OUTER* JOIN:   LEFT, and RIGHT.

In this case, all of the rows from the table (the table on the LEFT side, or the one on the RIGHT side, of the JOIN ...) will be included in the result set. Values from the other-side table will be included if a matching row exists in that table; otherwise, the values in those columns will be NULL.

This seems to be exactly what you're looking for here ...

Upvotes: 0

bill
bill

Reputation: 1656

SELECT *
FROM colors
LEFT JOIN pictures ON
colors.id = pictures.typeid

Upvotes: 1

Related Questions