Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
mosfet83
Forum Newbie
Posts: 4 Joined: Tue Apr 15, 2008 12:04 am
Post
by mosfet83 » Tue Apr 15, 2008 12:25 am
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.
deejay
Forum Contributor
Posts: 201 Joined: Wed Jan 22, 2003 3:33 am
Location: Cornwall
Post
by deejay » Tue Apr 15, 2008 2:48 am
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
Post
by mosfet83 » Tue Apr 15, 2008 9:37 pm
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.
aceconcepts
DevNet Resident
Posts: 1424 Joined: Mon Feb 06, 2006 11:26 am
Location: London
Post
by aceconcepts » Wed Apr 16, 2008 4:40 am
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
Post
by mosfet83 » Wed Apr 16, 2008 8:01 pm
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