[Resolved] I need help with this query...

bad76

I've three tables:
1) project: ID,Name,Desc
2) user: ID,Name,user,pwd...
3) user2proj:ID,PrjID,UsrID,Allowed

I want join 1 with 3 where UsrID is the logged user.

SELECT p.ID,p.Name,p.Desc,u.Allowed
FROM project p, user2proj u
WHERE u.UsrID=$logusr.

Now the problem: the table 3 maybe empty for a user!

So I've tried with:
SELECT p.*, u.Allowed
FROM project p LEFT JOIN user2proj u ON p.ID=u.PrjID
WHERE (u.UsrID=$logusr OR u.ID IS NULL)

But this don't give me all projects where exist u.ID but for another user!

What can I do? Have I to use two distinct query?

Please give me any hint...

bubblenut

What exactly do you want to return, I'm a little confused. If the user has projects then return those projects but if the user has no projects return???

bad76

It's an admin view.
The admin can look at complete list of projects, not?.
I want to know if a user is allowed to any project or not.

Now:
"u.Allowed"=1 => is allowed.
"u.Allowed"=0 => is not allowed.
"u.Allowed"=NULL => is not allowed.

But, actually, I can't get the third condition...

I hope to be more clear now...

bubblenut

Try this;

SELECT p.*, u.Allowed
FROM project p 
LEFT JOIN user2proj u ON (p.ID=u.PrjID || u.ID IS NUL)
WHERE u.UsrID=$logusr;

Hot it works, if not post back.

bad76

This give me only existing user2proj...

I think this may be a rigth point of view:

SELECT p.*, u.Allowed
FROM user n LEFT JOIN user2proj u ON u.UsrID=n.ID,
project p

WHERE u.ID=$logusr AND (u.PrjID=p.ID or (<p.ID is not in (user n LEFT JOIN user2proj u ON u.UsrID=n.ID>)))

But how can I to do <p.ID is not in (user n LEFT JOIN user2proj u ON u.UsrID=n.ID> now?

TheIceman5

whenever i get stuck on a query i use microsoft access, works every time.

bad76

Originally posted by TheIceman5
whenever i get stuck on a query i use microsoft access, works every time.

I think "microsoft" and "works every time" words must not be in the same phrase :p
Anywhere thanks, i'll try next time.

I'm joking because after all ... 4 heures! ... i get the solution.
after the hint of bubblenut I've thinked this:

SELECT p.*, u.Allowed
FROM project p LEFT JOIN user2proj u
ON (p.ID=u.PrjID and u.UsrID=$logusr)
WHERE (u.UsrID=$logusr OR u.ID IS NULL)

And... All work fine!!!

Another thanks to all... see you