Re: why does left join gives more results?
Your doing a left join which can increase the number of rows returned. This is then GROUP BYed and run through a HAVING. Is: posts.poster_id=users.id a one to one relationship? If it is not, then count(*) would be a larger number and pass the HAVING. This may not be your problem, but I suggest you have more than you realize. this is not a one-to-one as a user can make many comments but a comment belongs to one user only, so I guess the left join returns more than necesary. but I'm kind of stuck on that one :/ I suggest reading http://www.xaprb.com/blog/2006/04/26/sql-coding-standards/ I read it thanks but I still can't find a way to do it right, I mean I am following this pattern right no?: select column ... from table ... where criterion ... group by groupingclause ... having havingclause ... order by orderingclause ... Thanks in advance Pat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why does left join gives more results?
hey all, I have my query that counts posts per user: SELECT count(*) as counted, c.user_id FROM posts c group by c.user_id having counted1 order by counted DESC LIMIT 20 I wanted to add user login for each count so I did: SELECT count(*) as counted, u.login FROM posts c left join users u on posts.poster_id=u.id group by c.user_id having counted1 order by counted DESC LIMIT 20 but now I get more results. Any idea what I'm doing wrong? Thanks in advance Pat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ordering my regex
Hi all, I'm doing a select * from comments where c.content REGEXP 'http://[^i].*' and I would like to sort the urls found by repetition of the same urls. As an example if I get 3 records with http://google.com url in the content and two with http://mysql.com I would get the first the 3 comments with google.com and then the 2 with mysql.com. Any idea how to do that? Thanks in advance. Pat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to select total votes for each comment?
Hey all, I have comments(id,content) and votes(comment_id,vote). vote is a tinyint. I would like to select total votes for each comment, I tried: select content, sum(v.votes) from comments c left join votes v on c.id=v.comment_id but it only returns first result obviously, any idea how I could do this? Thanks in advance, Pat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
can I optimize this query?
Hey all, I have 2 tables: Profiles(id). Relationships(id,friend_id,befriender_id). friend_id and befriender_id represent profiles ids. I want to find all the profiles that are neither friends neither befrienders with a given profile. this is the query I use with profile id=1: select * from profiles where profiles.id not in (SELECT profiles.id FROM profiles INNER JOIN relationships ON profiles.id = relationships.befriender_id WHERE (relationships.friend_id = 1 )) and profiles.id not in (SELECT profiles.id FROM profiles INNER JOIN relationships ON profiles.id = relationships.friend_id WHERE (relationships.befriender_id = 1 )); is there a better, faster way to do so? thanx in advance Pat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to match all words
Hey all, I have a table 'clients' like this: id int(5), name varchar(55), address varchar(55) I would like to select all the records that have '%x%' and '%y%' but '%x%' can be in name and '%y%' can be in address. Also in my query there are generally more words to match (x,y,z,t etc) and I can't use full text search. Any what's the best way to 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]
database schema migration
hey all, I have two tables like that: artists(id,name) albums(id,artist_id,album_name) and I need to transfer the data of this database to three tables that look like this: artists(id,name) albums(id,name) artists_albums(album_id,artist_id) any idea what's the fastest query to 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]
can I recover a database from db files?
Hey all, I host my app on a friend server who make backup every night, well yesterday he installed another distro so I asked him for my db backup and it turns out the only backup he did was the whole hard drive. So he just sent me a tarball of my database directory containing: ads_categories.MYD,ads_categories.MYI,ads.frm,ads.MYD,ads.MYI,categories.frm,categories.MYD,categories.MYI,db.opt,regions.frm,regions.MYD,regions.MYI. I tried to create a database called mydb on my computer (debian etch) and just copied all the files to the place /var/lib/mysql/mydb/ then when I try to do a select * from categories, I get: ERROR 1017 (HY000): Can't find file: './mydb/categories.frm' (errno: 13) is there any way to get my db back? thanx in advance Pat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need help on before insert trigger
I would like to prohibit the value 'xxx' on my column title, and if it does contain the value I would like to create an exception by assigning 'xxx' to the primary key id which is int(5). This is what I do but I get an error on its creation so I guess it's not the right way: CREATE TRIGGER testref BEFORE INSERT ON bookmarks FOR EACH ROW BEGIN if NEW.title like '%xxx%' set NEW.id='xxx'; END; the error: server version for the right syntax to use near ': set NEW.id='xxx' at line 4 any idea how to do that? 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 on before insert trigger
I meant the error is: mysql CREATE TRIGGER testref BEFORE INSERT ON bookmarks - FOR EACH ROW - BEGIN - IF NEW.title LIKE '%xxx%' THEN - SET NEW.id ='xxx'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET NEW.id ='xxx'' at line 5 mysql END IF; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END IF' at line 1 mysql END; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help on before insert trigger
thanx it works the trigger is created successfully but it has no effect. here it is: delimiter // create trigger testref before insert on bookmarks for each row begin declare dummy char(2); if new.title like '%xxx%' then set new.id='xxx'; end if; end; //create trigger testref before insert on bookmarks - for each row - begin - declare dummy char(2); - if new.title like '%xxx%' - then - set dummy = 'xxx'; - end if; - end; - // then: insert into bookmarks values (1, x); Query OK, 1 row affected, 1 warning (0.00 sec) the row is created with a warning. I would like to prevent it from being created. I would like the insert to be canceled if the value is equal to xxx. any idea how to cancel the insert? 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 on before insert trigger
On 10/7/06, Patrick Aljord [EMAIL PROTECTED] wrote: thanx it works the trigger is created successfully but it has no effect. here it is: delimiter // create trigger testref before insert on bookmarks for each row begin if new.title like '%xxx%' then set new.id='xxx'; end if; end; //create trigger testref before insert on bookmarks - for each row - begin - declare dummy char(2); - if new.title like '%xxx%' - then - set dummy = 'xxx'; - end if; - end; - // (those are the two different triggers I tried) -- 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, 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
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]
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]
need help to delete duplicates
hey all, I have a table mytable that looks like this: id tinyint primary key auto_increment row1 varchar 150 row2 varchar 150 I would like to remove all duplicates, which means that if n records have the same row1 and row2, keep only one record and remove the duplicates. Any idea how to do this? thanks in advance Pat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
