MasterGberry MasterGberry - 7 months ago 11
SQL Question

Join returning null columns?

I have a MYSQL query selecting from two tables. The second table may not necessarily have a matching join id to the first table. Let's use these two as examples:

++++++++++++++++++
table: t1
column: id
column: test_id
column: info

table t2
column: t2id
column: test_id
column: extra_info
++++++++++++++++++


I inserted these following rows into the table (this is just all pseudo at the moment):

insert into t1 values (1, 4, "asd")
insert into t1 values (2, 25, "dfg")
insert into t2 values (1, 25, "123")


Now my goal is to join the two tables together, but I am having issues with the join. if the second table (t2) doesn't seem to have a matching row it makes the join column in the original table NULL? Why is it doing this? Here is an example query:

SELECT * FROM `t1` LEFT JOIN `t2` ON (`t1.test_id` = `t2.test_id`) WHERE `t1.id` = 1;


Now I have absolutely no issues if they match up, but for some reason if there is no row that exists, then test_id from t1 is being set as NULL...My goal is to join these two tables if there is something to join. EDIT: If there is nothing to join then i want to just return the data from t1 by either having all the t2 columns set to NULL or just returning the t1 data. Do I need to do a subquery? and if so what would it be? Thanks :)

hkf hkf
Answer

Use an INNER JOIN.

SELECT * FROM `t1` 
INNER JOIN `t2` ON (`t1.test_id` = `t2.test_id`) 
WHERE `t1.id` = 1; 

Documentation on JOIN types: http://dev.mysql.com/doc/refman/5.7/en/join.html