Jump to content

Archived

This topic is now archived and is closed to further replies.

n8w

[SOLVED] JOIN questions

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]

Share this post


Link to post
Share on other sites
What error is it that you're getting? Or what is the unexpected output?

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

×

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.