CODEWITHSUNDEEP
mysqlinner-join
Nick R
Nick R

Reputation: 13

Joining 3 tables in mysql where data does not live in both joins

I am having an issue joining 3 tables where the data i need is one of two tables, but not both.

So lets say we have table 'customers' with columns 'id', 'city', 'state', and 2 data tables data_a and data_b with column 'id' and the data i want.

right now i have a statement similar to the below.

SELECT cust.id, cust.city, cust.state, a.info as infoa, b.info as infob
  FROM customers cust
  INNER JOIN data_a a 
    ON cust.id = a.id
  INNER JOIN data_b b
    ON cust.id = b.id
  WHERE cust.city = 'mytown';

This takes forever to run, and I have not gotten any responses from the query since i will kill it before it finishes (if it will ever actually finish).

If I remove the second data table the statement works fine, only when I added it do i run into issues. All the id columns are indexed, as well as the city column.

Or am I better off creating a temp table and doing 2 joins, inserting those into the temp and reading from the temp?

edit: fixed the typo

If i do left joins the query sits at executing, presumably indefinitely. When using Inner Joins I will get a empty set.

Upvotes: 1

Views: 104

Answers (1)

Marcus Adams
Marcus Adams

Reputation: 53830

If the join will be on one table, but not both, then you need LEFT JOIN:

SELECT cust.id, cust.city, cust.state, a.info as infoa, b.info as infob
  FROM customers cust
  LEFT JOIN data_a a 
    ON a.id = cust.id
  LEFT JOIN data_b b
    ON b.id = cust.id
  WHERE cust.city = 'mytown';

Ensure that you have the following indexes:

cust: ('city')
data_a: ('id')
data_b: ('id')

Upvotes: 1

Related Questions