Query returns empty row

[EchoFool]

Hey,

 

 

I have a query which returns a row with no values in the fields, technically it should not find a row based on my current example data, but it still finds one. I have no idea how to solve this.... please can you help.

 

 

Here is my query:

 

 

<?php
   SELECT *,count(t3.userid) as Total 
   FROM user_settings t1
   INNER JOIN users t2
   ON  t1.userid=t2.UserID
   LEFT JOIN users_pictures t3
   ON t1.userid=t3.userID
   WHERE TIMESTAMPDIFF(DAY,LastAction,NOW()) < 34
   AND NOT EXISTS
             (
              SELECT id FROM users_blocked
              WHERE (user1='$myid' AND user2=t2.UserID)
              OR (user1=t2.UserID AND user2='$myid')
             )
   LIMIT 50
?>

 

I am wondering if my count() is causing it ? IF so how can i solve it ?

 

Thanks

[EchoFool]

bumpy

[fenway]

First, don't bump.

 

Second, you can't just tack on a count(*) like that -- it's meaningless.  You'll need another query -- don't be lazy.

[EchoFool]

I thought you could bump after 12 hours guess the rules changed since i last visited, and if i use a second query will that be efficient enough =/

 

Always thought making it in to one query is most efficient method when possible.

[fenway]

Do not bump topics without providing additional information.

 

The truth is, you can just count the rows that you get back -- there's no LIMIT clause -- so why the second query at all?

[EchoFool]

The left join is to see if the user has images uploaded to their profile.

 

Because the query is only to load users who have images of 1 or more. But some users can decide to load users without images, so i used a left join and just echo'd how many images the user has uploaded to their profile adjacent to their name like:

 

User 1 : 0 images.

User 2:  5 images.

 

etc.

 

But if it finds no users it comes up with a empty row showing 0 images. I tried to merge it into one query for efficiency which i do as much as possible.

[fenway]

I suppose you could use a derived table to get the photo counts for each such user.

[EchoFool]

Well i was always told to avoid derived tables due to performance issues?

[fenway]

Well i was always told to avoid derived tables due to performance issues?

Dependent subqueries -- avoid.  Derived tables are subject to the same performance if they're derived or not.

[EchoFool]

Okay thanks for the advice!