How can I select a Colors and the first Image associated with that Colors?

Question

0.00/5 (No votes)

See more:

I have 2 tables named Colors and ColorImages. ColorImages have multiple images for a single color

What I'm trying to do: I want a grid of Colors with one picture from ColorImages table in my database. So I'm pulling all of the colors with this SQL command.

SQL

SELECT * FROM Colors

But I also want a picture from ColorImages

SQL

SELECT * FROM Colors
INNER JOIN ColorImages ON Colors.ColorID = ColorImages.ColorID
(SELECT TOP 1 ColorImages.ColorImage FROM ColorImages
 WHERE Colors.ColorID = ColorImages.ColorID)

When I try to test the query it tells me I have incorrect syntax near "SELECT" and ")"

Posted 11-Sep-11 18:33pm

AditSheth

Add a Solution

2 solutions

Solution 2

Hello Friend use this

SQL

with TEMP_TABLE  (ID, Color,IMAGEID,TEMP_ID,ColorImage,Ranking)
as (
SELECT     Colors.ColorID, Colors.Color, ColorImages.ImageID, ColorImages.ColorID AS TEMP_ID, ColorImages.ColorImage
,rank() over (Partition by Colors.Color Order by ColorImages.ImageID) MyRank
FROM         Colors INNER JOIN
                      ColorImages ON Colors.ColorID = ColorImages.ColorID
)

SELECT * from TEMP_TABLE where
Ranking   = 1 ORDER BY TEMP_TABLE.ID

Posted 11-Sep-11 20:20pm

Tejas Vaishnav

Updated 14-Sep-11 2:02am

v2

Comments

AditSheth 12-Sep-11 2:36am

Hi, can you update your code with my table structure. I am not getting what you code
My Table structure is as below
Colors : ColorID,Color
ColorImage : ImageID,ColorImage

AditSheth 12-Sep-11 2:37am

Hi Tejash,
Accepted solution works fine and takes very less time to execute..
but i still want to know your solution

Tejas Vaishnav 14-Sep-11 8:05am

Hello i have done changes as per filed
Colors : ColorID,Color
ColorImage : ImageID,ColorImage

please check it...
and also gave me the response...
if it work for your requirement then accept my answer too and also rate it...

thanks

Tejas Vaishnav

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prerak Patel · Accepted Answer · 2011-09-11T18:43:00

Solution 1

I think, you want this

SQL

SELECT * FROM Colors
INNER JOIN (SELECT TOP 1 ColorImages.ColorImage FROM ColorImages
 WHERE Colors.ColorID = ColorImages.ColorID) ColorImages ON Colors.ColorID = ColorImages.ColorID

[Edit]
Try nested query

SQL

SELECT *,(SELECT TOP 1 ColorImages.ColorImage FROM ColorImages WHERE Colors.ColorID = ColorImages.ColorID) ColorImages FROM Colors

Posted 11-Sep-11 18:43pm

Prerak Patel

Updated 11-Sep-11 19:34pm

v2

Comments

AditSheth 12-Sep-11 1:10am

Thanks for Quick response.
But,its not work . Gives error "Colors.ColorID" could not be bound.
How you can bind "Colors.ColorID" in inner select ?
i think its wrong.

Prerak Patel 12-Sep-11 1:34am

Sorry, try nested query. Updated the answer.

AditSheth 12-Sep-11 2:03am

Yes Nested Query works fine.
Thanks

Prerak Patel 12-Sep-11 2:09am

You are welcome.