Morning all (and especially Rudy, the God of SQL :D), I'm struggling to figure out a way to join a table with itself to return a certain set of rows.
First, the table: mysql> describe servicedata; +-----------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-----------------------+------+-----+---------+-------+ | domain | varchar(100) | | MUL | | | | prefname | varchar(20) | | MUL | | | | prefvalue | varchar(128) | YES | MUL | NULL | | +-----------+-----------------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) Now, a domain will have an arbitrary number of rows in this table (each prefname is a different "preference" for that domain, but the set of preferences which may be saved will vary). What I need to do (simplified example) is get a list of all domains which have prefname = 'foo' and prefvalue = 1, but *don't* have a record with a prefname of 'bar'. I could do a join on this easily enough if was just comparing the value of the other row, but I'm struggling to see how to do what I'm after. Let's say the table contained two domains: mysql> select * from servicedata; +---------------+----------+-----------+ | domain | prefname | prefvalue | +---------------+----------+-----------+ | example.co.uk | foo | 1 | | example.co.uk | bar | 2 | | example.com | foo | 1 | +---------------+----------+-----------+ 3 rows in set (0.00 sec) example.co.uk should *not* appear in the results as it has a prefname of 'foo' with the correct value, but also has a prefname of 'bar' set (its value is irrelevant in this case). However example.com *should* appear in the result, as it has a prefname of 'foo' with the correct value and *doesn't* have a prefname of 'bar' stored. This is a MySQL 4.0.x server, so subqueries are out of the window. I'd appreciate any guidance - I think there's a simple way to do this in one query, but it's eluding me at the moment. Time for coffee. Cheers Dave P -- David Precious http://blog.preshweb.co.uk/ :: http://www.preshweb.co.uk/ ____ The WDVL Discussion List from WDVL.COM ____ To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: [email protected] To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk ________________ http://www.wdvl.com _______________________ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
