postgres query help involving count

call from two tables where x appears > 1

[proper_bo]

I have the following two tables (simplified):

user

user_id ¦ gender ¦ active
--------+--------+-------
...1....¦...1....¦...1
...2....¦...1....¦...0
...3....¦...3....¦...1

user_picture

user_id ¦ picture_id ¦ accepted
--------+------------+---------
...1....¦.....1......¦....1
...1....¦.....2......¦....1
...1....¦.....3......¦....0
...2....¦.....4......¦....1
...3....¦.....5......¦....1
...3....¦.....6......¦....1

I would like to call all user_id's where the user is active and has more than one accepted picture in the user_picture table.

I am not very experienced with postgres and I just can't seem to get my head around this.

Thank you for your help.

[txbakers]

I might split this into two queries. One to get a list of actives with any pictures, then use those IDs to run a second query to get those with more than one picture.

1)SELECT user.user_id as idnum FROM user, user_picture WHERE user.user_id = user_picture.user_id and active = 'Y'

while!EOF{
2) SELECT count(user_id) as idcnt from user_picture where user_id='" + FirstQueryID + "' and count(user_id) > 1 GROUP BY user_id
}

Also, WHY did you pick those horrid table/field names? Underscores are a royal pain in the neck when you have these things. You could use "userid" or "uid", etc. and user_picture is a mouthful. A simple table of "picture" would work just well.

As you develop further in SQL you won't want to be dogged down in typing.

[proper_bo]

thanks for your help. I will go and try that out.

The site was inherited so the table and field names were not my choice. I realise they are ott.

[bcc1234]

select * from user where active=1 and user_id in(
select user_id from user_picture where accepted=1 group by user_id having count(*)>1
);

[proper_bo]

bcc1234 your a star.

what I wanted:

select user_id from user
where active=1
and user_id in(
select user_id from user_picture
where accepted=1
group by user_id
having count(*)>1
);

that is fantastic. Thanks.

[proper_bo]

A further questions with this;

If I then want an order and offset and limit do I place it after the brackets?

select user_id from user
where active=1
and user_id in(
select user_id from user_picture
where accepted=1
group by user_id
having count(*)>1
)
order by whatever
limit 100
offset 50;

Is that correct?

And further to this. Is it possible to order by the number of results.
So those with most images come top?

can you just do 'order by count(*)'?

[proper_bo]

I solved the above questions with trial and error.

I still need to know if you can order by count though.

'order by count(*)' doesn't seem to work. Is there a correct way to do it?

[zCat]

You can order by COUNT(*), no problem. Just use "ORDER BY 1", where "1" refers to the column in the SELECT part of the clause with COUNT(*) in it.

I think you could write your statement like this:

select count(*), u.user_id
 from user_picture up
join user u
on u.user_id=up.user_id
where accepted=1
group by user_id
having count(*)>1 
order by 1

[zCat]

txbakers:

Also, WHY did you pick those horrid table/field names? Underscores are a royal pain in the neck when you have these things. You could use "userid" or "uid", etc. and user_picture is a mouthful. A simple table of "picture" would work just well.

On the other hand, when a database has more than a few tables, underscores become very useful to make table names more readable. Quite often you end up with two tables, say "user" [1] and "picture", and a reference table to join the two, and there I would always use "user_picture" rather than "userpicture" or even worse, an abbreviation like "usrpic".

[1]

user

is actually a reserved word in PostgreSQL and is not a good idea to use as a table name

As you develop further in SQL you won't want to be dogged down in typing.

PostgreSQL has a very good command line client (psql) which has a useful tab completion facility (far better than MySQL's) which vastly reduces the amount of typing necessary.

[eeek]

user is actually a reserved word in PostgreSQL and is not a good idea to use as a table name

Or just quote all your names and not worry about it.