CODEWITHSUNDEEP
mysqlsql
John
John

Reputation: 1015

Issue with using 2 INNER JOINS within 1 mySQL Query

I have the following query that is doing an INNER JOIN within a select query to insert records into the table. The issue is instead of using the WHERE clause and listing the 3 t2.device I would like to see, I need to do another INNER JOIN with a table called tbl_Full_List.

Both tbl_Full_List and tbl1_device have a column called device so instead of the WHERE clause I would like it to show only devices that match in both tbl_Full_List and tbl1_device 

INSERT INTO TEMP_Table1 (place, device, description, quantity)
            (SELECT t1.place, t2.device, t2.description, Sum(t2.quantity) AS quantity
            FROM Device_Table AS t1
            INNER JOIN tbl1_device AS t2
            ON t1.rma = t2.rma
            WHERE t2.device IN ('PRT1030', 'PRT-23','PRT-20139')
            AND t1.date_made = Current_Date()
            GROUP BY t1.place, t2.device, t2.description ORDER BY place ASC, device ASC )

Upvotes: 0

Views: 38

Answers (1)

xQbert
xQbert

Reputation: 35333

Without knowing how the two relate... I'm assuming FL.ID is the PK of full list and we have a Foreign Key to tbl1_devcice called FL_ID

INSERT INTO TEMP_Table1 (place, device, description, quantity)
(SELECT t1.place, t2.device, t2.description, Sum(t2.quantity) AS quantity
 FROM Device_Table AS t1
 INNER JOIN tbl1_device AS t2
    ON t1.rma = t2.rma
 INNER JOIN tbl_Full_List FL
    ON FL.Device = t2.Device
 WHERE t1.date_made = Current_Date()
 GROUP BY t1.place, t2.device, t2.description ORDER BY place ASC, device ASC )

Upvotes: 1

Related Questions