SQL query help using mySQL

[sjohri214]

Hi,
I Hope someone can help me,

I have the following problem:

I have a table to query, which is a s follows:

Table: ECReact
id: int (11)
ReactID: varchar(5)
Ecid: int(11)
EC:varchar(12)

the question i have is, how do i check in the column ReactID for entries that have the same ECid occurring more than once ?

The query i came up with was as shown below:

SELECT ReactID, Ecid AS tmp
FROM ECReact
WHERE (SELECT COUNT (*)
FROM ECReact
WHERE Ecid = tmp) > 1;

however, i was getting exceptions thrown out - because i'm querying a mySQL database and apparently this does not support nested queries - I was hoping that someone could help me out with this query

Thanks in advance for any assistance

 

[piti]

hi

i'm not sure if this is what you want

SELECT ReactID, Ecid FROM ECReact GROUP BY ReactID, Ecid HAVING count(*) > 1

returns every pair of ReactID, Ecid that is more than once in a table

e.g.
for this data
+---------+------+
| ReactID | Ecid |
+---------+------+
| one | 1 |
| one | 2 |
| two | 1 |
| three | 2 |
| two | 1 |
| one | 1 |
+---------+------+

returns

+---------+------+
| ReactID | Ecid |
+---------+------+
| one | 1 |
| two | 1 |
+---------+------+

 

[sjohri214]

Hi Piti,

Thanks for the reply, I don't think my question clearly explained what i wanted, so let
me try again.

+----------+------+
|ReactID | ECid |
+----------+------+
|v |1 |
|w |1 |
|x |2 |
|y |4 |
|z |1 |
| | |
+----------+------+

so if i have the above table, i want to be able to extract the ReactId's that share the same ECid - so for the above table
I would like to extract v,w + z because these are the ReactId's that share the same Ecid.

Thanks again,

from sjohri214

 

[piti]

if you want to find such rows for an explicit Ecid

SELECT ReactID, Ecid FROM ECReact WHERE Ecid = <value>

if value = 1

result is

+---------+------+
| ReactID | Ecid |
+---------+------+
| w | 1 |
| v | 1 |
| z | 1 |
+---------+------+

or if all rows that share some Ecid

SELECT DISTINCT A.ReactID, A.Ecid FROM ECReact As A INNER JOIN ECReact As B ON A.Ecid = B.Ecid AND A.id <> B.id

result is (should be ;-)) the same

if we add another row to the data, let's say (ReactID = zz, Ecid = 2), then the result is

+---------+------+
| ReactID | Ecid |
+---------+------+
| zz | 2 |
| v | 1 |
| z | 1 |
| w | 1 |
| x | 2 |
+---------+------+

 

[sjohri214]

Hi Piti,

Thanks for the help with the query - much appreciated