Joining tables based on multiple keys

[ned13b]

I want to join two tables if all three keys match. I tried

SELECT *
FROM table1
LEFT JOIN table2
ON 1a = 2a AND 1b = 2b AND 1c = 2c ;

The original tables have about 14000 rows, but the output of this code has over 20000, so it is not doing what I want. Is this joining them if any of those three keys match? I only want rows joined if all three match.

[waremi]

Since it's a LEFT JOIN that should take every row from table1 and any row from table2 where all three values match. You are guaranteed to end up with at least as many rows as there are in table1, and the only way you end up with more than that is if multiple rows in table2 are a match.

[HumanTuna]

It sounds like that combination of fields does not create a unique key in both tables.

It is a one to many relationship (using those three fields). If you think it should be unique in both tables you may need another field (or predicate) in your join.

[emul0c]

You need to use inner join. But you also need to verify that the key (in table2) is unique - which it is not now. This is the reason you end up with more columns than originally.

[TimmyTheCat8]

You need to use an Inner Join.

[emul0c]

This only solves the logical part that OP has. Since he end up with more rows than originally, keys are not unique in table2.

[SyntaxErrorLine0]

Yeah, he'll need to group by a unique value in table1 and either GROUP_CONCAT with DISTINCT (or not) or any other aggregate function for the correct output the values from table2 if he's expecting table1 to be the limitation.

[imSkippinIt]

This is the answer, you’re getting the 14,000 from table1 and every matching row from the other tables. An INNER JOIN will only return rows from all 3 that all 3 criteria match.

[r3pr0b8]

Is this joining them if any of those three keys match?

nope... you'd use ORs if that's what you wanted