Dear All,



my table looks like this :
code size
1n3j 14
1n3j 32
1n3j 37
1n9j 14
1n9j 32
1n9j 14
1nm4 14
1nm4 37
1nm4 32
1nmi 14
1nmi 14
1oo3 14
1oo3 14
1oo4 14
1oo4 14

I want to select those rows with 'size' 14, 32 and 37. This is easy..but I
also want only those uniq codes which have all the 'size' (14,32 and 37)
associated with it. Please note that none of the fields are uniq.
Here in this case I want result like

1n3j 14
1n3j 32
1n3j 37
1nm4 14
1nm4 37
1nm4 32

because 1n3j and 1nm4 are the only two codes which have all the 'size's
requested ie., 14,32, 37.

Hope my question is clear.

Any kind of help is highly appreciated.

Best

Rajesh

Hey thangudu,

I would try the following, with and without the DISTINCT option in it:

Hope that helps.

That's a pretty cool trick. I'll have to remember that one. :)

In this case, I think TABLE1 == TABLE2 == TABLE3

Seems to work under mysql anyhow.

Just trying to help. =)

Hi,
Thank you but that did not work. itz returning an empty set.
Here am including more information hoping for a better suggestion from you.

This is the structure of table...
+------+-----+-----+------+-----+-----+-----+----+------+
| res1 | ch1 | no1 | res2 | ch2 | no2 | grd | size | code |
+------+-----+-----+------+-----+-----+-----+----+------+
| LEU | A | 34 | PHE | A | 64 | A | 31 | 1n3j |
| VAL | A | 37 | ALA | A | 59 | A | 23 | 1n3j |
| SER | A | 71 | THR | A | 102 | C | 32 | 1n3j |
| PRO | A | 74 | TYR | A | 105 | C | 32 | 1n3j |
| ASN | A | 75 | THR | A | 102 | B | 28 | 1n3j |
| HIS | A | 78 | ILE | A | 103 | A | 26 | 1n3j |
| LEU | B | 34 | PHE | B | 64 | A | 31 | 1n3j |
| VAL | B | 37 | ALA | B | 59 | A | 23 | 1n3j |
| SER | B | 71 | THR | B | 102 | C | 32 | 1n3j |
| PRO | B | 74 | TYR | B | 105 | C | 32 | 1n3j |
| ASN | B | 75 | THR | B | 102 | B | 28 | 1n3j |
| HIS | B | 78 | ILE | B | 103 | A | 26 | 1n3j |
| PRO | A | 2 | GLU | A | 27 | C | 26 | 1nm4 |
| SER | A | 6 | HIS | A | 24 | B | 19 | 1nm4 |
| VAL | A | 30 | PRO | A | 63 | C | 34 | 1nm4 |
| LEU | A | 38 | ALA | A | 55 | B | 18 | 1nm4 |
| ALA | A | 42 | THR | A | 78 | B | 37 | 1nm4 |
| LYS | A | 54 | ALA | A | 71 | B | 18 | 1nm4 |
| LYS | A | 54 | SER | A | 72 | B | 19 | 1nm4 |
| VAL | A | 81 | VAL | A | 97 | C | 17 | 1nm4 |
+------+-----+-----+------+-----+-----+-----+----+------+


mysql> select A.*, count(distinct B.ls) as lss from test A left join test B on (A.code=B.code and B.ls in (14,5,37)) group by A.code,A.ls having lss=3 and A.ls in (14,17,37);
+------+-----+-----+------+-----+-----+-----+----+------+-----+
| res1 | ch1 | no1 | res2 | ch2 | no2 | grd | size | code | lss |
+------+-----+-----+------+-----+-----+-----+----+------+-----+
| ALA | A | 56 | THR | A | 69 | C | 14 | 1nm4 | 3 |
| VAL | A | 81 | VAL | A | 97 | C | 17 | 1nm4 | 3 |
| ALA | A | 42 | THR | A | 78 | B | 37 | 1nm4 | 3 |
+------+-----+-----+------+-----+-----+-----+----+------+-----+
3 rows in set (0.06 sec)


This is wroking, in the sense 1nm4 is only code in the above table which has all the sizes (14,17,37) associated with it. But there is one more missing thing here...there is more than one record where size is 14 and code is 1nm4. This sql query is returning only one of them. Can you help me to refine it.

And also suggest me if indexing is needed as my original table has morethan 2 million records.

Best

I'm to familiar with mySQL but from what i see in your sql query you are only grabbing the ones that exactly have a count of 3 you might wanna try the following:

or if that does not work you might be only able to do one of the following < > or = in that case try this:

Let me know how this turns out for you.

Take care.

Yeah ...it did look like a count more than 3 should return the required ouput. I have tried both these syntax without success.

What might be the reason...some loose end in the logic?

Best

Rajesh

Glad u got the query work with the following:



Just in case anybody else out there has a similar question or problem.
Now onto your index problem.

Yes, now the problem solved. I am getting the desired result but taking too long more than 4 hrs. I have created index on two columns (code and ls) and both together also.

The EXPLAIN says...

mysql> explain select A.*, count(distinct B.ls) as lss from dsdbase A left join dsdbase B on (A.code=B.code and B.ls in (37,14,38,32)) group by A.code, A.ls, A.no1, A.no2 having lss>=3 and A.ls in (37,14,38,32) order by A.code;

+-------+------+-----------------------+--------+---------+--------+---------+----------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+-----------------------+--------+---------+--------+---------+----------------+
| A | ALL | NULL | NULL | NULL | NULL | 2385617 | Using filesort |
| B | ref | cindex,lindex,lcindex | cindex | 4 | A.code | 122 | Using where |
+-------+------+-----------------------+--------+---------+--------+---------+----------------+
2 rows in set (0.34 sec)


Any pointers.

Thank you.