I suck at MySQL JOINS, can someone gimme a hand?

[Cloudrunner]

How would I compress these four queries into one?

PHP Code:

$nav_new_users_today = $vbulletin->db->query_first("SELECT
    COUNT(*) AS count
    FROM ".TABLE_PREFIX."user
    WHERE joindate >= '".$nav_starttime."'");
$nav_users = $vbulletin->db->query_first("SELECT
    COUNT(*) AS count
    FROM ".TABLE_PREFIX."user
    WHERE lastactivity >= '".$nav_starttime."'");
$nav_new_threads = $vbulletin->db->query_first("SELECT
    COUNT(*) AS count
    FROM ".TABLE_PREFIX."thread
    WHERE dateline >= '".$vbulletin->userinfo['lastvisit']."'");
$nav_new_posts = $vbulletin->db->query_first("SELECT
    COUNT(*) AS count
    FROM ".TABLE_PREFIX."post
    WHERE dateline >= '".$vbulletin->userinfo['lastvisit']."'"); 


I came up with a LEFT JOIN scenario, but alas, due to my lack of knowledge on this, it failed...

PHP Code:

$nav_query = $vbulletin->db->query_first("SELECT 
    COUNT(new_user.*) AS new_users_today, 
    COUNT(thread.*) AS new_threads, 
    COUNT(post.*) AS new_posts, 
    COUNT(user.*) AS users 
    FROM ".TABLE_PREFIX."user AS new_user 
    LEFT JOIN ".TABLE_PREFIX."thread AS thread ON(thread.dateline >= '".$vbulletin->userinfo['lastvisit']."') 
    LEFT JOIN ".TABLE_PREFIX."post AS post ON(post.dateline >= '".$vbulletin->userinfo['lastvisit']."') 
    LEFT JOIN ".TABLE_PREFIX."user AS user ON(user.lastactivity >= '".$nav_starttime."') 
    WHERE new_user.joindate >= '".$nav_starttime."'"); 


Any and all help on this would be greatly appreciated...I'm just straight up stuck, I've been through all of my reference materials, but just cannot figure out how to do this...

Thank you in advance for the assistance!

)O( Cloudrunner )O(

[filburt1]

Can you explain in English what specifically you are you trying to do (i.e., what data you are trying to retrieve)?

[Cloudrunner]

Originally Posted by filburt1

Can you explain in English what specifically you are you trying to do (i.e., what data you are trying to retrieve)?

Oh that part's easy LOL

I'm looking for the total count of new users registered today, the total count of users visited, the total count of new threads since a users last visit, and the total count of new posts since a user's last visit all in one shot.

I can do that easily with the first four queries, but I want to squish those four queries into one to cus everyone and their dog here gripes about multiple queries in a hack, so I figured if I could turn those four queries into one, no one could gripe

[Marco van Herwaarden]

You can not combine counts from different types of data into 1 query i think.

[deathemperor]

I dont think this is right:

PHP Code:

ON(thread.dateline >= '".$vbulletin->userinfo['lastvisit']."') 


ON() is used for the connection between 2 tables, not for condition IIRC.

I learnt SQL half a year ago and almost forgot complex parts +_+