How to Select records from table inside another table and making joing

Question

I'd really appreciate some help with an SQL query across tables. I have:

Table A1 inside Table A
B1 inside Table B. I want to select columns from table A1 that have a corresponding tag in table

A1 table

Id       type message
1        2    'hello'
102      0    'bye'
302      2    'hey'

B1 Table

Id      data     refId   
1       70       102
2       6        2
3       8        302

Expected result:

Id       type data
1        2    
102      0    70
302      2    8

What i did:

SELECT Id, type, B.B1.data 
FROM A.A1 a
INNER JOIN B.B1 b ON a.id = b.refid
;

Does this answer your question? [What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?](https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join) — angel.bonev, May 02 '23 at 11:09
https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms — Reporter, May 02 '23 at 11:12
Based on this [db fiddle](https://www.db-fiddle.com/f/fw6Xw2PWvArWAyRb4sYzY5/0) `Id` column is ambiguous between tables `A1` and `B1`, hence you should prefix it with either `a.` or `b.` in `SELECT` list. — orhtej2, May 02 '23 at 11:16
But, more import then telling you how to fix this, is you telling us which error you got. Future readers can then see if this question/answer can interest them because it's about a subject they can learn from. — Luuk, May 02 '23 at 11:18

SelVazi · Accepted Answer · 2023-05-02T11:30:55.040

0

You have to use left join instead of inner join, to returns all rows from the left table A1, even if there are no matches in the right table B1.

SELECT a.Id, a.type, b.data 
FROM A.A1 a
LEFT JOIN B.B1 b ON a.id = b.refid

NB : Use aliases for better readability

edited May 02 '23 at 11:30

answered May 02 '23 at 11:12

SelVazi

1

and when you are using PostgreSQL, you can see demo here: https://dbfiddle.uk/nLl--3rX (I was just ready when this was posted...) – Luuk May 02 '23 at 11:16

1 Answers1