In a Join, How to Ensure Each Group In Table B Joins to Every Row in Table A-CodePudding

My SQL dialect is MySQL. I am creating a view on two tables.

I have TableA which looks like this:

| date | value |
|------|-------|
| 1    | 100   |
| 2    | 150   |

I have TableB which looks like this:

| date | group | name  |
|------|-------|-------|
| 1    | d     | alice |
| 1    | e     | bob   |
| 2    | d     | clark |
| 2    | e     | mick  |

I want to do a join and combine them on the date column, in such a way the each group in B gets a join on A. To be more clear, here's what the output data should look like:

| date | group | value | name  |
|------|-------|-------|-------|
| 1    | d     | 100   | alice |
| 1    | e     | 100   | bob   |
| 2    | d     | 150   | clark |
| 2    | e     | 150   | mick  |

So this is a pretty contrived example, just to make it easy to read in markdown. But some details about my actual problem:

I have a large of number of groups, so many nested derived tables is not feasible, and would slow the view query time too much.
I need to generate columns that are some function of both tables (e.g. some output like value name, which is obviously nonsensical with this example's types)

I have tried using:

SELECT value, name, group
FROM TableA
RIGHT OUTER JOIN TableB
ON TableA.date = TableB.date

The output in this case just takes one of the groups, and ditches the rest.

What is the syntax for this?

CodePudding user response：

Based on the data sample you've shared, it looks like you're looking for an INNER JOIN instead of a RIGHT JOIN.

SELECT value, name, group
FROM TableA
JOIN TableB
 ON TableA.date = TableB.date