MySQL Forums
Forum List  »  InnoDB

help in formatting a query
Posted by: Jason Green
Date: May 30, 2005 06:59PM

Hello

I need some help in formatting a query using 'case when' conditionals

I have 2 tables:
* Table article stores information on an article such as: ID, subject, submittter, submission date and audience scope.
* Table audience_members stores information about specific members who are entitled to access articles

Here are the two table definitions:

CREATE TABLE `article` (
`article_ID` int(10) unsigned not null auto_increment,
`subject` varchar(40) not null default '',
`submitter_id` int(10) unsigned not null,
`submissionDate` datetime,
`AudienceScope` tinyint(1) unsigned not null,
PRIMARY KEY (`article_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `audience_members ` (
`article_ID` int(10) unsigned not null,
`Member_ID` int(10) unsigned not null,
PRIMARY KEY (`article_ID`, `Member_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


Field `AudienceScope` in table article defines what is the audience scope level of the article. It has 2 possible values:
1 - Only the current article submitter is allowed to see it
2 - Only set of members with IDs 2, 4, or 6 allow to see it

When forming the query, I have access to the ID of the current user.
I use PHP, so for those who know PHP, you can assume that the current user ID is stored in
variable $currentUserID. That parameter needs to be used in the query
For those you don't know PHP, simply assume that the current user ID is the constant 4

I need to form a query that will return all the fields of the article (article.*) that adhere to the `AudienceScope` value.
That means that I need to iterate over all the articles and for each one to test the following: if the `AudienceScope` is 1, then include it in the result set, only if the submitter_id if 10, else if `AudienceScope` is 2, then include it if members 2, 4 or 6 are found in table `audience_members` for that article note that this table is ONLY used if `AudienceScope` is 2).

I hope that I made this clear

any help would be appreciated

Options: ReplyQuote


Subject
Views
Written By
Posted
help in formatting a query
2651
May 30, 2005 06:59PM
2034
June 08, 2005 12:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.