Get all the data from a table and sometimes relate that table with another

Question

My question is: Can I do this? I try many things, some of them are:

I need all the info form Table A, and sometimes I need to join this table with Table B. My problem is that when I join both tables, if an specific parameter in Table A is not in Table B just give me the records when the specific parameter are, but I want all the records.

Table A

|--------------------------------|
|           Table A              |
|-------------------|------------|
| id_table_A | name | id_table_B |
|------------|------|------------|
|   1        | Joe  |  1         |
|------------|------|------------|
|   2        | Ben  |            |
|------------|------|------------|
|   3        | Lya  |            |
|------------|------|------------|
|   4        | luis |  2         |
|------------|------|------------|

Table B

|----------------------|
|      Table B         |
|----------------------|
| id_table_B | Elements|
|------------|---------|
|   1        |    Car  | 
|------------|---------|
|   2        |    Byke | 
|------------|---------|
|   3        |    Moto | 
|------------|---------|

What I want to show in my View is this:

|------------|------|------------|
| id_table_A | name | Elements   |
|------------|------|------------|
|   1        | Joe  |  Car       |
|------------|------|------------|
|   2        | Ben  |            |
|------------|------|------------|
|   3        | Lya  |            |
|------------|------|------------|
|   4        | luis |  Byke      |
|------------|------|------------|

My model In my model this is what I tried:

"SELECT * FROM table_A, table_B where table_A.id_table_B = table_B.id_table_B"

But this query only show me data 1 and 4. This can be done or not? Thanks in advance.

Does it not work if you do `SELECT * FROM table_A` and just `LEFT JOIN` table B? — Giedrius, Aug 29 '17 at 18:31
@Giedrius When I do `Select * from table_A left join table B` that give me a syntax error — Mary, Aug 29 '17 at 18:34

score 3 · Accepted Answer · edited Aug 29 '17 at 20:54

3

You can use left join

SELECT * 
FROM table_A
left join table_B on table_A.id_table_B = table_B.id_table_B

Left join is used when the keys between tables may not always match. In this case, the left join retrieves the correct match where it's possible and the values become NULL when not possible.

SQL LEFT JOIN Documentation

edited Aug 29 '17 at 20:54

GrumpyCrouton

8,486
7
32
71

answered Aug 29 '17 at 18:33

ScaisEdge

131,976
10
91
107

Good answer, I just wish you explained _why_ more, or at least linked to documentation instead of just "Just do this >>code paste<<" – GrumpyCrouton Aug 29 '17 at 18:40
@GrumpyCrouton answer updated with a simple but i hop clear explanation – ScaisEdge Aug 29 '17 at 18:42

score 0 · Answer 2 · answered Aug 29 '17 at 18:35

0

You need an explicit LEFT JOIN as opposed to the implicit INNER JOIN that is used when you simply list the tables like that. https://www.w3schools.com/sql/sql_join_left.asp

answered Aug 29 '17 at 18:35

Marc DePoe

43
6

Get all the data from a table and sometimes relate that table with another

2 Answers2