Re: need help for my jointure
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
Re: need help for my jointure
--- 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; I would like to select id, conf from confs where ip!='some val'; how can I do this? thanx in advance Pat What is the question you want answered? What you wrote can answer some questions but that may not be the actual query you are trying to write. Please describe in words what you are looking for and I (or someone else on the list) will be able to respond with an appropriate query. Shawn Green __ 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]
Re: need help for my jointure
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help for my jointure
--- 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]
need help for my jointure
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; I would like to select id, conf from confs where ip!='some val'; how can I do this? thanx in advance Pat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help for my jointure
First of all, I'm going to guess that English is not your first language and tell you that jointure is not the word normally to describe the process of combining two tables in a database: the word you want is joining. Second, there are many kinds of joins and you haven't specified which kind you want to do. If you look in the MySQL manual, you will see that there are cross joins, inner joins, straight joins, natural joins, left joins, right joins, etc. You need to figure out which kind of join you want because your decision will affect the way you need to write your SQL. Third, the manual gives some information and examples on how to do joins. You haven't specified which version of MySQL you are using but if it is Version 5.0, the topic you want is http://dev.mysql.com/doc/refman/5.0/en/join.html. If you are using a different version, you can find the various editions on this page http://dev.mysql.com/doc/. Fourth, the manual does _not_ do a very good job of explaining the differences between the types of joins. This has been a known deficiency for some time and I am disappointed that this has (apparently) not been addressed yet. I wish I could suggest a good place to see a clear description of how the join types differ but I can't. Maybe someone else here has seen a decent tutorial on the differences between the types of joins However, if you plan to do an inner join, which is the kind most people do most of the time, your syntax will look like this: select id, conf from confs as c inner join conf_id as i on c.id = i.id where id != '101.33.55.123' If you need to do a different kind of join, please specify which kind you want to do and perhaps someone here can suggest the right syntax. -- Rhino - Original Message - From: Patrick Aljord [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 25, 2006 8:48 PM Subject: need help for my jointure 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; I would like to select id, conf from confs where ip!='some val'; how can I do this? thanx in advance Pat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.6/323 - Release Date: 24/04/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.6/323 - Release Date: 24/04/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
