MYSQL LEFT JOIN Return value from empty table

Question

I'm querying 3 tables with 1 query with MySQL. I'm using the JOIN method as follows:

table have column "test_column" value="test_value"
table have column "test_column" value="test_value"
table have column "test_column" value=NULL

I'getting always test_column value is NULL, but I would like to have the value "test_value"

    $sql  = "SELECT _t.*, _t1.*, _t2.*";
    $sql .= "FROM _test as _t ";
    $sql .= "LEFT JOIN _test1 as _t1 ";
    $sql .= "ON _t.test_column=_t1.test_column ";
    $sql .= "LEFT JOIN _test2 as _t2 ";
    $sql .= "ON _t.test_column = _t2.test_column ";
    $sql .= "WHERE _t.test_column ='test_value'";

Barmar · Accepted Answer · 2015-10-08 09:54:32Z

1

Your query is selecting multiple columns named test_column. When you get the results in PHP, $_row['test_column'] will contain the value from the last one, which is _test2.test_column, which is NULL.

You should use a column alias so you can get the column from a specific table.

$sql = "SELECT _t.test_column AS t_test_column, _t.*, _t1.*, _t2.*";

Then use $row['t_test_column'] to get the value.

answered Oct 8, 2015 at 9:54

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sueleyman topaloglu Over a year ago

I'm sorry 3. table is empty not any record!

Barmar Over a year ago

When you do a LEFT JOIN, you get null values for all the columns from the table if there's no match.

Collectives™ on Stack Overflow

MYSQL LEFT JOIN Return value from empty table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related