dB design question

[GUJUm0deL]

I have the following table which holds the info for images the user uploads.

PHOTOID (PK)
CATID (FK) -- PK in categories table
SUBCATID (FK) - PK in subcategories table

I can get the insert into and update to work fine, but how can I get the query to work so that I can only get results where all images are in one particular group set?

To see this in action:

http://jayweinstein.com/content.cfm?C=3&SC=7

PHOTOID  CATID  SUBCATID  GROUPING  PICNAME  ISACTIVE	
1  	   3	  7  	  TGROUP1   1710.jpg    True
2  	   3	  7  	  TGROUP1   1717.jpg    True
6  	   3	  7  	  TGROUP1   1721.jpg    True
7  	   3	  7  	  TGROUP1   1730.jpg    True
5  	   3	  7  	  TGROUP1   1711.jpg    True
8  	   3	  7  	  ZGROUP2   5056.jpg    True
9  	   3	  7  	  ZGROUP2   5062.jpg    True
10 	   3	  7  	  ZGROUP2   5076.jpg    True

Thanks.

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[r937]

how do you know which grouping you want? and in which table is the GROUPING column?

r937.com | rudy.ca

 

[ArunPrithviraj]

Hi,

Can you be more specific like Is it when u r trying to select a set of photos etc? U will be able to give a where condition to get a particular set.

Thanks,
AP

 

[GUJUm0deL]

Hi, the link in my original post, the user clicks 'Photography' link at the top, which returns a sub-categories (shown in the left side). The user then chooses which sub-category they want to see the pics in. When they choose a sub-category they want, the main section loads all groupings of the pics available (the link above returns 'corn' and 'Spaghetti & Clams'. Now, if they choose 'corn' then all images have 'corn' should be displayed, and likwise if they choose 'Spaghetti & Clams' then its set of pics should be displayed)

What happens now, is I get all pics returned. Below is the query:

SELECT	P.PHOTOID  P.CATID  P.SUBCATID  P.GROUPING  P.PICNAME  P.ISACTIVE, C.CatName, SC.SubCatName
FROM	Photos AS P INNER JOIN Categories AS C ON P.Cat_ID = C.Cat_ID INNER JOIN SubCategories AS SC ON P.SubCat_ID = SC.SubCat_ID
WHERE 	P.isLive = 1 AND C.Cat_ID = #URL.C# AND SC.SubCat_ID = #URL.SC#

The only was I can probably do this if the url looked like:

[URL unfurl="true"]http://jayweinstein.com/content.cfm?C=3&SC=7&G=Corn[/URL]

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[GUJUm0deL]

Underscore was snuck in...

SELECT  P.PHOTOID,  P.CATID,  P.SUBCATID,  P.GROUPING,  P.PICNAME,  P.ISACTIVE, C.CatName, SC.SubCatName
FROM    Photos AS P INNER JOIN Categories AS C ON P.CATID = C.CATID INNER JOIN SubCategories AS SC ON P.SUBCATID = SC.SUBCATID
WHERE   P.isLive = 1 AND C.CatID = #URL.C# AND SC.SubCatID = #URL.SC#

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[r937]

if a subcategory can belong to only one category (and it had better!!) then you don't need to pass in the category because the subquery has a link to it in the table

you wouldn't need to link each photo to its category either, just link it to its subcategory

this saves joining the photo to the category

this approach also avoid situations where the photo is not properly joined to the correct categoty and subcategory (errors on data entry, or errors introduced when re-assigning a subcategory to a different category)

in fact you don't need a separate subcategory table either

see

http://sqllessons.com/categories.html

r937.com | rudy.ca

 

[GUJUm0deL]

So, my link should look like:

[URL unfurl="true"]http://jayweinstein.com/content.cfm?SC=7[/URL]

And, my query should be:

SELECT  P.PHOTOID,  P.CATID,  P.SUBCATID,  P.GROUPING,  P.PICNAME,  P.ISACTIVE, C.CatName, SC.SubCatName
FROM    Photos AS P INNER JOIN Categories AS C ON P.CATID = C.CATID INNER JOIN SubCategories AS SC ON P.SUBCATID = SC.SUBCATID
WHERE   P.isLive = 1 AND AND SC.SubCatID = #URL.SC#

But how would it still know to find only 'corn' pics and not anything else? The original post had what the table looked like. All pics so far belong C=3 and SC=7.



_____________________________
Just Imagine.

http://www.imajinarts.com

 

[r937]

let's examine your join

first, you want to start with the subcats table, because that's where the restriction is -- WHERE SC.SubCatID = #URL.SC#

then, if you need anything from the categories table, you would join to it next, getting the single (many-to-one) parent cat row for this subcat (remember, you're getting only one subcat)

finally, you would join the subcat to the pictures in that subcat, and here you go from one to many

SELECT ...
  FROM SubCategories AS SC 
INNER
  JOIN Categories AS C 
    ON P.CATID = SC.CATID 
INNER 
  JOIN Photos AS P 
    ON P.SUBCATID = SC.SUBCATID
   AND P.isLive = 1 
 WHERE SC.SubCatID = #URL.SC#

does that make sense?

it's a hierarchy, categories have subcategories which have photos, and you're juimping into the tree in the middle, picking out a single subcat



r937.com | rudy.ca

 

[GUJUm0deL]

Hi Rudy, I hear what your saying, your explanation is good. And, I tried it...but no luck. See the only thing differing is the column GROUPING. See below, items in red belong to one photo group, while items in blue belong to another -- yet both photo groups belong to the same cat/subcat combo.

PHOTOID  CATID  SUBCATID  GROUPING  PICNAME  ISACTIVE    
[COLOR=red]1         3      7        TGROUP1   1710.jpg    True
2         3      7        TGROUP1   1717.jpg    True
6         3      7        TGROUP1   1721.jpg    True
7         3      7        TGROUP1   1730.jpg    True
5         3      7        TGROUP1   1711.jpg    True[/color]
[COLOR=blue]8         3      7        ZGROUP2   5056.jpg    True
9         3      7        ZGROUP2   5062.jpg    True
10        3      7        ZGROUP2   5076.jpg    True[/color]

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[r937]

okay, in that case, let's ahve another look at your original question:

how can I get the query to work so that I can only get results where all images are in one particular group set?

the answer is, you would have to specify which group set you want, in the WHERE clause


r937.com | rudy.ca