Query with clauses from two tables

[El Chupacodra]

I decided to put a statusbar on a site where two things will show; the users online (which works fine, Pikachu got that working somewhere else on the site earlier) and any users the logged in user has added as favorites.

No matter how I try I haven't gotten it to work.

 

Most times I get all the users currently online once OR I get every user on a given user's favorite list repeated as many times as there are users currently online.

 

The tables I query are called user_favorites with columns user and favorited and user_online with columns user and last_activity.

What I need is every user who has last_activity > DATESUB(NOW(), INTERVAL 15 MINUTE) and who is also on the logged in user's (stored in variable $user) favorite list.

 

These queries all failed:

$query1 = "SELECT user_favorites.favorited FROM user_favorites WHERE (SELECT FROM user_online WHERE user_online.user='user_favorites.favorited' AND user_online.last_activity > DATE_SUB(NOW(), INTERVAL 15 MINUTE))";
$query1 = "SELECT user_favorites.favorited, user_online.last_activity FROM user_favorites JOIN user_online ON user_online.user=user_favorites.user  WHERE user_favorites.user='$user' AND user_online.last_activity > DATE_SUB(NOW(), INTERVAL 40 MINUTE)"; 
$query1 = "SELECT user_favorites.favorited FROM user_online, user_favorites WHERE user_favorites.user='$user' AND user_online.last_activity > DATE_SUB(NOW(), INTERVAL 40 MINUTE)";
$query1 = "SELECT user FROM user_online WHERE last_activity > DATE_SUB(NOW(), INTERVAL 15 MINUTE) AND (SELECT favorited FROM user_favorites WHERE user = '$user')";
$query1 = "SELECT user_favorites.favorited FROM user_favorites, user_online WHERE user_favorites.user='$user' AND (user_online.last_activity > DATE_SUB(NOW(), INTERVAL 15 MINUTE))";

 

[kickstart]

Hi

 

What did they fail with?

 

What is the table layouts? What format is the column user_online.last_activity in?

 

All the best

 

Keith

[El Chupacodra]

Thank you for looking into this.

The format for last_activity is DATETIME (or TIMESTAMP) and that should be ok.

The parallell query I have works great - it queries the last_activity for all users online in the last 15 minutes.

The queries don't have a syntax fail, they just produce other results than the one I want.

Instead of listing the online favorite users of any logged in user it shows me all the users on the favorite list.

Probably because it looks through the favorites list and then checks if the online user is online.

 

 

[kickstart]

Hi

 

Not quite sure how your tables join up, but try something like this:-

 

$query1 = "SELECT user_favorites.favorited 
FROM user_favorites
INNER JOIN user_online 
ON user_favorites.favorited = user_online.user
WHERE user_favorites.user='$user' 
AND user_online.last_activity > DATE_SUB(NOW(), INTERVAL 15 MINUTE)"; 

 

I presume what you want is to get the all the favourites of the current user from user_favourites and join that to user_online to find which favourites have been online in the last 15 minutes.

 

Going through your other attempts to explain what seems to be happening:-

 

$query1 = "SELECT user_favorites.favorited 
FROM user_favorites 
WHERE (SELECT FROM user_online WHERE user_online.user='user_favorites.favorited' AND user_online.last_activity > DATE_SUB(NOW(), INTERVAL 15 MINUTE))";

 

Not sure what this will do I would expect a syntax error. The subselect isn't bringing anything back, and even if it does the WHERE clause doesn't compare it with anything.

 

$query1 = "SELECT user_favorites.favorited, user_online.last_activity 
FROM user_favorites JOIN user_online ON user_online.user=user_favorites.user  
WHERE user_favorites.user='$user' AND user_online.last_activity > DATE_SUB(NOW(), INTERVAL 40 MINUTE)"; 

 

This is doing the join but on the current users field. Effectively bring back just the current user.

 

$query1 = "SELECT user_favorites.favorited 
FROM user_online, user_favorites 
WHERE user_favorites.user='$user' AND user_online.last_activity > DATE_SUB(NOW(), INTERVAL 40 MINUTE)";

 

No fields specified to do the join, hence it will do a cross table join and bring back a hell of a lot of rows.

 

$query1 = "SELECT user 
FROM user_online 
WHERE last_activity > DATE_SUB(NOW(), INTERVAL 15 MINUTE) AND (SELECT favorited FROM user_favorites WHERE user = '$user')";

 

Doesn't actually check against anything from the subselect.

 

$query1 = "SELECT user_favorites.favorited 
FROM user_favorites, user_online 
WHERE user_favorites.user='$user' 
AND (user_online.last_activity > DATE_SUB(NOW(), INTERVAL 15 MINUTE))"; 

 

This is the same as your 3rd attempt.

 

All the best

 

Keith

[El Chupacodra]

That looks brilliant Keith.

I'm out right now but when I get home in 6-7 hours I will get right to it.

Thanks - you made my weekend if it works.

And if it didn't it's still good info.

[El Chupacodra]

I tried your query Keith.

Keeping all fingers crossed and with a prayer to all kinds of deities.

You solved it!

Thanks - this has been a huge headache and now I learned a little about join.

I really appreciate it.

[kickstart]

Hi

 

Glad it helped

 

It is a nice break from trying to beat my head against SQL Server and ASP .NET  .

 

All the best

 

Keith

[El Chupacodra]

I'm even happier.

Hope I can do something to help you back someday.