[SOLVED] LEFT Joined tables missing records

[rubing]

OK, this is really pissing me off!!!!! 

 

 

I am executing this SQL:

 

SELECT ad.ad_id, ad.owner_api, ad.file, ad_log.ad_played, COUNT( ad_played )
FROM ad
LEFT JOIN ad_log ON ad.ad_id = ad_log.ad_played
GROUP BY ad_played

 

here are my 2 tables (sorry had trouble posting them with correct aligning)

 

TABLE ad:

 

ad_id owner_api file plays_purchased times_played

1 0 cmn.mp3 0 0

2 1 dumb.mp3 8 4

3 1 stupid.mp3 0 0

4 1 crazy.mp3 10000 0

5 5 wild.mp3 1787 333

6 5 justin.mp3 22 22

 

TABLE ad_log:

 

ipaddress sess_id ad_played date_played

11 1 2 2008-08-31 11:35:44

11 1 2 2008-08-31 11:35:44

55 5 3 2008-08-31 11:36:20

55 5 2 2008-08-31 11:35:44

55 5 5 2008-08-31 11:35:44

66 6 5 2008-08-31 11:35:44

66 6 5 2008-08-31 11:35:44

66 6 6 2008-08-31 11:35:44

77 7 5 2008-08-31 11:35:44

77 7 3 2008-08-31 11:35:44

11 1 5 2008-08-31 12:09:11

11 1 5 2008-08-31 12:09:11

 

 

And here is the result of my query:

 

ad_id owner_api file ad_played COUNT(ad_played)

1 0 cmn.mp3 NULL 0

2 1 dumb.mp3 2 3

3 1 stupid.mp3 3 2

5 5 wild.mp3 5 6

6 5 justin.mp3 6 1

 

 

 

So, my problem is this:  Why the hell isn't ad_id 4 showing up in my results table????  ARGHHHHHHH!!!!!  >

[cooldude832]

Its really how you are doing your query that is the issue.

 

What are you trying to return? 

The issue arrives at the selecting of ad_log.ad_played

You don't need this item because it is equal to ad.ad_id becaues of your join criteria.

 

try

SELECT 
ad.ad_id,
ad.owner_api, 
ad.file,
COUNT( ad_played ) as play_count
FROM `ad`
LEFT JOIN `ad_log` ON (ad.ad_id = ad_log.ad_played)
GROUP BY ad.ad_id

[rubing]

OK, that's great!!! it works now, which means i can ask the second part of my question (i swear there are only 2 parts!!!!)

 

I have a third table called ad_limit, which limits the # of plays an ad will get per session. here it is:

 

Table ad_limit:

 

advert_id plays_per_session

1             3

2             3

3             4

4             5      

6             2   

 

I want to combine all three tables in such a way as to show me the least played ad for a particular session id.  (of course so I can serve it to them!    ) 

 

So, if  i execute the following SQL: 

SELECT ad.ad_id, ad.owner_api, ad.file,sess_id,plays_per_session,count(*) AS played
FROM (ad
LEFT JOIN ad_log ON ad.ad_id = ad_log.ad_played)
LEFT JOIN ad_limit ON ad.ad_id=ad_limit.advert_id
WHERE owner_api=1
Group BY sess_id,ad_id 

 

I get a nice little list of the number of times an ad's been played for any session that's happened to play them:

 

ad_id owner_api file            sess_id plays_per_session played

4         1            crazy.mp3    NULL       5                         1

2             1         dumb.mp3    1               3                  2

2         1         dumb.mp3    5               3                  3

3         1         stupid.mp3    5               4                  1

3         1         stupid.mp3    7               4                  1

 

 

Great!  But really, I am only interested in a single sess_id number....let's say 5.  I want to show the ad that's been played the least by sess_id 5.  In this case it would be ad_id 4, since sess_id has not played this song yet. 

 

If I try to filter the list by specifying sess_id =5 in my WHERE clause, then ad_id 4 doesnt' even show up.  b/c of course in that case it's will only show ads that have been played by 5.  I think this is a tricky problem   ???

[cooldude832]

Add an order by and limit clause there (LIMIT 1)

 

So ORDER BY Played ascending (So the least played = row 0)  *Played being the returned item you specified as the count(*)

 

And the LIMIT 1 (so you only get row 0)

 

 

Should do what you want as long as your WHERE Clause is just the session_id and that session_id has a count >0 which if it doesn't it doesn't exist technically for you.

[rubing]

Should do what you want as long as your WHERE Clause is just the session_id and that session_id has a count >0 which if it doesn't it doesn't exist technically for you.

 

 

That's the problem!  If I change the query to include the limits you suggest (changes in red)

 

SELECT ad.ad_id, ad.owner_api, ad.file, sess_id, plays_per_session, count( * ) AS played

FROM (ad LEFT JOIN ad_log ON ad.ad_id = ad_log.ad_played)

LEFT JOIN ad_limit ON ad.ad_id = ad_limit.advert_id

WHERE owner_api =1

AND sess_id =5

GROUP BY sess_id, ad_id

ORDER BY played ASC

 

Then my results set will look as follows:

ad_id owner_api file                 sess_id plays_per_session played

3               1 stupid.mp3           5                   4                       1

