James Neff wrote:
Greetings,
I need help with a select statement that finds duplicate sets of rows.
I have a table like so:
batch_data
-----------------------
ID - int (auto inc)
Record ID - int
DataValue - VarChar(99)
DataType - int
With a sample of data like this:
1 100 1122A 1
2 100 1350G 1
3 100 1001 2
4 101 1122A 1
5 101 1350G 1
6 101 1002 2
7 102 8493P 1
8 102 1003 1
What I want to do is return the DataValue where theDataType = "2" of the
duplicate set of matching datavalue records where data type = "1".
In this case I want the value of "1002" returned (from row 6) because
there are 2 matching DataValue rows, 1 & 2, which match rows 4 & 5.
A set of DataValue's is defined by the RecordID. So set 1 consists of
rows 1 to 3, set 2 is 4 to 6, and set 3 is rows 7 & 8.
Is this possible to do in one SELECT statement, or should I just iterate
through a resultset in my application?
I don't think you can do it in one statement but this should give you a
start.
This will get you the values:
select t1.datavalue from tablename t1, tablename t2 where
t1.recordid=t2.recordid and t1.datavalue=t2.datavalue and
t1.datatype=t2.datatype group by t1.datavalue having count(*) > 1;
+-----------+
| datavalue |
+-----------+
| 1122A |
| 1350G |
+-----------+
2 rows in set (0.00 sec)
then you can get the appropriate recordid and do another self join:
select t1.recordid from blah t1, blah t2 where t1.datatype='2' and
t1.recordid=t2.recordid and t2.datavalue in ('1122A', '1350G') group by
t1.recordid;
+----------+
| recordid |
+----------+
| 100 |
| 101 |
+----------+
2 rows in set (0.00 sec)
Not a "great" option but it should work ;)
There's probably a better way..
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
