Jump to content


Photo

[SOLVED] JOIN questions


  • Please log in to reply
2 replies to this topic

#1 n8w

n8w
  • Members
  • PipPipPip
  • Advanced Member
  • 123 posts

Posted 27 February 2006 - 02:28 AM

all my joins work correctly except for when I try to do a join with table e ..

table e only currently only has two rows with this information in it

(ent_date, user_id, illustrator_id, status)
    
2006-02-26 20:43:42      1699      2154      1
2006-02-26 20:45:59     1699     2151     1

So I think that is probably part of the problem.

What type of join do I need to do if I want to join information .. but if it doesn't have anything for that user_id .. it will still show all the other information .. just won't have anything for the table I was trying to join?


this is what it looks like if I leave out table e
[a href=\"http://www.illustrationmundo.com/illustrators.php\" target=\"_blank\"]http://www.illustrationmundo.com/illustrators2.php[/a]
NOTICE: It leaves out all the score and voting informtion .. and illustrator_id

this is what I get if I include table e
[a href=\"http://www.illustrationmundo.com/illustrators2.php\" target=\"_blank\"]http://www.illustrationmundo.com/illustrators.php[/a]

Thanks
n8w





SELECT a.*, b.avg, b.total_votes, c.clicks, d.*,e.* 
FROM illustrators_table a 
LEFT JOIN users d ON a.user_id = d.user_id LEFT JOIN (SELECT user_id, AVG(score) as avg, COUNT(*) as total_votes FROM score_table GROUP BY user_id) b ON a.user_id=b.user_id 
LEFT JOIN (SELECT user_id, COUNT(*) as clicks FROM external_url WHERE str_date > curdate() - INTERVAL 14 DAY GROUP BY user_id) c ON a.user_id=c.user_id 
LEFT JOIN (SELECT user_id, COUNT(*) as total_favorites FROM favorites GROUP BY user_id) e ON a.user_id=e.user_id WHERE visible ="t" 
&& s_verified ="t" GROUP BY c.user_id ORDER BY a.user_id DESC LIMIT 0,9


#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 27 February 2006 - 06:56 AM

What error is it that you're getting? Or what is the unexpected output?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 n8w

n8w
  • Members
  • PipPipPip
  • Advanced Member
  • 123 posts

Posted 27 February 2006 - 07:01 AM

[!--quoteo(post=349761:date=Feb 27 2006, 03:56 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 27 2006, 03:56 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
What error is it that you're getting? Or what is the unexpected output?
[/quote]

hey it's resoloved .. I had another error .. that wasn't related .. so it was messing up my join .. but everthing is now work great! Thanks for the help!

n8w





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users