|Struggling with 3 (maybe 4) level MySQL query|
Need a little help forming the most efficient cross-table query
| 7:34 am on Sep 12, 2011 (gmt 0)|
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.
| 7:44 am on Sep 12, 2011 (gmt 0)|
I am not sure, but I guess there is problem with your databases.
Example: Does your SITES table has SITEID that you use in your SITES_CATEGORIES table to set relation?
| 8:10 am on Sep 12, 2011 (gmt 0)|
yes, there are tables:
all with relevant data and unique primary key. Then there are several linking tables, each with 3 fields (first field also being primary key, + 2 linking fields):
MEDIA_AUTHORS - links USERS.ID to MEDIA.ID
MEDIA_CATEGORIES - links MEDIA.ID to CATEGORIES.ID
CATEGORIES_SITES - links CATEGORIES.ID to SITES.ID
So, the data + linking data is all there, I just need some help formulating what seems to me to be quite a deep query - at least beyond my scope right now.
| 10:26 am on Sep 12, 2011 (gmt 0)|
I am assuming you have something similar to the following tables :-
Now, you want to display a list of AUTHORS (users) that have MEDIA on SITE X.
You have URL (SITE X)
- So get SITEID from Table SITES using the URL.
- Then get all MEDIAID related to SITEID from Table MEDIA.
- Get all USERID related to MEDIAID from Table MEDIA_AUTHORS. Remove duplicate USERID
- Show the list of USERID and related data (USERNAME etc)
Instead of adding MEDIAID to Table MEDIA_AUTHORS, Add USERID to Table MEDIA. So you can get USERID using SITEID from Table MEDIA.