On 4/26/06, Shawn Green <[EMAIL PROTECTED]> wrote: > > > > --- Patrick Aljord <[EMAIL PROTECTED]> wrote: > > > On 4/26/06, Patrick Aljord <[EMAIL PROTECTED]> wrote: > > > I have a table confs like this: > > > id int 5 auto_increment primary key; > > > conf text; > > > > > > and another table conf_ip like this: > > > id int 5 auto_increment primary key; > > > conf_id int 5; ==>foreing key of confs > > > ip varchar 150; > > > > > ok, sorry all for not being specific enough. I figured a query which > > is half working like that: > > "select c.id, c.conf from confs as c inner join conf_ip as i on c.id > > = > > i.conf_id where i.ip!='someip" > > I would like the select to return all the c.id that are in conf and > > all that don't have a c.ip='someip'. The problem is that if there is > > a > > record that have the c.id=i.conf_id but an ip different than > > localhost, my query will still return the corresponding c.id and I > > don't want that. > > I tried something like that but couldn't get it to work: > > select c.id, c.conf from confs as c inner join conf_ip as i on c.id = > > i.conf_id where (select i.ip from conf_ip where ip='$ip')=0 > > > > hope yo usee what I mean > > > > Thanx in advance > > > > Pat > > > > Yes, I think I do. What you have is a table of 'conf' (whatever they > are) that can have multiple IP addresses (see table `conf_ip`). What I > think you want to know is which "confs" do not have a particular IP > address. > > There are several ways to write this query. One of the more > straightforward ways to write this is to use a temporary table > (depending on your version, this should work well written as a > subquery, too). > > CREATE TEMPORARY TABLE tmpMatches (key(conf_id) SELECT DISTINCT conf_id > FROM conf_ip > WHERE ip = 'some_ip_value'; > > SELECT c.id, c.conf > FROM confs c > LEFT JOIN tmpMatches m > ON m.conf_id = c.id > WHERE m.conf_id IS NULL; > > DROP TEMPORARY TABLE tmpMatches; > > > What we do is generate a list of all of the conf's that do have the > target IP and save that list into a temporary table. Then we run a > query that finds every conf EXCEPT those we just located in the first > step. Last we clean up after ourselves by getting rid of the temp > table. > > Make sense? >
Yes it makes sense, thanx a lot Shawn. As I'm using it my php code, I think I'd rather do it with a sub query. Here is how I do it: SELECT c.id, c.conf FROM confs c LEFT JOIN conf_ip i ON i.conf_id = c.id WHERE c.id NOT IN (SELECT DISTINCT conf_id FROM conf_ip WHERE ip = 'some_ip_value); is that correct? I can't test it right now thanx in advance Pat
