n8w

[!--quoteo(post=351171:date=Mar 2 2006, 09:43 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 2 2006, 09:43 PM) [snapback]351171[/snapback][/div][div class=\'quotemain\'][!--quotec--] I've never needed any more than the online documentation, it's pretty extensive. I think the best way to internalize joins and subqueries is to practice a lot. You might try finding an old used textbook that has sets of problems to solve. Many of them will be Oracle based but it doesn't really matter all that much. Or you could hang around here and try to help people. :) [/quote] good idea .. I need to participate in the forums and help more .. good idea

removed

Hey can any one recommend a good mysql book? .. I want to learn about complex queries with all types of joins .. etc Thanks n8w

[b]thats it!!!!!!!!!!!!!!!!!!!!!![/b][size=5] awesome .. thanks so so much .. I can't express my gratitude... thanks!

ahhh .. thanks .. that's all I needed to hear .. should I just write another query in this page .. or should I just create a new my_favorites.php page? The count is working corectly .. I am trying to provide a way for people to find illustrators they like ..so the count reflects how many people added them .. not the other way around. Thanks for your help!!!

hey Wickning1 thanks for responding .. you are always so helpful The result I am looking for is .. it to return all the the records that the user_id 1699 marked as their favorites .. which would be illustrator_id 846,848,850,etc I tried the statement from your previous post and this is what it returned [a href=\"http://www.illustrationmundo.com/illustrators3.php?favorites=1699\" target=\"_blank\"]http://www.illustrationmundo.com/illustrat...?favorites=1699[/a] just the user 1699 Here are the correct results .. but can't seem to get them when I am doing a join table structure (ent_date,user_id, illustrator_id,status) ent_date= date entered user_id= the person that is dong the action of adding another person as one of their favorites illustrator_id= the person they added .. the artist they liked status=0 is delete from table .. 1 is add .. don't really need this column [code]                    Edit       Delete  2006-02-26 22:00:31      1699      846      1     Edit     Delete     2006-02-26 23:09:19     1699     848     1     Edit     Delete     2006-02-26 23:09:40     1699     850     1     Edit     Delete     2006-02-26 23:30:49     1699     859     1     Edit     Delete     2006-02-27 01:41:37     1699     870     1     Edit     Delete     2006-02-27 09:42:33     1699     896     1     Edit     Delete     2006-02-27 00:24:47     1699     920     1     Edit     Delete     2006-02-27 09:51:57     1699     926     1     Edit     Delete     2006-02-26 22:30:10     1699     927     1     Edit     Delete     2006-02-27 03:22:03     1699     974     1     Edit     Delete     2006-02-27 00:25:49     1699     981     1     Edit     Delete     2006-02-27 00:26:14     1699     1043     1     Edit     Delete     2006-02-27 03:19:02     1699     1067     1     Edit     Delete     2006-02-27 10:20:26     1699     1082     1     Edit     Delete     2006-02-27 01:51:55     1699     1144     1     Edit     Delete     2006-02-27 01:51:24     1699     1164     1     Edit     Delete     2006-02-27 03:18:33     1699     1179     1     Edit     Delete     2006-02-27 01:40:27     1699     1210     1     Edit     Delete     2006-02-27 09:52:38     1699     1256     1     Edit     Delete     2006-02-27 01:20:47     1699     1384     1     Edit     Delete     2006-02-27 02:24:00     1699     1471     1     Edit     Delete     2006-02-26 23:18:11     1699     1539     1     Edit     Delete     2006-02-27 01:20:47     1699     1587     1     Edit     Delete     2006-02-27 03:18:02     1699     1679     1     Edit     Delete     2006-02-26 23:08:34     1699     1984     1 [/code]

[!--sizeo:4--][span style=\"font-size:14pt;line-height:100%\"][!--/sizeo--][b]I am trying to return all the rows with the user_id 1699 when I do it without the join it returns the correct number of 24[/b][!--sizec--][/span][!--/sizec--] SQL STATEMENT [code]SELECT * FROM favorites WHERE `user_id` = "1699" LIMIT 0 , 30[/code] Results [code]       Edit        Delete       2006-02-26 22:00:31      1699      846      1     Edit     Delete     2006-02-26 23:09:19     1699     848     1     Edit     Delete     2006-02-26 23:09:40     1699     850     1     Edit     Delete     2006-02-26 23:30:49     1699     859     1     Edit     Delete     2006-02-27 01:41:37     1699     870     1     Edit     Delete     2006-02-27 09:42:33     1699     896     1     Edit     Delete     2006-02-27 00:24:47     1699     920     1     Edit     Delete     2006-02-27 09:51:57     1699     926     1     Edit     Delete     2006-02-26 22:30:10     1699     927     1     Edit     Delete     2006-02-27 03:22:03     1699     974     1     Edit     Delete     2006-02-27 00:25:49     1699     981     1     Edit     Delete     2006-02-27 00:26:14     1699     1043     1     Edit     Delete     2006-02-27 03:19:02     1699     1067     1     Edit     Delete     2006-02-27 01:51:55     1699     1144     1     Edit     Delete     2006-02-27 01:51:24     1699     1164     1     Edit     Delete     2006-02-27 03:18:33     1699     1179     1     Edit     Delete     2006-02-27 01:40:27     1699     1210     1     Edit     Delete     2006-02-27 09:52:38     1699     1256     1     Edit     Delete     2006-02-27 01:20:47     1699     1384     1     Edit     Delete     2006-02-27 02:24:00     1699     1471     1     Edit     Delete     2006-02-26 23:18:11     1699     1539     1     Edit     Delete     2006-02-27 01:20:47     1699     1587     1     Edit     Delete     2006-02-27 03:18:02     1699     1679     1     Edit     Delete     2006-02-26 23:08:34     1699     1984     1[/code] [!--sizeo:4--][span style=\"font-size:14pt;line-height:100%\"][!--/sizeo--][b]But when I do it with a join .. it leaves some of them out for some reason .. it only returns 17 rows .. can you see a problem with my SQL statement? [/b][!--sizec--][/span][!--/sizec--] [code] Showing rows 0 - 16 (17 total, Query took 0.9221 sec) SQL query: SELECT a . * , b.avg, b.total_votes, c.clicks, d . * , e.total_favorites 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 illustrator_id, user_id, COUNT( illustrator_id ) AS total_favorites FROM favorites GROUP BY illustrator_id )e ON a.user_id = e.illustrator_id WHERE visible = "t" && s_verified = "t" && e.user_id = "1699" GROUP BY c.user_id ORDER BY a.user_id DESC LIMIT 0 , 200[/code] Results [code]removed  [/code]

[!--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

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]

Hey Fenway .. thanks for responding. I think I will try the Iframe approach.

I am creating a [b]"my favorites" [/b]feature on [a href=\"http://www.illustrationmundo.com/illustrators.php?str_date=rb\" target=\"_blank\"]http://www.illustrationmundo.com/illustrat...php?str_date=rb[/a] where when a user is logged in they can add the illustrators they like to their favorites list The favorites table will have the following fields: user_id - login id illustrator_id - the id of the illustrator they want to save as one fo their favorites date - current date So I have two questions [!--sizeo:2--][span style=\"font-size:10pt;line-height:100%\"][!--/sizeo--][b]1. When a user clicks "add favorite" should I have this as an ajax function that processes this request and updates the page without changing the page (if so how do I do this .. or go about learning how to do this).. if not .. I assume I just have a php page that will process this info and return the user back to the page they were previously on.[/b][!--sizec--][/span][!--/sizec--] [!--sizeo:2--][span style=\"font-size:10pt;line-height:100%\"][!--/sizeo--][b]2. What is the best way to write the sql statement to insert/update this in the database?[/b][!--sizec--][/span][!--/sizec--] In the past I have wrote multiple SQL statements .. with a series of "if" statements .. but I am sure there is a more effecient way to do this?? What I have been doing is ... if the table has user_id and artist_id as the primary key then write a sql statement that UPDATES the table if not write a sql statement thatn inserts this info is there an easy way where you can just write .. insert this info .. and if it exists it overwrites it .. or do you have to do it the way I have been doing it? Thanks so much n8w Should I write a few sql statements

thank you sooooooooooooo much .. this works perfectly now and much faster .. you just opened the door to a whole new world .. thanks!

sooooooooooo cool .. I didn't even know you could run a select within a join .. the performance is defiinitely better .. but it still looks like it's selecting all the clicks do you see anything strange in my sql statement [a href=\"http://www.illustrationmundo.com/illustrators3.php?str_date=bc\" target=\"_blank\"]http://www.illustrationmundo.com/illustrat...php?str_date=bc[/a]

[!--quoteo(post=348509:date=Feb 22 2006, 07:13 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 22 2006, 07:13 PM) [snapback]348509[/snapback][/div][div class=\'quotemain\'][!--quotec--] Intersting approach... but doing a CAST() for each potential record is also a perfomance hit. The real question is why the date is being stored as anything but a date to begin with. [/quote] the date is stored as datetime .. the name str_date is misleading .. I must admit I don't get the data I want .. but it doesnt hang when I do this note the "<" no hang && str_date < DATE_SUB(curdate(), INTERVAL 1 MONTH ) correct but hangs && str_date > DATE_SUB(curdate(), INTERVAL 1 MONTH ) [a href=\"http://www.illustrationmundo.com/illustrators3.php?str_date=bc\" target=\"_blank\"]http://www.illustrationmundo.com/illustrat...php?str_date=bc[/a]

hey thanks wickning1 .. my naming conventions are bad .. so it was kind of misleading .. the date type is actually stored as datetime so no need to convert .. is there anyway to put the condition up on this part count(distinct concat( b.user_id, b.judge)) AS total_votes where str_date >DATE_SUB(curdate(), INTERVAL 1 MONTH ) thanks n8w

[!--quoteo(post=348452:date=Feb 22 2006, 04:53 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 22 2006, 04:53 PM) [snapback]348452[/snapback][/div][div class=\'quotemain\'][!--quotec--] That makes it look like you're putting it after the GROUP BY -- could you post the final, concatenated query? [/quote] sure [code] SELECT a.*, round(AVG(b.score),1) AS avg, count(distinct concat( b.user_id, b.judge)) AS total_votes, count(distinct concat( c.user_id, c.ip_address)) AS clicks, d.* FROM (illustrators_table a LEFT JOIN score_table b ON ( a.user_id = b.user_id ) LEFT JOIN external_url c ON ( a.user_id = c.user_id ) LEFT JOIN users d ON ( a.user_id = d.user_id )) WHERE visible ="t" && s_verified ="t" && str_date >DATE_SUB(curdate(), INTERVAL 1 MONTH ) GROUP BY c.user_id ORDER BY a.user_id DESC[/code]

I think my page is timing out cause I wrote the sql statment in a poor way .. everything worked fine until I wanted to put a date range on the clicks. .. basically want I want to do is get the "clicks" that are less than a month old .. count(distinct concat( c.user_id, c.ip_address)) AS clicks, is there a way to put the where statement as part of the count? because when I put it a the bottom .. I think it's doing a lot more work than I intend $sql .="&& str_date >DATE_SUB(curdate(), INTERVAL 1 MONTH )"; here is the live version [a href=\"http://www.illustrationmundo.com/illustrators2.php\" target=\"_blank\"]http://www.illustrationmundo.com/illustrators2.php[/a] and here is the code [code] $sql = '           SELECT a.*, round(AVG(b.score),1) AS avg, count(distinct concat( b.user_id,  b.judge)) AS total_votes, count(distinct concat( c.user_id,  c.ip_address)) AS clicks, d.* FROM (illustrators_table a LEFT JOIN score_table b ON ( a.user_id = b.user_id ) LEFT JOIN external_url c ON ( a.user_id = c.user_id ) LEFT JOIN users d ON ( a.user_id = d.user_id ))';     $sql = $sql." ".$q_visible_temp;     $sql = $sql." ".$q_s_verified_temp;     $sql = $sql." ".$q_i_search_temp;     $sql = $sql." ".$q_medium_temp;     $sql = $sql." ".$q_style_temp;     $sql = $sql." ".$q_category_temp;     $sql = $sql." ".$q_feature_temp;     $sql = $sql." ".$q_score_temp;     $sql .="&& str_date >DATE_SUB(curdate(), INTERVAL 1 MONTH )";     $sql= $sql. " GROUP BY c.user_id ";     $sql = $sql." ".$str_date_temp; [/code]

[!--quoteo(post=334045:date=Jan 6 2006, 02:07 PM:name=obsidian)--][div class=\'quotetop\']QUOTE(obsidian @ Jan 6 2006, 02:07 PM) 334045[/snapback][/div][div class=\'quotemain\'][!--quotec--] try this: [!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] a.illustrator_id, AVG(score) AS avg FROM illustrator_table a RIGHT JOIN score_table b ON a.illustrator_id = b.illustrator_id GROUP BY a.illustrator_id; [!--sql2--][/div][!--sql3--] THANKS SOOOOOOOOOOOOOOOOOO MUCH!!!!!! This is awesome ..sorry about the double posts .. I wasn't sure if this forum got much traffic so I posted it in the general .. thanks

How do I write a sql statement that will join the tables and get the average of each illustrator's score? I'm trying to create a rating system where users can rate multiple illustrators on one page and it shows the illustrator's existing score I need help writing a sql statement in order to get the score of each illustrator. I have two tables How do I write a sql statement that will join the tables and get the average of each illustrator's score? thanks!!!!!!