Query Help

[DANZIG]

Hellos,

I'm trying to write a query to get results from 2 tables.

Sites
Idx SiteCode SiteLoc
1 SiteA Town1
2 SiteB Town2
3 SiteC Town3
4 SiteD Town4

and

Rights
Idx SiteCode UserID
1 SiteC User4
2 SiteA User2
3 SiteD User2

The return I need has 2 parts.
1. The list contains all sites in Asc order
2. In the Asc order sites where the user has rights are notated.

IE.

SiteA Town1 User2
SiteB Town2
SiteC Town3
SiteD Town4 User2


I've tried a few different join types to get the results. The best I've been able to get is a sorted listing of the sites the user has rights in.

IE.

SiteA Town1 User2
SiteD Town4 User2


Any help would be appreciated


Thanks, Danzig

 

[SQLSister]

What you need is a left join. Then create an additonal field inthe selct called USERIDSORT which is conditionally filled with 1 if Rights.UserID is not null and 2 if userrights.USerID is null

Then the sort will be on Sites.Sitecode asc, USERISSORT asc and UserID asc

Here's an example using my tables:

select p.proposalnumber,  scopeID, case when Scopeid is null then 2 else 1 end as SortORder
from proposals p left join Proposalsubcontractors s 
on p.proposalnumber = s.Proposalnumber order by  p.ProposalNumber asc, SortORder asc, scopeid asc

Questions about posting. See faq183-874