SQL join to retrieve multiple lines in right table

Question

I'm new to SQL and trying to find the correct join to use in this situation. Table A contains a series of unique (i.e. no repeats) IDs. Table B contains multiple measurements for each ID, including IDs that don't exist in table A. I need a combined table that contains all the data in table B for the IDs that are in table A.

For example:

A               B                                 JOINED:
ID              ID  | count | date                ID  | count | date
-               --  | ----- | ----                --  | ----- | ----
001             001 | 512   | 01-Aug-2014         001 | 512   | 01-Aug-2014
003             001 | 123   | 08-Aug-2014         001 | 123   | 08-Aug-2014
004             002 | 2325  | 01-Aug-2014         003 | 2325  | 01-Aug-2014
                002 | 7653  | 08-Aug-2014         003 | 423   | 08-Aug-2014
                003 | 2325  | 01-Aug-2014         004 | 2     | 01-Aug-2014
                003 | 423   | 08-Aug-2014         004 | 76    | 08-Aug-2014
                004 | 2     | 01-Aug-2014
                004 | 76    | 08-Aug-2014
                005 | 232   | 01-Aug-2014
                005 | 67    | 08-Aug-2014

Now, if I understand joins correctly, a LEFT join on A-B would produce a result that only had one line for each ID, not the multiple lines for each date. A RIGHT join would include all the IDs in B that aren't in A. Is that correct?

So, in summary, how do I get a results table like that shown above?

I would say the easiest way to go would be to go on www school and try it out yourself. Pretty sure it would be faster than formatting it on SO — Steve, Sep 16 '14 at 22:21
I did go on www school before asking. I was struggling to understand the distinction between all the different joins. — thornate, Sep 16 '14 at 22:35

M.Ali · Accepted Answer · 2014-09-16T22:29:39.333

SELECT A.ID 
      ,B.[Count]
      ,B.[Date]
FROM TableA A INNER JOIN TableB B 
ON A.ID = B.ID

INNER JOIN will give you the matching rows between Two tables.

RIGHT JOIN will give you matching rows from the table on left side of join key word and all rows from the table on right side of the join key word.

LEFT JOIN will give you matching rows from the table on right side of join key word and all rows from the table on left side of the join key word.

FULL JOIN will return all the matching and non matching rows from both tables.

Matching Row Joining condition defined in ON condition will map each value in ID column in tableA to each matching value in ID column of tableB.

score 2 · Answer 2 · answered Sep 16 '14 at 22:15

2

Inner join is the one you are looking for

As for your questions. If I understood it correctly, then the answer is no. Left join would be an inner join + anything that LHS table has but not in the RHS, it will fill in the blank with null, similarly for Right join.

answered Sep 16 '14 at 22:15

Steve

11,696
7
43
81

But won't an inner join only produce a single line for each ID? – thornate Sep 16 '14 at 22:16
Oh, wait... do you mean INNER JOIN without the 'LEFT' specifier? – thornate Sep 16 '14 at 22:18
@thornate no, inner join would match all the rows which the ON condition meets. And yes...just INNER JOIN – Steve Sep 16 '14 at 22:19

score 1 · Answer 3 · answered Sep 16 '14 at 22:22

1

Hope this will help you.

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;

answered Sep 16 '14 at 22:22

bl2b

123
4

score -1 · Answer 4 · answered Mar 10 '17 at 12:45

-1

This will get all rows from B which IDs match in A. will also return all rows in A so echo what you want to print

SELECT* FROM A RIGHT OUTER JOIN B ON B.ID=A.ID ORDER BY ID ASC;

Hope it helps you

answered Mar 10 '17 at 12:45

Njoroge Mathu

45
6

SQL join to retrieve multiple lines in right table

4 Answers4