--- 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? Shawn Green Database Administrator __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
