Hi,
i know this is probably a simple answer and i probably drunk too much coffee!
given the following sql data:
ID value
1 A
1 B
1 C
2 A
2 B
3 A
3 B
3 C
i need to run a query that says return me the ids that are linked to values A
and B and C.
so this query on the
Something like...
SELECT
ID
FROM
MyTable
WHERE
value IN (cfqueryparam cfsqltype=cf_sql_varchar
value=#myList# list=true /)
;
??
On Mon, Sep 26, 2011 at 12:18 PM, Richard White rich...@j7is.co.uk wrote:
Hi,
i know this is probably a simple answer
ids that are linked to values A and B and C.
If you mean only ID's linked to all three (3) values? Something like this
cfset distinctValues = A,B,C
...
SELECT ID, COUNT(Value) AS MatchCount
FROM TableName
WHERE ID IN ( cfqueryparam value=#distinctValues# list=true
Richard,
I think this will work (untested, assumes SQL Server):
select t.ID
from mytable t
inner join mytable a on t.id = a.id and a.value = 'A'
inner join mytable b on t.id = b.id and b.value = 'B'
inner join mytable c on t.id = c.id and c.value = 'C'
HTH,
Carl
I'm sure there is a much more efficient way of doing this, but this would
work:
SELECT distinct ID from table where id in
(select ID from table where val = 'A' and id in
(select id from table where val = 'B' and ID in
(select id from table where val = 'C')))
On Mon, Sep 26, 2011 at 1:18 PM,
Yup, I think Carl's is the best, though you'd probably want to throw a GROUP
BY in there so you don't get multiple rows for the same ID.
-- Josh
On Mon, Sep 26, 2011 at 10:41 AM, Carl Von Stetten
vonner.li...@vonner.netwrote:
Richard,
I think this will work (untested, assumes SQL Server):
Or change the first line to:
select distinct t.ID (again assumes SQL Server)
Carl
On 9/26/2011 10:44 AM, Josh Nathanson wrote:
Yup, I think Carl's is the best, though you'd probably want to throw a GROUP
BY in there so you don't get multiple rows for the same ID.
-- Josh
On Mon, Sep 26,
thanks, works perfect!
Hi,
i know this is probably a simple answer and i probably drunk too much
coffee!
given the following sql data:
ID value
1 A
1 B
1 C
2 A
2 B
3 A
3 B
3 C
i need to run a query that says return me the ids that are linked to
If you mean only ID's linked to all three (3) values? Something like this
Duh. Just noticed I left off the GROUP BY...
cfset distinctValues = A,B,C
...
SELECT ID, COUNT(Value) AS MatchCount
FROM TableName
WHERE
ID IN ( cfqueryparam value=#distinctValues# list=true
9 matches
Mail list logo
