Simple Case Statement Help

[Trent]

Hello.

I am trying to execute a statement that works fine in ms sql but will
not work in mysql. There are three tables. user (user_sid,
user_name, user_email), file (file_sid, file_name, file_desc),
file_access (file_sid, user_sid).

Here is what I want:
Given a particular file_sid return all users with an extra column
(1,0) inticated whether or not there was a match in the file_access
table for given file_sid. here is what I attempted that DOES work in
mssql.

select case when not (select file_access.user_sid from file_access
where file_access.file_sid=1 and file_access.user_sid=user.user_sid)
is null then 1 else 0 end as has_access, user.* from user
PLEASE HELP...

[Siemel Naran]

"Trent" <tr*********@earthlink.net> wrote in message

Given a particular file_sid return all users with an extra column
(1,0) inticated whether or not there was a match in the file_access
table for given file_sid. here is what I attempted that DOES work in
mssql.

Would group by help? It's not exactly the same thing you're looking for,
but maybe can be tweaked.

select file.name, count(*)
from file
left outer join fileuser on file.id = fileuser.file_id
left outer join user on fileuser.user_id = user.id
group by file.name

If you really need 0, 1 then maybe there's another function you can use in
place of count(*). A simple if(...) or case comes to mind, but it has the
disadvantage of counting all the rows whereas you need to see if there is at
least one row (ie. stop counting at 1 row). So somewhere, somehow, a limit
1 might be helpful.

[Trent]

While that statement did prove to give back a count of users with
access to each file, I am wanting something different. With one query
I want ALL users displayed in a list on a webpage. The list will also
contain a checkbox. the checkbox will look for the extra column (1 or
0) and if one will place SELECTED in the checkbox when writing to the
screen.

FOR EXAMPLE:

File Access for test_doc.PDF

chk user fullname
X Trent Trent J.
Larry Larry Smith
X Bob Bob Booth
X John John Smith
Where X is 1 from result set otherwise 0 is blank.

"Siemel Naran" <Si*********@REMOVE.att.net> wrote in message news:<aa*****************@bgtnsc04-news.ops.worldnet.att.net>...

"Trent" <tr*********@earthlink.net> wrote in message

Given a particular file_sid return all users with an extra column
(1,0) inticated whether or not there was a match in the file_access
table for given file_sid. here is what I attempted that DOES work in
mssql.

Would group by help? It's not exactly the same thing you're looking for,
but maybe can be tweaked.

select file.name, count(*)
from file
left outer join fileuser on file.id = fileuser.file_id
left outer join user on fileuser.user_id = user.id
group by file.name

If you really need 0, 1 then maybe there's another function you can use in
place of count(*). A simple if(...) or case comes to mind, but it has the
disadvantage of counting all the rows whereas you need to see if there is at
least one row (ie. stop counting at 1 row). So somewhere, somehow, a limit
1 might be helpful.