sql - Show COUNT(*) column for value 0 in multiple rows -


i have table content

content(id, contenttext, contentdate, iduser → user, contenttype)

and table votequestion

votequestion(iduser → user, idquestion → content, isup)

i want select, instance, first 30 recent questions. 

select *  "content"  "type" = 'question' order "contentdate" limit 30

however, want concatenate columns information related question, don't need query database again each question returned.

for instance, want count votes each question, , returned in same row.

example:

| id | contenttext | contentdate | iduser | contenttype | votes | ----------------------------------------------------------------- | 2  |   'abc'     | '2013-03-25'|  192   |  'question' |  10   |

i tried following query:

with question (select *  "content"  "type" = 'question' order "contentdate" limit 30 )  select count("votequestion"."iduser")  "votequestion", question "idquestion" = question."id" group question."id";

but doesn't return questions number of votes = 0 (only 16 questions returned instead of 30). how solve it?

then, how can concenate both tables? , how can subtract votes isup true , isup false in query?

you can subselect vote count:

select content.* ,  (   select count(*)    votequestion   idquestion = content.id ) votes content type = 'question' order contentdate limit 30

Comments

Post a Comment

Popular posts from this blog

apache - Remove .php and add trailing slash in url using htaccess not loading css -

i have add following code htaccess file removing .php extension , add trailing slash urls. worked fine reduced page loading speed , css code not loading @ all. pages ugly. please help rewriteengine on rewritebase / ## hide .php extension snippet # externally redirect /dir/foo.php /dir/foo rewritecond %{the_request} ^[a-z]{3,}\s([^.]+)\.php [nc] rewriterule ^ %1/ [r,l] # add trailing slash rewritecond %{request_filename} !-f rewritecond %{request_uri} !/$ rewriterule . %{request_uri}/ [l,r=301] # internally forward /dir/foo /dir/foo.php rewritecond %{request_filename} !-d rewritecond %{request_filename}.php -f rewriterule ^(.*?)/?$ $1.php [l] we can rewrite urls using .htaccess files. before writing in .htaccess files need verify kind of server using. can check server adding a <?php phpinfo(); ?> in php page. ensure mod_rewrite module enabled in apache server. can identified checking phpinfo included page. common methods used url rewriting follows ...
Read more

javascript - jQuery show full size image on click -

pretty self explanatory not workking . i want display full image when user click on image... yes know there tons of plugins but i want keep simple. any idea ? css : .full { display: none; width: 500px; height: 500px; margin-left: 100px; margin-top: -300px; position: fixed; background-color: #f00; z-index: 6; } .thumbnail { width: 50px; height: 50px; } html: <img class="thumbnail" src="http://4.bp.blogspot.com/-uhbdp2esxng/txneptvi92i/aaaaaaaaaky/upfqis7zto0/s1600/rihanna-wallpaper.jpg"> jquery: $(document).ready(function() { $('.thumbnail').on('click', function() { var img = $('<img />',{src:this.src,'class':'full'}); $('.full').html(img).show(); console.log('done!'); }); }); try looking how jquery works. $(document).ready(function() { $('.thumbnail').on('click', function(...
Read more