Model Icolor
m1 i1
m1 i2
m1 i3
m2 i2
m2 i3
m2 i4
I am having a table like this, i need to select Icolor values which is common to both model m1 and m2.
views:
59answers:
4
A:
select lcolor from Model where m1 = x
union
select lcolor from Model where m2 = y
x is your model m1 and y is your model m2 value or if it is a bit datatype then you've got a 1 there. union gets you the shared values from the results of both queries. I'm not sure if the syntax is correct (but I'm pretty sure it is). If not you can google it.
Gambrinus
2009-12-23 11:12:33
+1
A:
DECLARE @TABLE TABLE
(
Model VARCHAR(30),
LColour VARCHAR(30)
)
INSERT INTO @TABLE
SELECT 'm1','i1' UNION
SELECT 'm1','i2' UNION
SELECT 'm1','i3' UNION
SELECT 'm2','i2' UNION
SELECT 'm2','i3' UNION
SELECT 'm2','i4'
SELECT
LColour
FROM
@TABLE
GROUP BY
LColour
HAVING
COUNT(*) = (SELECT COUNT(DISTINCT Model) FROM @TABLE)
Edited to give all colours that are common to every model :)
Meff
2009-12-23 11:15:25
Interesting solution. But what about performance? (it's not related to the question but it's rather interesting for me)
Roman
2009-12-23 11:39:20
Performance should be acceptable, this is (So far as I know) the best way to achieve this, as there are no self-joins or unions used. This table could be indexed for this query, but without a real schema it's difficult to suggest. Data distribution also plays a part.I've used similar approaches in the past, where I had to find an ID in a table that matched at least four out of possibly hundreds values, and found HAVING COUNT(*) = 4 worked very well for me :)
Meff
2009-12-23 11:45:59
I made some performance tests. It's not too slow but my solution is faster in 5 times (and I believe that my solution is also not the fastest possible).
Roman
2009-12-23 12:02:23
Hi Meff,The table i am having is having many models, i have to select a color which is common to all models.
Pugazh
2009-12-23 12:19:00
Edited to fulfil your requirements ;)
Meff
2009-12-24 11:48:45
A:
UPDATED:
select color from
(select color, T2.num from test_table,
(select count(*) as num from (select distinct model from test_table) T) T2
group by color, T2.num
having count(*) = T2.num) T3
Roman
2009-12-23 11:31:56
Hi Roman, The table i am having is having many models, i have to select a color which is common to all models.
Pugazh
2009-12-23 12:22:27
Hi Roman, you said your initial solution was faster than mine, but haven't you now turned yours into a correlated subquery? That'll give a new query per row, making mine faster surely :p
Meff
2009-12-24 11:54:03
I wrote my comment when we assumed that only 2 models exist. Now I'd prefer your solution. but anyway no one cares.
Roman
2009-12-24 12:27:48
A:
I beleive it shuld be something like this:
SELECT tt.Icolor
FROM test_table tt
INNER JOIN test_table tt1 ON tt1.Icolor = tt.Icolor AND tt1.Model = 'm2'
WHERE tt.Model = 'm1'
krig
2009-12-23 11:38:25
Hi Krig,The table i am having is having many models, i have to select a color which is common to all models.
Pugazh
2009-12-23 12:18:24
I'm doing almost the same for my application. I'm preparing query from user input by adding INNER JOIN for each additional item. INNER JOIN test_table ttN ON ttN.Icolor = tt.Icolor AND ttN.Model = 'mN' I don't know what technology you are using but this is very simple solution for PHP. And performance is good for this query with indexes on Model and Icolor.
krig
2009-12-23 13:24:06