2               1 dumb.mp3           5                   3                       3

 

 

The problem with this result list is that it's missing ad_id number 4, crazy.mp3, which is the one that should played, since it's been played the least (0 times).  Unfortunately, this query only brings up ads that session 5 has played at least once. 

 

   

 

[cooldude832]

that query is really funky select from a table and only group by ad_id

[rubing]

Do you mean i should do a different kind of join.  I heard that a cross join is too taxing on the resources.

[cooldude832]

what are u selecting from?

[rubing]

1. I want to select all the ads from owner_api (all of a single advertisers ads). 

 

2. I want to select which of these ads been played least by the sess_id (browser's session id) by this client.  (In table ad_log a single log entry is made every time an ad is played.) 

 

3.  I want to eliminate ads that have been played too many times per this clients session.

 

As I showed you before I can select for the least played ads without a problem.  However, when I do that I fail to bring up the ads that haven't played at all.

 

again, here are my 3 tables (better fromatted), where ad_id = ad_played = advert_id

 

TABLE ad:

 

ad_id      owner_api      file        plays_purchased      times_played

1              0        cmn.mp3              0                        0

2              1        dumb.mp3            8                        4

3              1        stupid.mp3          0                        0

4              1        crazy.mp3        10000                      0

5              5        wild.mp3            1787                    333

6              5        justin.mp3          22                      22

 

TABLE ad_log:

 

ipaddress      sess_id      ad_played      date_played

11              1              2      2008-08-31 11:35:44

11              1              2      2008-08-31 11:35:44

55              5              3      2008-08-31 11:36:20

55              5              2      2008-08-31 11:35:44

55              5              5      2008-08-31 11:35:44

66              6              5      2008-08-31 11:35:44

66              6              5      2008-08-31 11:35:44

66              6              6      2008-08-31 11:35:44

77              7              5      2008-08-31 11:35:44

77              7              3      2008-08-31 11:35:44

11              1              5      2008-08-31 12:09:11

11              1              5      2008-08-31 12:09:11

 

 

Table ad_limit:

 

advert_id    plays_per_session

1                3

2                3

3                4

4                5       

6                2 

 

 

[cooldude832]

FROM (ad LEFT JOIN ad_log ON ad.ad_id = ad_log.ad_played)

 

 

that makes no sense

 

select from the table you want to select from not some join

[rubing]

Well, how would I select for ad_id  (TABLE ad) where ad_id has no corresponding ad_played (TABLE ad_log) entry for sess_id = 5

 

???

[cooldude832]

can I get a dump of the db?

[rubing]

sure!  should i send it to your email?  what form csv??

[fenway]

can I get a dump of the db?

sure!  should i send it to your email?  what form csv??

Let's keep the discussion to the boards, please....

 

You're LEFT JOIN-ing the ad_log table, so you need to move any conditions (like sess_id) to the ON clause, not the WHERE clause, or you won't get the "0" you want.  It's that easy.

[rubing]

I was thinking along those lines, but the only the only field these three tables have in common is the id field.  the session field is unique to the ad_log table. I thought I could only JOIN on fields which were the equivalent

[fenway]

I was thinking along those lines, but the only the only field these three tables have in common is the id field.  the session field is unique to the ad_log table. I thought I could only JOIN on fields which were the equivalent

I'm not sure what you mean.

[rubing]

I have the following 2 tables, where ad_id = ad_played.  I want to know how many times sess_id=5 has played each ad of owner_api =1

 

TABLE ad:

 

ad_id        owner_api

1                  0

2                  1

3                  1

4                  1

5                  5

6                  5

 

TABLE ad_log:

 

sess_id      ad_played

1              2   

1              2   

5              3   

5              2   

5              5     

6              5   

6              5     

6              6   

7              5     

7              3     

1              5   

1              5   

 

 

My Result table should look like this:

 

ad_Id        owner_api  sess_id      num_times_played

2                    1            5                    1

3                    1            5                    1

4                    1            5                    0

[fenway]

How about this?

 

SELECT 
ad.ad_id, ad.owner_api, ad_log.sess_id, COUNT(*) AS num_times_played 
FROM ad 
LEFT JOIN ad_log ON ( ad_log.ad_played = ad.ad_id AND ad_log.sess_id = 5 ) 
WHERE ad.owner_api = 1 
GROUP BY ad.ad_id

[rubing]

If I change COUNT(*) to COUNT(ad_log.ad_played), then it works perfect!  otherwise it counts null values (unplayed ads) as being played.  THanks so much fenway for this elegant solution!!!!

 

I had no idea that you could join on 2 different conditions like that.

 

SELECT

ad.ad_id, ad.owner_api, ad_log.sess_id, COUNT(ad_log.ad_played) AS num_times_played

FROM ad

LEFT JOIN ad_log ON ( ad_log.ad_played = ad.ad_id AND ad_log.sess_id = 5 )

WHERE ad.owner_api = 1

GROUP BY ad.ad_id

[fenway]

If I change COUNT(*) to COUNT(ad_log.ad_played), then it works perfect!  otherwise it counts null values (unplayed ads) as being played.

Oops, missed that... good catch; in general, I always count on the "joined" field.  Glad you got it working.