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
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
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
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
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
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
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
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:
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
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
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
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
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
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
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?
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?
16 matches
Mail list logo
