MySQL Query - Trouble with joins, filtering results

Question

I'm in way over my head with this query. Any help would be immensely appreciated. I'll try and break it down as simply as I can, but please let me know if I'm being too vague.

There are four sections: criminal, family, civil, and business. Each has a judgeID and a userID of the user who last updated the report, along with report information.

Then there is a users table, with userID and userType.

There is a judges table with a judgeID and judge name.

What I'm trying to do: Get all current reports from one of the four sections ($court) based on current month and year, find the name of each judge that corresponds with each judgeID on the reports found, and then (this is where I'm having trouble) filter the reports based on the userIDs that are of userType 'user' (rather than admin).

Here's what I have (a little bit of PHP in there):

$query = "SELECT Name FROM judges LEFT JOIN $court
       ON ($court.JudgeID = judges.JudgeID)
       where Month='$month' and Year='$year' order by Name asc;";

It's just the subsequent filtering of userIDs by userType that I'm having trouble with.

Dynamic tablenames are a major SQL-injection headache. Make sure you check `$court` against a whitelist of allowed tablenames before injecting it into the query, see: http://stackoverflow.com/questions/5811834/how-to-prevent-sql-injection-with-dynamic-tablenames — Johan, Sep 30 '11 at 15:14

score 2 · Accepted Answer · answered Sep 30 '11 at 14:46

Tim, I'm not sure I totally understand the requirements, but let's start with this query and see what happens.

$query = "SELECT Name FROM judges LEFT JOIN $court
       ON ($court.JudgeID = judges.JudgeID)
       LEFT JOIN users
       ON ($court.userid = users.userid) and user.userType='user'
       where Month='$month' and Year='$year' order by Name asc;";

score 1 · Answer 2 · answered Sep 30 '11 at 14:47

1

Try this:

SELECT Name 
FROM judges 
LEFT JOIN $court ON ($court.JudgeID = judges.JudgeID)
LEFT JOIN users ON ($court.userID = users.userID)
WHERE Month='$month' AND Year='$year' AND users.userType = 'user'
ORDER BY Name ASC;

answered Sep 30 '11 at 14:47

Aziz Shaikh

16,245
11
62
79

MySQL Query - Trouble with joins, filtering results

2 Answers2