Click here to Skip to main content
13,141,561 members (26,479 online)
Rate this:
Please Sign up or sign in to vote.
See more:
problem is when we select color name and colorid is 8,2 or more than one color id in table
it is my table
modelid  modelno            colorid
66	S108	                 1
67	S108-Metallic Red	8,2
68	Sleec	                9
69	Sleec-White	        1
70	Vibe                    2
71	Vibe-Black	        6
84	Aura	                2
114	S09 Metallic Red	1,2
115	S09 test	        1
117	S09 mobile tester	1
120	S08	                1,2
121	S09 mobile	        1

color table
colorid    color name
1	Metallic Red
2	Black
3	Military Green
4	Golden Yellow
5	Metallic Blue
6	Gold
7	Special Black
8	White
9	Dark Silver
10	Pink
11	Dark Brown

how to select model with color name
thank in advance
Posted 15-Dec-11 1:06am
Amir Mahfoozi 20-Dec-11 5:40am
How are things going ?
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Refer to this post[^] for various ideas.
Marcus Kramer 15-Dec-11 11:30am
+5. It's almost too easy to find homework answers using search engines. :)
RaviRanjankr 15-Dec-11 16:44pm
Nikil S 15-Dec-11 17:48pm
Thanks Marcus, yeah sometimes search engines helps prevent reinvent the wheel :)
Nikil S 15-Dec-11 17:48pm
Thanks Ravi
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

I'd suggest using a third table for 'modelcolor'. Remove the colorid column from your model table and create a third table along these lines

id        modelid        colorid
1         67             8
2         67             9

You can now query like so...

    modelid,  modelno, colorname
    Model m
    ModelColor mc ON M.modelid = MC.modelid
    Color C ON MC.colorid = C.colorID

Your data is now normalised and this SQL will return all the models and possible color options
DINESH K MAURYA 15-Dec-11 8:16am
Dear sir i your insert two color in colorid then what will happen
my problem that if your insert two color for a model.
Dylan Morley 15-Dec-11 8:24am
Try it out, see what happens!

What I've demonstrated is a very common relational data pattern for normalising data. When 1 item (model) can have many items (Color)

The third table (modelcolor) provides the 1-to-many relationship
Marcus Kramer 15-Dec-11 11:33am
I like your thinking and agree that the data model is a problem here, but I suspect this is a homework question to teach the students how to deal with non-normalized data. It's actually a pretty good practical example. Most developers will likely have run into similar situations where the structure couldn't be normalized properly. +5 for pointing this out though.
RaviRanjankr 15-Dec-11 16:44pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 4

Here it is :

select modelid, modelno, s.colorid, [color name] from (
select modelid, modelno ,colorId from
     modelno, modelid, CAST('<r>' + REPLACE(colorid, ',', '</r><r>') + '</r>' AS XML) coloridXml
  FROM Model
) newColorid
    colorId.value('.', 'int') colorId
  FROM newColorid.coloridXml.nodes('r') AS colorIds(colorId)
) splited
) s  inner join color c on s.colorid= c.colorid

Hope it helps.
Marcus Kramer 15-Dec-11 11:35am
This is clearly a homework question. Helping the OP is what we strive to do, but simply dumping a code solution for them is not going to help the OP in any way. Sure they'll copy and paste this and it may work, but they won't know why and when they get a good mark and enter the workforce, they won't know anything.
Amir Mahfoozi 15-Dec-11 11:46am
I am dubious about it to be a homework, it is like a bad designed table which was leaved to him to do some works on it. BTW, if you were moderator of stackoverflow you had to delete all of the responses every day because you hate providing source codes. Please do not be a negative person.
RaviRanjankr 15-Dec-11 16:45pm
Amir Mahfoozi 15-Dec-11 23:58pm
Thanks Ravi
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

use Cursor or Row_Number()
after that use split() and insert into temp table.
fetch records from temp table.
Marcus Kramer 15-Dec-11 11:31am
Not a great way to approach this. It would likely work, but very inefficiently.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web02 | 2.8.170915.1 | Last Updated 15 Dec 2011
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100