I was thinking of calling this thread "Arrrggghhh my head hurts" as it seemed quite apt as I have reached mental capacity and the information in the mysql manual is no longer hitting any part of my brain that can decipher it.
I have three tables (within a mysql database) that I need to perform a select query on :
user_categories
user_id(int) cat_id(int)
----------- ----------
98574 23
98574 24
98574 26
1234 22
1234 45
4321 15
4321 12
4321 23
portals
id(int) portalurl(text)
------- --------------
123 text1
124 text2
125 text3
126 text4
portal_categories
portal_id cat_id
--------- ------
123 23
124 23
125 24
126 25
'portal_categories' is the table that links the first two together as the '_id' table fields and the same as the '_id' fields in 'user_categories' and 'portals'
So the value I have a 'user_id' and from this I need to get 'portalurl' (this will also be ordered by 'RAND() LIMIT 1' as I only want one random 'portalurl' to be returned.
So in plain english what I want to achieve is this :
From the 'user_id' in 'user_categories' select all relevant 'cat_id', from that list of 'cat_id' select a list of relevant 'portal_id' from 'portal_categories', and finally from that list of 'portal_id' select one random 'portal_url' from 'portals'
I have acheived the results I need by using seperate queries, but its messy, if its possible to do this in one query (which I believe it is) I would prefer to do it that way.
Am I making sense here?
I have three tables (within a mysql database) that I need to perform a select query on :
user_categories
user_id(int) cat_id(int)
----------- ----------
98574 23
98574 24
98574 26
1234 22
1234 45
4321 15
4321 12
4321 23
portals
id(int) portalurl(text)
------- --------------
123 text1
124 text2
125 text3
126 text4
portal_categories
portal_id cat_id
--------- ------
123 23
124 23
125 24
126 25
'portal_categories' is the table that links the first two together as the '_id' table fields and the same as the '_id' fields in 'user_categories' and 'portals'
So the value I have a 'user_id' and from this I need to get 'portalurl' (this will also be ordered by 'RAND() LIMIT 1' as I only want one random 'portalurl' to be returned.
So in plain english what I want to achieve is this :
From the 'user_id' in 'user_categories' select all relevant 'cat_id', from that list of 'cat_id' select a list of relevant 'portal_id' from 'portal_categories', and finally from that list of 'portal_id' select one random 'portal_url' from 'portals'
I have acheived the results I need by using seperate queries, but its messy, if its possible to do this in one query (which I believe it is) I would prefer to do it that way.
Am I making sense here?