SQL problem... extracting specific data 1

[newphpbie]

Hi, I've been trying to make this work for most of the day now and I'm not getting very far....

I have 2 tables

User
intUserID
strFirstName
strLastName

Extension
intExtID
intExtNum
intUserID

I am trying to extract a list of users which don't have an Extension assigned to them. So....what I am trying to do is this...

Select All Users Which don't have an extry in the Extension Table.

I have been trying to do this using queries very similar to this.....

$userquery="SELECT strFirstName, strLastName FROM User WHERE intUserID NOT IN(SELECT * FROM Extension)";

but I'm getting no success....

Could someone advise me on this please or recommend somewhere I might find a tutorial on this....

Thanks.

 

[sleipnir214]

First of all, what database server are you running? What version of that server?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[newphpbie]

Sorry, new here...
I'm running LAMP

Red Hat 8
Apache 1.3v
MYSQL Ver. 4.0
PHP 4.2.2

 

[sleipnir214]

MySQL 4.0.x does not support subqueries (

http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html),

so your clause "WHERE NOT IN(SELECT...)" won't work.

Typically, when trying to find records that are in one table but not in another, I use a left join:

SELECT
u.intUserID, u.strFirstName, u.strLastName
FROM
User u
LEFT JOIN
Extension e
ON
u.intUserID = e.intUserID
WHERE
e.intUserID is NULL;

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[newphpbie]

Fantastic, it's working fine. Thanks for the help and the link, much appriciated.