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.
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
It's best practice to always alias the columns you select to prevent ambiguity.
To select it you can do:
SELECT *
FROM TB1
WHERE id NOT IN (
SELECT compid
FROM TB2
);