Jump to content

[SOLVED] JOIN questions


n8w

Recommended Posts

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)
[code]    
2006-02-26 20:43:42      1699      2154      1
2006-02-26 20:45:59     1699     2151     1
[/code]

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





[code]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[/code]
Link to comment
Share on other sites

[!--quoteo(post=349761:date=Feb 27 2006, 03:56 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 27 2006, 03:56 AM) [snapback]349761[/snapback][/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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.