MySQL call for additional filtering for certain rows, only if another table is populated

Two clarifications:

• I'm going to actually be doing pagination with this, so ideally it can be one SQL s开发者_开发百科tatement...
• If this helps, what I'm trying to do is: Get all content for user, BUT if content item has criteria in content_criteria table, check that user also has criteria in users_criteria table, OTHERWISE, content is valid for user

So this might be hard to explain. Let's start with the main tables:

content:

| contentIdNum | contentStr | programIdNum |

A piece of content (unlike users) can only be associated with one program. A typical record would be like this. Both are associated with a program with id of 1.

| 1 | Zelda | 1 |
| 2 | Mario | 1 | 

content_criteria:

| contentIdNum | criteriaIdNum |

A typical record would be like this: (this basically a table that associates content with criteria. Not all content is associated with criteria.)

| 1 | 5 |

** user_criteria: **

| userIdNum | criteriaIdNum |

A typical record would be like this: (this basically a table that associates users with criteria. Not all content is associated with criteria.) Note that userIdNum 1 has the same criteria as contentIdNum 1

| 1 | 5 |

** program_users: **

| userIdNum | criteriaIdNum |

A typical record would be like this (both user 1 and 2 are associated with program 1)

| 1 | 1 |
| 2 | 1 |

So here's what I need to do:

• Create a MySQL call that returns all pieces of content a user has access to
• By default, a user has access to all content associated with the programs he is assigned to
• If a piece of content has criteria associated with it in the content_criteria table, ONLY users with that corresponding criteria in the user_criteria table have access to that content
• For example, if I pass in userIdNum 1, I should get both pieces of content
• If I pass in userIdNum of 2, I should only get one piece of content (the one with contentIdNum of 2)

Obviously selecting content that matches a user's programs is easy (below is what I have). I'm trying to figure out an easy way to filter the content - can I do it in one go, or do I have to do a secondary pass and loop through the content via PHP?

SELECT DISTINCT (c.contentIdNum), c.*
FROM content c , program_users pu 
WHERE 1=1 
AND pu.userIdNum = 2 AND pu.programIdNum = c.programIdNum

SELECT  c.*
FROM    content           c , 
        program_users     pu , 
        content_criteria  cc, 
        user_criteria     uc
WHERE   pu.userIdNum      = 2 
AND     pu.programIdNum   = c.programIdNum
and     cc.contentIdNum   = c.contentIdNum
and     uc.criteriaIdNum  = cc.criteriaIdNum
and     uc.userIdNum      = pu.userIdNum

should do the trick.

"distinct" is a really good way of hiding bugs - especially while you're still trying to work out what your query should look like.

Fix it thanks to UNIONs. The first part of the UNION gets all content that does NOT have a criteria to filter by. The second part of the UNION gets all content with a criteria to filter by, if the user has that criteria.

SELECT  c.*
FROM    content           c , 
        program_users     pu 
WHERE   pu.userIdNum      = 1
AND     pu.programIdNum   = c.programIdNum
AND NOT EXISTS (
    SELECT *
    FROM content_criteria cc
    WHERE cc.contentIdNum = c.contentIdNum
)
UNION 
SELECT  c.*
FROM content_criteria cc, users_criteria uc, content c
WHERE cc.contentIdNum = c.contentIdNum
AND cc.criteriaIdNum = uc.criteriaIdNum
AND uc.userIdNum = 1