Page 1 of 1

SELECT JOIN problem

Posted: Tue Apr 15, 2008 12:25 am
by mosfet83
Hye everybody,

I'm new to SQL and this is my first problem that I encounter.

I have two tables, stf_info that stores staff details and stf_login that stores staff who has admin privilege information. How do i select staff from table stf_info who doesn't has admin privilege? I try code below but the result came out multiple times.

Code: Select all

SELECT stf_info.stf_name FROM stf_login INNER JOIN stf_info ON stf_login.stf_id<>stf_info.stf_id
Result :
MICHAEL
MICHAEL
MICHAEL
JENNY
JENNY
JENNY
DAVID
DAVID
DAVID

Thank you for your help.

Re: SELECT JOIN problem

Posted: Tue Apr 15, 2008 2:48 am
by deejay
have you tried using LEFT JOIN that should give you one set of results.

Re: SELECT JOIN problem

Posted: Tue Apr 15, 2008 9:37 pm
by mosfet83
deejay wrote:have you tried using LEFT JOIN that should give you one set of results.
yes, but same result. this is what i try :

Code: Select all

SELECT * FROM stf_info LEFT JOIN stf_login ON stf_info.stf_id<>stf_login.stf_id
there are three rows of data in stf_login, so when above query is executed, each fields returned three times. Thats what i assumed so far.

Re: SELECT JOIN problem

Posted: Wed Apr 16, 2008 3:07 am
by EverLearning

Code: Select all

SELECT DISTINCT * ....

Re: SELECT JOIN problem

Posted: Wed Apr 16, 2008 4:40 am
by aceconcepts
You should be able to sort this out using a JOIN - maybe revise the way you have structured your query.

Also, you could use NOT IN:

Code: Select all

 
$query=("SELECT * FROM table1 WHERE table1.id NOT IN (SELECT id FROM table2)");
 
Hope this helps.

Re: SELECT JOIN problem

Posted: Wed Apr 16, 2008 8:01 pm
by mosfet83
EverLearning wrote:

Code: Select all

SELECT DISTINCT * ....
tried and didnt work :(

aceconcepts wrote:You should be able to sort this out using a JOIN - maybe revise the way you have structured your query.

Also, you could use NOT IN:

Code: Select all

 
$query=("SELECT * FROM table1 WHERE table1.id NOT IN (SELECT id FROM table2)");
 
Hope this helps.
that works :)
thank you very much 8)