Topic awaiting preservation: a simple mysql query question...

Hello,

I have a simple table with these fields:
id id2
values are:
1,1
1,2
1,3
2,1
2,3
2,99
3,1
3,3
3,5
4,9
4,1
5,1
5,6
6,1
6,2
7,5
7,9

How can i make a query that will return all records that have id2 = 1 and 3


Elias

SELECT * FROM your_table WHERE id2 = 1 OR id2 = 3

At least I think that should do the trick.

-Butcher-

No...not that what I am asking...

The query that I want would only return the fields that have id is same and id2 is 1, 3
The result would be this:
id=1,id=2,id=3
Because those ids have id2=1,2,3 and the other ids have only 1 or only 3 but not both.

Elias

if I understood you correctly, you'll need to use 'group by'

try
SELECT * FROM your_table WHERE id2 = 1 AND id2 = 3 GROUP BY yid

Hello,

TP, This didn't return anything!

My colleagues suggest that this can be accomplished via nested select statements...

I don't know how to do it, besides, MySql doesn't support that directly.

Elias

the suggest mssql solution is:

quote:

---

SELECT DISTINCT ID1
FROM TestTable
WHERE ID1 IN (SELECT ID1 FROM TestTable WHERE ID2 = 1)
AND ID1 IN (SELECT ID1 FROM TestTable WHERE ID2 = 2)

---

I wonder how to write it in MySql ...

Elias

lallous: The problem is that MySQL doesn't appear to allow subselects (despite what it says in my PHP book ).

Can't you do something like:

SELECT * FROM your_table WHERE id = id2

I've never tried it but that could work - if not then I'd try and JOIN the table to itself so:

SELECT *
FROM your_table AS yt1,
your_table AS yt2
WHERE yt1.id = yt2.id2

or something like that. Again I've not tried it but I suspect it might be possible - if not you could load your_table into a temporary table and then join the two - that would work but I suspect there is a more elegant solution.

[edit: Yeah it looks like self-joins might be an answer:
www.devshed.com/Server_Side/MySQL/Join/page5.html
www.mysql.com/doc/en/Multiple_tables.html

its the last bit of the manual on that page.]

___________________
Emps

FAQs: Emperor

Sorry I misunderstood the question the first time around lallous.

Emps way of thinking (joining the table to itself) will work correctly.

-Butcher-