SELECT JOIN problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mosfet83
Forum Newbie
Posts: 4
Joined: Tue Apr 15, 2008 12:04 am

SELECT JOIN problem

Post 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.
User avatar
deejay
Forum Contributor
Posts: 201
Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall

Re: SELECT JOIN problem

Post by deejay »

have you tried using LEFT JOIN that should give you one set of results.
mosfet83
Forum Newbie
Posts: 4
Joined: Tue Apr 15, 2008 12:04 am

Re: SELECT JOIN problem

Post 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.
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: SELECT JOIN problem

Post by EverLearning »

Code: Select all

SELECT DISTINCT * ....
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: SELECT JOIN problem

Post 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.
mosfet83
Forum Newbie
Posts: 4
Joined: Tue Apr 15, 2008 12:04 am

Re: SELECT JOIN problem

Post 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)
Post Reply