Need help with dynamic query

[LLLLL]

i have 2 tables, one with employees and the other with 10 locations (branch
1, branch 2, branch3 etc.). Each employee must select a branch, but cant use
the same branch twice, so if user1 used branch 5, I want the query to ONLY
show 1,2,3,4,6,7,8,9,10 for user 1. (The ones he hasnt used yet!)
Sorry if it sounds confusing.
Thanks

 

[Duane Hookom]

Normalize, normalize, normalize.
http://www.datamodel.org/NormalizationRules.html

 

[LLLLL]

I understand your point, but I cant redesign teh database at this point.
Does anyone have another suggestion?

 

[John Spencer]

Divide and conquer with three queries

QueryA:
SELECT UserID, BranchId
FROM Employees, Branches

QueryB:
SELECT UserID, BranchID
FROM Employees

QueryC:
SELECT QueryA.UserID, QueryA.BranchID
FROM QueryA LEFT JOIN QueryB
ON QueryA.UserID = QueryB.UserID AND
QueryA.BranchID = QueryB.BranchID
WHERE QueryB.UserID is Null and QueryB.BranchID is Null

You can try to do this all in one query as follows
SELECT QueryA.UserID, QueryA.BranchID
FROM (
SELECT UserID, BranchId
FROM Employees, Branches
) as QueryA
LEFT JOIN (
SELECT UserID, BranchID
FROM Employees
) as QueryB
ON QueryA.UserID = QueryB.UserID AND
QueryA.BranchID = QueryB.BranchID
WHERE QueryB.UserID is Null and QueryB.BranchID is Null