469,112 Members | 2,030 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,112 developers. It's quick & easy.

Simple Case Statement Help

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...
Jul 20 '05 #1
2 1525
"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.
Jul 20 '05 #2
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.

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

27 posts views Thread by Brian Sabbey | last post: by
31 posts views Thread by da Vinci | last post: by
6 posts views Thread by deanfamily11 | last post: by
5 posts views Thread by Rob Somers | last post: by
5 posts views Thread by Tim::.. | last post: by
12 posts views Thread by rAinDeEr | last post: by
26 posts views Thread by jacob navia | last post: by
4 posts views Thread by amit.uttam | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.