trying to output the correct value from SQL query from comparing a different table

Question

I'm very new with SQL and need assistance on how I can accomplish this task using the correct query.

I have 2 tables that I need to use. Table "TB1" has:

id Name
1  bob
2  blow
3  joe

table "TB2" has:

compid property
1      bob
2      blow

I am trying to get which compid is missing in "TB2" and insert it from "TB1"

the query I am doing is:

SELECT id, name from TB1, TB2 where id <> compid

what I get is 2 ouputs of Id 1, and 2, and 3 outputs from id 3. by using php:

for($i=0;$i <= mysql_num_rows($comp)-1; $i++)
{
 echo mysql_result($comp, $i, 0)."<br>";


}

and I expected the ouput 3 but instead got this:

1
1
2
2
3
3
3

I understand its comparing all the rows within the table but is there a way to achieve what I am looking for?

Thanks for your time.

Answer 1

You are performing an implicit Cartesian JOIN which results in every row against every other row. You need to specify what attribute JOINs the two tables.

Using implicit syntax (not recommended):

SELECT id, name 
FROM TB1, TB2 
WHERE id <> compid
  AND TB1.Name = TB2.property <-- Column join

Using explicit syntax:

SELECT id, name
FROM TB1
JOIN TB2
  ON TB2.property = TB1.Name <-- Column join
WHERE id <> compid

To accomplish your goal you would need something along the lines of:

SELECT TB1.id, TB1.name
FROM TB1
LEFT JOIN TB2
  ON TB2.property = TB1.Name
WHERE TB2.compid IS NULL

See it in action

It's best practice to always alias the columns you select to prevent ambiguity.