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?
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.