If those are the sole requirements having can be used to filter out all revisions without the 3 or however many profiles. You would just adjust the COUNT() = ? based on number of revisions that should be matched. Of course this assumes that the revisionProfiles table (RevisionID,ProfileID) make up a unique or primary key.
SELECT
r.RevisionID
FROM
revision r
INNER
JOIN
revisionProfiles rp
ON
r.RevisionID = rp.RevisionID
INNER
JOIN
profiles p
ON
rp.ProfileID = p.ProfileID
WHERE
p.ProfileID IN (1,2,3)
GROUP
BY
r.RevisionID
HAVING
COUNT(*) = 3
You can probably get a better understanding of how this works if you remove the groupby and having clause. If you were to do that you would notice that revisions are repeated based on the number of profiles they are associated with. So you can than use to group by to aggregate a count than filter using having by counting the number of duplicates.
I should also point out that the last join could be eliminated since the second table exposes that data. Though if you needed to filter by data other than the primary key than the third table (profiles) would be necessary.
SELECT
r.RevisionID
FROM
revision r
INNER
JOIN
revisionProfiles rp
ON
r.RevisionID = rp.RevisionID
WHERE
rp.ProfileID IN (1,2,3)
GROUP
BY
r.RevisionID
HAVING
COUNT(*) = 3
On the page itself, its using a search extension by Web Assist. (Sorry, I know that’s probably bad form with serious coders, but my background is mostly design.)
The search code generates a WHERE clause, and after playing around with it, its not going to fly using HAVING.
The problem, as far as I can tell, is that there are multiple records in the revisionProfiles table, which somehow need to be grouped together.
Another thing that might be an option is creating a temp table.
I got as far as creating a table, and inserting the string of Profile IDs using:
Are you aware that the mysql_* extension has been deprecated as of version 5.5.x of PHP? You should be migrating over to either the mysqli_* extension or PDO. You should use prepared statements to prevent attack via SQL Injection.
If you’re not already doing so, any user submitted data submitted in any way should be considered dangerous until it has been sanitized.
Yeah - its been in the back of my mind for a while. Unfortunately my background is design, so for my sins use Dreamweaver, and Adobe haven’t updated their database server behaviour stuff to either. How much is involved in migrating this sort of stuff? At the very least I assume the connection file needs to be rewritten.