Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
5.00/5 (1 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
Comments
Amir Mahfoozi 20-Dec-11 5:40am    
How are things going ?

Refer to this post[^] for various ideas.
 
Share this answer
 
v4
Comments
fjdiewornncalwe 15-Dec-11 11:30am    
+5. It's almost too easy to find homework answers using search engines. :)
RaviRanjanKr 15-Dec-11 16:44pm    
5+
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
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...

SQL
SELECT 
    modelid,  modelno, colorname
FROM 
    Model m
INNER JOIN
    ModelColor mc ON M.modelid = MC.modelid
INNER JOIN
    Color C ON MC.colorid = C.colorID


Your data is now normalised and this SQL will return all the models and possible color options
 
Share this answer
 
Comments
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
fjdiewornncalwe 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    
5+
Here it is :

SQL
select modelid, modelno, s.colorid, [color name] from (
select modelid, modelno ,colorId from
(
  SELECT
     modelno, modelid, CAST('<r>' + REPLACE(colorid, ',', '</r><r>') + '</r>' AS XML) coloridXml
  FROM Model
) newColorid
CROSS APPLY (
  SELECT
    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.
 
Share this answer
 
Comments
fjdiewornncalwe 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    
5+
Amir Mahfoozi 15-Dec-11 23:58pm    
Thanks Ravi
use Cursor or Row_Number()
after that use split() and insert into temp table.
fetch records from temp table.
 
Share this answer
 
Comments
fjdiewornncalwe 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)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900