List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:November 11 2002 8:08pm
Subject:Re: Problems with simple(?) query
View as plain text  
In the last episode (Nov 11), James Dyer said:
> If I create a table called foo like this:
> create table foo ( id1 int(11) , id2(int 11) );
> 
> populate it with data and then try:
> 
> SELECT * FROM foo WHERE id2=1 AND id2=2 (for example),
> 
> I just get an empty set returned. An explain on the query gives an
> 'Impossible WHERE' message.
> 
> Both id1 and id2 can have duplicate values in them, though the same
> combination of id1 and id2 cannot appear in the data (ie: id1=1,
> id2=1 ; id1=1, id2=2 is possible, id1=1, id2=1 ; id1=1, id2=1 is not
> possible), and it is possible that the values I search for will not
> be in the table at all.
> 
> All I really want is to get the value for id1 where there are
> corresponding records which match all of my search parameters for
> id2.

You need a self-join.

SELECT foo1.id1 
FROM foo AS foo1, foo AS foo2 
WHERE foo1.id1=foo2.id1 AND foo1.id2=1 AND foo2.id2=2;
 
-- 
	Dan Nelson
	dnelson@stripped
Thread
Problems with simple(?) queryJames Dyer11 Nov
Re: Problems with simple(?) queryDan Nelson11 Nov
RE: Problems with simple(?) queryPeter Grigor11 Nov
Re: Problems with simple(?) queryKeith C. Ivey11 Nov
  • Re: Problems with simple(?) queryMichael T. Babcock11 Nov
Re: Problems with simple(?) queryJohn Ragan12 Nov
  • How stable is MySQL 4.x vs 3.23.x?Jaime Teng12 Nov
    • Re: How stable is MySQL 4.x vs 3.23.x?Jeremy Zawodny12 Nov
  • RE: Problems with simple(?) queryArthur Fuller18 Nov
RE: How stable is MySQL 4.x vs 3.23.x?Kelly W [PCS] Black12 Nov