while I am here, I have another question that hopefully someone can help me out with. Apologies, I am fairly new with php / mysql etc.
I have a table called SITES, containing website records (site1, site2 etc).
Another table called CATEGORIES containing categories (cooking, sport etc).
There is another table SITES_CATEGORIES containing fields SITEID and CATEGORYID, thus assigning specific categories to specific sites.
There is a MEDIA table, listing details on stored pdf documents.
There is a MEDIA_CATEGORIES table (fields CATEGORYID and MEDIAID, assigning specific media to specific categories.
There is a USERS table, containing users info.
Finally there is a MEDIA_AUTHORS table (fields USERID, MEDIAID) linking authors(users) to their media. There may be one or more authors to a given media, e.g. if a document was co-authored.
I would like to display a list of AUTHORS (users) that have MEDIA on SITE X.
However, this query goes a bit beyond my capabilities. In short:
WHERE USER(S) LINKED TO MEDIA
WHERE MEDIA LINKED TO CATEGORY
WHERE CATEGORY IS ON SITE X
I don't know whether I should be trying to create a complex join, or subquery, or what. Could anyone enlighten me? Assistance is much appreciated.