View Full Version : Ignore empty links in table

02-19-2007, 08:55 PM
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.

02-19-2007, 09:27 PM
"images linked to gallery via gallerylink table"

so from here u just want to grab the image name? and you want it to be randomized?

02-19-2007, 09:41 PM
Thanks for the reply

Well I have a query that is not posted that handles getting the random image from the gallerylink table. That works if I give it a valid categoryid that has subcats, galleries and images assigned.

All I need is a random categoryID from my category table to put in that query. The query I posted is the one I am concerned about. As it grabs a random catID it is possible that it grabs one that does not have a subcategory assigned or does have a subcat but does not have a gallery etc.

What I need to know is how to select a category out of the category table but ONLY if there is a record in teh subcategory table with that categoryid.

I actually need more than this but if I can at least figure this out then I can use the solution to continue checking on down the line for gallery and images.

02-19-2007, 09:48 PM
Right, i understand what your asking for.
and I'm trying to answer your question, and at the same time show you how to do this all at once, while only grab stuff that exists. work with me here! ;-)

so for:
"images linked to gallery via gallerylink table"
whats the field name that your grabing?

02-19-2007, 10:38 PM
oh ok, I am getting the imageid. THe gallery table has a galleryid. The images table has a imageid and the gallerylink table holds both and links them together.

Here is the second query:

$query = "select images.ImageID from category, subcategory, gallery, images, gallerylink where category.CategoryID = ".$row["CategoryID"]." and subcategory.CategoryID = category.CategoryID and subcategory.SubCategoryID = gallery.SubCategoryID and gallery.GalleryID = gallerylink.GalleryID and category.UserID = ".$_Session["UserID"]." and category.UserID = images.UserID and images.ImageID = gallerylink.ImageID and images.Type = 1 and images.Active = 1 and images.Private = 2 and subcategory.SubCategoryID is not null and gallery.SubcategoryID is not null ORDER BY RAND() limit 1";


02-20-2007, 02:25 AM
what you should be doing is grab a random image id and work your way up the table to make sure it is in the relevant categories.

Have a look at this article (http://sqllessons.com/categories.html) if you are not overly familiar with traversing trees (categories/subcategories).

02-20-2007, 03:31 PM
I appreciate the response guelphdad.

From what I see in what you posted, I need to be using outer join to accomplish my task? This issue I am having is needing to be resolved in two different places on my page. Once at the gallery level and the other at the category level. Selecting a random imageid first then checking to see if it belongs to the root is what I am trying to do in one query. Is this not possible?

I do not fully understand what I was looking at...

Thanks again for you time. Any further direction with this is very welcome as well :)

02-20-2007, 04:15 PM
I am working with the query exapmle in the link you provided. I got it to work from cat->gallery. It shows me all of the names in each table. This is something I have never done or used (outer join).

I will continue chiseling away at this in the hopes that I will be able to link this new method all teh way to the image level inside of the gallerlink table. I am really ignorant to this method so I have a feeling there is going to be a lengthy learning curve here for me on this one :)

angst, any luck? ;)

02-20-2007, 04:31 PM
Ok, here is what I have to the gallery level and it works. It excludes results that have null in the subcat or gallery table.

The problem I am having now is how to incorporate my (images and gallerylink table)

gallerylink.ImageID, gallerylink.GalleryID

Here is what I have working in a test query right now...

select root.name as root_name
, down1.name as down1_name
, down2.name as down2_name

from category as root
left outer
join subcategory as down1
on down1.CategoryID = root.CategoryID
left outer
join gallery as down2
on down2.SubCategoryID = down1.SubCategoryID
where down1.CategoryID is not NULL and down2.SubCategoryID is not NULL
by root_name
, down1_name
, down2_name

02-20-2007, 06:39 PM
Thanks for the replies, I figured it out with the help from another programmer I know. Here is my query

Select images.ImageID from images, gallerylink, gallery, subcategory Where images.ImageID = gallerylink.ImageID And gallerylink.GalleryID = gallery.GalleryID And gallery.SubCategoryID = subcategory.SubCategoryID And images.Type = 1 and images.Active = 1 and images.Private = 2 And subcategory.CategoryID IN ( select CategoryID from category where UserID = 1 and CategoryID is not null) And gallerylink.ImageID is not null And gallery.GalleryID is not null And subcategory.SubCategoryID is not null And subcategory.CategoryID is not null ORDER BY RAND() limit 1