Hello. I have a set of tables like so
category -> subcategory - gallery -> gallerylink-> images
each linked by a parentid
category - holds a categoryID
subcategory table is linked to category via categoryid
gallery linked to subcat via subcatid
gallerylink holds galleryid and imageids
images linked to gallery via gallerylink table
Ok so I am trying to get a random imageid(to display a thumbnail) out of my images table for a specific user. At the point I run this query, all I have is the userid. Here is my query:
select CategoryID from category where UserID = 1 ORDER BY RAND() limit 1
I then take the categoryid I get here and my userid and run a more specifc query to get the imageid I need using all the linked tables.
The problem is when I run this categoryid query, it is possible that it grabs a categoryid that has no subcategory assigned yet, or gallery or images. So I only want to look at categoryids to which have a record in the subcat table and the subcat table has a record in the gallery table and it has a record in the gallerylink table.
So the categoryid that is randomly selected has to be linked all the way to an existing image.
How do I accomplish this without running 4 separate queries?
THank you for any help with this.