Group by/distinct query question

[LMid]

Hi, I'm new to this board so I hope I'm posting this question in th
right place

I have a table with 3 fields owner, project, user_id. Every user_i
could be in
1. with the project P and the project L OR 2. just with th
project L

I would like to make a query that returns only one record per user i
BUT if the user has a record with the project L, that should be th
record returned. I know this is possible with multiple queries, bu
is there a way to do it in one query

Thanks in advance for your help

 

[Ken Snell [MVP]]

SELECT * FROM TableName
WHERE [project] = "L";

 

[Pieter Wijnen]

Something like:
SELECT * FROM PROJ
WHERE PROJECT = "L" AND USERID = CurrentUser()
UNION ALL
SELECT * FROM PROJ P1
WHERE PROJECT = "P" AND USERID = CurrentUser()
AND NOT EXISTS (SELECT 'x' FROM PROJECT P2
WHERE PROJECT = "L" AND USERID=CurrentUser())

should do it

HTH

Pieter

 

[LMid]

Thank you everyone for your help. Ken, I couldn't use your ide
because not everyone has an L project. Pieter's idea would do th
trick though. I found a solution on tek-tips.com which seems to d
it faster.
http://www.tek-tips.com/viewthread.cfm?qid=101845

SELECT * FROM proj
(SELECT user_id, min(project) as mPro
FROM pro
GROUP BY user_id) tm
WHERE proj.user_id = tmp.user_i
and proj.project = tmp.mpro

This returns one record per user_id, but the record that is returne
contains the minimum project (in this case L). It is also very fas
because it does not contains INs or NOTs

Thank you all again

 

[Ken Snell [MVP]]

Thank you everyone for your help. Ken, I couldn't use your idea
because not everyone has an L project.

My apology.. I apparently misread your post's information.

 

[Pieter Wijnen]

Yupp, but mine is more universally usable
And most RDBMS'es are tuned towards NOT EXISTS

;-) Pieter