unexpected SQL Query Results

[n8w]

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

[wickning1]

When you do it in the join, you are losing user_id information because you are grouping on illustrator_id. I am trying to discern what you want from this query. I'm guessing that you want to know how many people have selected the user 1699 as one of their favorite illustrators. Is that correct?

[code]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
    WHERE user_id="1699"
    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
    AND user_id="1699"
    GROUP BY user_id
) c ON a.user_id = c.user_id
LEFT JOIN (
    SELECT illustrator_id, COUNT(*) AS total_favorites
    FROM favorites
    WHERE illustrator_id="1699"
    GROUP BY illustrator_id
) e ON a.user_id = e.illustrator_id
WHERE visible = "t" && s_verified = "t" && a.user_id="1699"[/code]

[n8w]

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]

[wickning1]

Ok, so you just want a count of how many favorites they've picked? That's easy enough.

Now, if you're actually wanting to know WHICH illustrators are their favorites, you are better off doing a second query than trying to combine it into this one.

[code]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
    WHERE user_id="1699"
    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
    AND user_id="1699"
    GROUP BY user_id
) c ON a.user_id = c.user_id
LEFT JOIN (
    SELECT user_id, COUNT(*) AS total_favorites
    FROM favorites
    WHERE user_id="1699"
    GROUP BY user_id
) e ON a.user_id = e.user_id
WHERE visible = "t" && s_verified = "t" && a.user_id="1699"[/code]

[n8w]

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

[wickning1]

Ahh so you want all of user 1699's favorites to be printed on screen, with all of their info. I finally understand what you're after. Here's the fastest way I can think of:

[code]SELECT a.* , b.avg, b.total_votes, c.clicks, d.*
FROM illustrators_table a
INNER JOIN users d ON a.user_id = d.user_id
INNER JOIN (
    SELECT s.user_id, AVG(s.score) AS avg, COUNT(*) AS total_votes
    FROM score_table s
    INNER JOIN favorites e ON e.illustrator_id = s.user_id
    WHERE e.user_id = "1699"
    GROUP BY s.user_id
) b ON a.user_id = b.user_id
INNER JOIN (
    SELECT u.user_id, COUNT(*) AS clicks
    FROM external_url u
    INNER JOIN favorites e ON e.illustrator_id = u.user_id
    WHERE str_date > curdate() - INTERVAL 14 DAY
    AND e.user_id = "1699"
    GROUP BY u.user_id
) c ON a.user_id = c.user_id
WHERE visible = "t" && s_verified = "t"[/code]

[n8w]

[b]thats it!!!!!!!!!!!!!!!!!!!!!![/b][size=5]

awesome .. thanks so so much .. I can't express my gratitude... thanks!