database driven gallery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

database driven gallery

Author

Topic

ScottBot
Starting Member

16 Posts

Posted - 2011-05-09 : 07:54:26

I have a database with several categories in it, each category has several images associated to them, some categories even have a same image as it can belong to multiple categories.

Categories and images have their own database table and are joined with a junction table to allow multiple categories per image.

What I'm trying to achieve and failing miserably at is to pull each category from the database to display as a heading accompanied by the last uploaded image assigned to that category.

On a single connection I keep getting multiple Images out per category or a single image and only one category but never all categories with one image.

I have also tried using a datalist within a datalist and two connections . I could not get the internal datalist to react from the categories which were pulled by the outer datalist.

I may well be going completely wrong here so any one with a better idea or a solution I am happy to listen and learn.

Thanks in advance

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-09 : 08:57:10

Would you like to provide, table structure, sample data and required output. As it will really help us to understand your problem.

--------------------------
http://connectsql.blogspot.com/

ScottBot
Starting Member

16 Posts

Posted - 2011-05-09 : 09:27:01

The three tables are as follows.

Categories - c_ID,c_Name

JunctionTable - c_ID,a_ID

AssetLibrary - a_ID,a_Name,a_Type

Some of the categories I have are: Sports, Drama, Music and General

Obviously some of the images can be classified as Sports and General.

Now what I want to happen is on my Gallery Page, I want it to show me all the categories that I have created accompanied by the latest image that was uploaded into each category.

So on my page I would get:
Sports<img ... alt="sportsday"/>
Music<img ... alt="Grand Piano"/>
Drama<img ... alt="Les Miserables"/>
General<img .. alt="sportsday"/>

I have been tinkering with the SQL command and just cant get anything to pull each category accompanied by a single image, the code I have so far is;

SELECT t_Categories.c_ID, t_Categories.c_Name, t_Categories.f_ID, t_AssetLibrary.al_Name, t_AssetLibrary.al_Type
FROM t_Categories INNER JOIN
t_AssetCategory ON t_Categories.c_ID = t_AssetCategory.c_ID INNER JOIN
t_AssetLibrary ON t_AssetCategory.al_ID = t_AssetLibrary.al_ID
WHERE (t_AssetLibrary.al_Type = 'Image') AND (t_AssetLibrary.al_Name <> 'No_Image.jpg')
GROUP BY t_Categories.c_ID, t_Categories.c_Name, t_Categories.f_ID, t_AssetLibrary.al_Name, t_AssetLibrary.al_Type

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-09 : 09:59:48

If the last image means the one with the highest al_id,
try this

SELECT cat.c_ID, cat.c_Name, cat.f_ID, lib.al_Name, lib.al_Type
FROM       t_Categories cat 
INNER JOIN (select c_id,max(al_id) as a_if
            from t_AssetCategory
            group by c_id)
           )tac   ON 
                               cat.c_ID = tac.c_ID
INNER JOIN t_AssetLibrary    ON 
                               tac.al_ID = lib.al_ID
WHERE (lib.al_Type = 'Image') AND (lib.al_Name <> 'No_Image.jpg')
GROUP BY cat.c_ID, cat.c_Name, cat.f_ID, lib.al_Name, lib.al_Type

Jim

Everyday I learn something that somebody else already knew

ScottBot
Starting Member

16 Posts

Posted - 2011-05-09 : 10:15:00

Hi Jim thanks for helping,

I get the following error

Error in GROUP BY clause.
Unable to parse query text.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-09 : 10:22:00

Hopefully just a bunch of typos


SELECT cat.c_ID, cat.c_Name, cat.f_ID, lib.al_Name, lib.al_Type
FROM       t_Categories cat 
INNER JOIN (select c_id,max(al_id) as al_id
            from t_AssetCategory
            group by c_id)
           )tac   ON 
                               cat.c_ID = tac.c_ID
INNER JOIN t_AssetLibrary lib    ON 
                               tac.al_ID = lib.al_ID
WHERE (lib.al_Type = 'Image') AND (lib.al_Name <> 'No_Image.jpg')
GROUP BY cat.c_ID, cat.c_Name, cat.f_ID, lib.al_Name, lib.al_Type

Jim
You are using Microsoft SQL Server, yes?

Everyday I learn something that somebody else already knew

ScottBot
Starting Member

16 Posts

Posted - 2011-05-09 : 10:30:28

Yep using MS SQL 2008

I get the same error followed by a SQL Execution Error

Error Source: .Net SqlClient Data Provider
Error Message: Incorrect syntax near ')'.

ScottBot
Starting Member

16 Posts

Posted - 2011-05-09 : 10:41:45

found it there was a double ) where only a single was required and it works beautifully!!!

Thanks Jim you have taught me something new today and it's appreciated.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-09 : 10:55:19

You're very welcome.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources