help in formatting a query
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