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
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));
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.