[SOLVED] LEFT Joined tables missing records

rubing · August 31, 2008

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

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

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 · August 31, 2008

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 · August 31, 2008

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 · August 31, 2008

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 · August 31, 2008

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 · September 1, 2008

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

rubing · September 1, 2008

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

cooldude832 · September 1, 2008

what are u selecting from?

rubing · September 1, 2008

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

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

Table ad_limit:

advert_id plays_per_session

1 3

2 3

3 4

4 5

6 2

cooldude832 · September 1, 2008

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 · September 1, 2008

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 · September 1, 2008

can I get a dump of the db?

rubing · September 1, 2008

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

fenway · September 1, 2008

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 · September 1, 2008

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 · September 1, 2008

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 · September 1, 2008

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

5 3

5 2

5 5

6 5

6 6

7 5

7 3

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 · September 1, 2008

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 · September 2, 2008

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 · September 2, 2008

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.

Sign In

[SOLVED] LEFT Joined tables missing records

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Important Information