Search code examples
mysqlsqlview

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

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:

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

Solution

  • 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