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:

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.

Kermit · Accepted Answer · 2012-12-03 18:34:48Z

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.

edited Dec 3, 2012 at 18:34

answered Dec 3, 2012 at 18:29

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

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

Comments

diggersworld · Accepted Answer · 2012-12-03 18:28:53Z

0

To select it you can do:

SELECT * 
FROM TB1 
WHERE id NOT IN (
    SELECT compid 
    FROM TB2
);

answered Dec 3, 2012 at 18:28

diggersworld

13.1k25 gold badges87 silver badges120 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

See it in action

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related