SQL help needed in finding common data

Forum Moderators: open

Message Too Old, No Replies

SQL help needed in finding common data

within the same table

solly

3:11 am on Jul 13, 2006 (gmt 0)

I have tried to solve this, and I know the answer lies in an inner join, or something even more simple...

I have this table:

+--------+------+
Ś number Ś id Ś
+--------+------+
Ś 1 Ś 10 Ś
Ś 1 Ś 11 Ś
Ś 1 Ś 12 Ś
Ś 2 Ś 50 Ś
Ś 2 Ś 7 Ś
Ś 2 Ś 11 Ś
Ś 3 Ś 11 Ś
+--------+------+

All I need to do is find where the column 'id' occurs in each of the 'number' fields. So, in the above example, the desired result is '11' because the number 11 occurs at least once in number 1, number 2, and number 3.

How do I get there? Thanks!

aspdaddy

12:28 pm on Jul 13, 2006 (gmt 0)

SELECT table1.ID
FROM table1
GROUP BY table1.ID
HAVING Count(table1.Number) = (SELECT COUNT (NUMBER) FROM (SELECT DISTINCT NUMBER FROM TAble1))

Graham

4:55 pm on Jul 13, 2006 (gmt 0)

SELECT DISTINCT Table1.id
FROM Table1
WHERE (((Table1.id) In (SELECT [id] FROM [Table1] As Tmp GROUP BY [id] HAVING Count(*)>1 )));

aspdaddy

6:23 pm on Jul 13, 2006 (gmt 0)

HAVING Count(*)>1

That wont work with other data sets, it needs to only return if the column 'id' occurs in each of the 'number' fields, not just more than 1

solly

7:20 pm on Jul 13, 2006 (gmt 0)

I thank you both for the replies....been busy today with other projects...but a quick mea culpa on the original data set. It is also possible that there are duplicates in each one. (* denotes the difference):

--------+------+
Ś number Ś id Ś
+--------+------+
Ś 1 Ś 10 Ś
Ś 1 Ś 11 Ś
Ś 1 Ś 11 Ś *
Ś 1 Ś 12 Ś
Ś 2 Ś 50 Ś
Ś 2 Ś 7 Ś
Ś 2 Ś 11 Ś
Ś 3 Ś 11 Ś
+--------+------+

I am using MySQL, so I had to modify the queries, but yours, aspdaddy, had trouble with the duplicate. Graham, at first glance, yours seems to work, but I hadn't had time to figure out why and don't know if it will work in all instances.

solly

10:20 pm on Jul 13, 2006 (gmt 0)

So, in MySQL, will this work?

SELECT table1.id
FROM table1
GROUP BY table1.id HAVING COUNT(table1.number)
>= (SELECT COUNT(number) FROM (SELECT DISTINCT(number) FROM table1) AS t2)

Basically the same as aspdaddy's query, except w/ an alias to make MySQL happy and the ">=" to allow for dups. I've yet to test it extensively...thanks.

syber

6:04 pm on Jul 14, 2006 (gmt 0)

This should work correctly, it takes into account duplicate id's for the same number and only brings back those id's that are present for every number.


SELECT id 
FROM 
( SELECT number, id 
 FROM table1 
 GROUP BY number, id) AS t2 
GROUP BY ID 
HAVING COUNT(*) = (SELECT COUNT (DISTINCT number) FROM table1)