postgresql - SQL Outer Joins with count function -

i writing query gets me users. every user can have picture , every user can picture once.

there 3 tables: user picture like

so need user data count of picture , want in 1 query. used right join users don't have picture , left join users don't have likes. have infos need except likes. when count 1 count when there no likes. possible in 1 query because need order likes ?

here query.

select u.uid,        firstname,        lastname,        email,        zipcode,        city,        street,        count(*) likes   picture p        right join user u using(pid)        join using(uid) group  uid,           firstname,           lastname,           email,           zipcode,           city,           street;   

edit:

this query correct:

select u.uid, firstname, lastname, email, zipcode, city, street, count(l.pid) likes  user u left join l using(pid)  group uid,firstname,lastname,email,zipcode,city,street; 

the table structure nice, since looks may have issue join like written. @ first glance, appear you're counting number of times user has "liked" picture , not number of times his picture liked. in case, it's reasonable assume must user likes something, user id in like table refers person that's doing liking , not what's being liked.

i'll assume here like join table foreign keys user.uid , picture.pid. if not case, edit question more clear , i'll alter accordingly.

if you're trying users (plus other information them), it's more clear you're trying if start that, it's best if from clause user u , work there:

select   u.uid,   firstname,   lastname,   email,   zipcode,   city,   street,   count(*) 'likes' user u left join l on u.pid = l.pid group   u.uid,   firstname,   lastname,   email,   zipcode,   city,   street