mysql command to get data of 2 tables-CodePudding

I would like to get data from 2 mysql tables.

Table tbl_1:

Table tbl_2:

I tried this:

SELECT `tbl_2`.* 
FROM `tbl_1` 
    LEFT JOIN `tbl_2` ON `tbl_1`.invoiceID = `tbl_2`.invoiceID 
WHERE `tbl_1`.customerID = "463";

My result:

The results looks good but not perfect. I get many null rows which are not be in my result. only the first both rows should be in the result.

Where is my fault?

CodePudding user response：

The LEFT JOIN keyword returns all records from the left table (tbl_1), and the matching records from the right table (tbl_2).

Maybe you should try 'INNER JOIN' instead of 'LEFT JOIN'?

CodePudding user response：

The LEFT JOIN keyword returns all records from the left table tbl_1, and the matching records from the right table tbl_2. So change the order of the tables

SELECT `tbl_2`.* 
FROM `tbl_2` 
    LEFT JOIN `tbl_1` ON `tbl_1`.invoiceID = `tbl_2`.invoiceID 
WHERE `tbl_1`.customerID = '463';

CodePudding user response：

Sir,

You have two tables (Table1 and Table2) and there is a COMMON column called invoiceID in both your tables. So if you want to select some columns from these two tables for a given CUSTOMERID:

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2
ON Table1.invoiceID = Table2.invoiceID
WHERE Table1.CUSTOMERID = "463"