Bit of SQL help

cotton31

cotton31

cotton31

Associate
Joined
25 Feb 2007
Posts
905
Location
Midlands
Hi all,

How would I go about finding all the entries in my table that have the same value in a field as another entry?

To be more clear, I need to find all staff who have the same home branch as a specified staff member...

Let me know if it doesn't make sense :D

Cheers,
 
select
STAFF_NAME
from
STAFF
where 1=1
and HOME_BRANCH = (select HOME_BRANCH from STAFF where STAFF_NAME = 'Norman.Bates')

This will also work... (with a bit more info)

select
s2.STAFF_NAME,
s2.HOME_BRANCH,
s1.HOME_BRANCH "NORMANS_BRANCH"
from
STAFF s1,
STAFF s2
where 1=1
and s1.STAFF_NAME = 'Norman.Bates'
and s1.HOME_BRANCH = s2.HOME_BRANCH

(Assuming STAFF_NAME is a unique PK)
 
Last edited:
Cheers mate, that worked a treat (first one)!

Now how do I show only those entries where a value appears more than once in that table - I need to show only those members who have a membership at more than one branch - so I thought I could do it by only displaying the entries where the branchid appears more than once?

Sorry for the questions!
 
You must log in or register to reply here.
Back
Top Bottom