Stacks of overflow Stacks of overflow - 1 month ago 9
MySQL Question

Selecting Rows from one table based on conditions in two separate tables

I've tried using joins and wherein statements for the following but I either get a timeout because it's taking too long to run or I get duplicate column name error.

I have 3 tables:
A, B, C
I'd like to create a table consisting of rows from A based on constraints in B and C. So The row in A has to fulfill a condition in B OR C:

(A.ID = B.ID and A.PURCHASE = B.PURCHASE) OR (A.ID = C.ID AND A.PURCHASE = C.PURCHASE).

I've been using mysql for around... a week and this is the closest I've gotten(it hangs):

CREATE TABLE D
SELECT T1.*
FROM TABLE A AS T1, TABLE B AS T2, TABLE B AS T3
JOIN T2, T3 ON ((T1.CUSTOMER_ID = T2.CUSTOMER_ID AND T1.DAY_ID =
T2.DAY_ID) OR (T1.CUSTOMER_ID = T3.CUSTOMER_ID AND T1.DAY_ID = T2.DAY_ID));


Thanks for any help!

Answer

Join together A, B, and C using LEFT JOIN, and then retain records from A where a match occurred in either B or C.

INSERT INTO D
SELECT DISTINCT a.*      -- remove duplicate records
FROM tableA a
LEFT JOIN tableB b
    ON a.CUSTOMER_ID = b.CUSTOMER_ID AND a.DAY_ID = b.DAY_ID
LEFT JOIN tableC c
    ON a.CUSTOMER_ID = c.CUSTOMER_ID AND a.DAY_ID = c.DAY_ID
WHERE b.CUSTOMER_ID IS NOT NULL OR    -- retain records where either
      c.CUSTOMER_ID IS NOT NULL       -- condition matches

If the D table is not already created, then create it using the same definition as for table A.