Closed Thread Icon

Topic awaiting preservation: a simple mysql query question... (Page 1 of 1) Pages that link to <a href="http://ozoneasylum.com/backlink?for=12760" title="Pages that link to Topic awaiting preservation: a simple mysql query question... (Page 1 of 1)" rel="nofollow" >Topic awaiting preservation: a simple mysql query question... <span class="small">(Page 1 of 1)</span>\

 
lallous
Paranoid (IV) Inmate

From: Lebanon
Insane since: May 2001

posted posted 05-25-2003 22:03

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

butcher
Paranoid (IV) Inmate

From: New Jersey, USA
Insane since: Oct 2000

posted posted 05-25-2003 23:09

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

At least I think that should do the trick.

-Butcher-

lallous
Paranoid (IV) Inmate

From: Lebanon
Insane since: May 2001

posted posted 05-26-2003 05:39

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

Tyberius Prime
Paranoid (IV) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 05-26-2003 08:24

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

lallous
Paranoid (IV) Inmate

From: Lebanon
Insane since: May 2001

posted posted 05-26-2003 13:28

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

lallous
Paranoid (IV) Inmate

From: Lebanon
Insane since: May 2001

posted posted 05-26-2003 16:53

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

Emperor
Maniac (V) Mad Scientist with Finglongers

From: Cell 53, East Wing
Insane since: Jul 2001

posted posted 05-26-2003 18:33

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

butcher
Paranoid (IV) Inmate

From: New Jersey, USA
Insane since: Oct 2000

posted posted 05-26-2003 20:08

Sorry I misunderstood the question the first time around lallous.

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

-Butcher-

« BackwardsOnwards »

Show Forum Drop Down Menu