Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
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
Comments
Amir Mahfoozi at 20-Dec-11 5:40am
   
How are things going ?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Refer to this post[^] for various ideas.
  Permalink  
v4
Comments
Marcus Kramer at 15-Dec-11 11:30am
   
+5. It's almost too easy to find homework answers using search engines. :)
RaviRanjankr at 15-Dec-11 16:44pm
   
5+
Nikil S at 15-Dec-11 17:48pm
   
Thanks Marcus, yeah sometimes search engines helps prevent reinvent the wheel :)
Nikil S at 15-Dec-11 17:48pm
   
Thanks Ravi
Rate this: bad
good
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...
 
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
  Permalink  
Comments
DINESH K MAURYA at 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 at 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 at 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 at 15-Dec-11 16:44pm
   
5+
Rate this: bad
good
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
(
  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.
  Permalink  
Comments
Marcus Kramer at 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 at 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 at 15-Dec-11 16:45pm
   
5+
Amir Mahfoozi at 15-Dec-11 23:58pm
   
Thanks Ravi
Rate this: bad
good
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.
  Permalink  
Comments
Marcus Kramer at 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
0 OriginalGriff 490
1 Gihan Liyanage 338
2 ChauhanAjay 180
3 Vinay Mistry 160
4 Sergey Alexandrovich Kryukov 153
0 Sergey Alexandrovich Kryukov 9,011
1 OriginalGriff 7,941
2 CPallini 2,603
3 Richard MacCutchan 2,121
4 Abhinav S 1,928


Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 15 Dec 2011
Copyright © CodeProject, 1999-2014
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