r937 wrote: >> 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. > > if there are only these two conditions, then the easiest solution is a > LEFT OUTER JOIN > > select yay.domain > from servicedata as yay > left outer > join servicedata as nay > on nay.domain = yay.domain > and nay.prefname = 'bar' > where yay.prefname = 'foo' > and yay.prefvalue = 1 > and nay.domain is null
Rudy, you're a genius! Thanks for that, greatly appreciated. Still confuses me, I'd expect the "nay.prefname = 'bar'" to cause ones without a row with prefname = 'bar' to not be included in the results - I understand that it's to do with the left outer join meaning that unmatching rows on the right side of the row still get returned (I think). However, it works a treat, so all good :) /me bows to Rudy's SQL prowess :) -- 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: wdvltalk@lists.wdvl.com 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.