[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL issue with getting correct data.

Posted on 2014-08-15
8
Medium Priority
?
182 Views
Last Modified: 2014-08-22
            SELECT *, DATE_FORMAT(search_request_date, '%Y-%m-%d') as mydate
            FROM video_searches t1
            INNER JOIN video_status t2 ON t1.record_id = t2.rel_record_id
            LEFT JOIN video_ratings t3 ON t2.rel_record_id = t3.rel_record_id
            WHERE 0 = 0
            AND t2.status_id = 1
            AND (t3.user_id IS NULL OR t3.user_id =! '#SESSION.user_id#')
            AND DATE(search_request_date) > DATE_SUB(CURDATE(), INTERVAL 2 DAY)
            GROUP BY mydate, t1.record_id

Open in new window


I seem to be having some issues with this query above... "t1" and "t2" tables are pretty standard id relationship tables (t1.record_id = t2.rel_record_id).  The third table is a middle table I populate for videos where a "discoverer user" role has accepted a video for a "judge user" role to judge. I want to query my data so all pending videos by the discover user are shown to the judges based off their user_id not existing in the t3.user_ratings table or that they've already judged the video equaling their user_id existing then. If judge A logs in and  goes to pending videos they see x amount of videos judging each one and submitting till they are all gone. If judge B goes in they would see the same set of videos and go through the same process.
0
Comment
Question by:brihol44
  • 3
  • 2
  • 2
7 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 40265296
Hi,

Can you show some sample data that illustrates your issue? That is, you have a reasonable looking query with reasonable looking joins, but the grouping looks like it might be a bit suspect. What is the userID condition in the where clause supposed to achieve?

HTH
  David
0
 

Author Comment

by:brihol44
ID: 40265356
Sure! Thx for the help.

Table video_ratings ...

record id = 500
user_id = 2
video_rel_record_id 603247
rating columns....

video_status
record_id = 924
rel_record_id = 603247
admin_id = 1
status_id = 1

video_searches
record_id = 603247
video_id = 10001
some non related stat columns...
0
 

Author Comment

by:brihol44
ID: 40265357
The data above is the row I'm pulling the 1 video that won't go away (record_id = 603247).
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 35

Expert Comment

by:David Todd
ID: 40265366
Hi,

Does the left join to video_ratings need to be instead an inner join?

I've asked the moderators to add this to the MySQL topic area.

HTH
  David
0
 
LVL 35

Accepted Solution

by:
James0628 earned 2000 total points
ID: 40265448
I'm wondering if the following line is correct:

AND (t3.user_id IS NULL OR t3.user_id =! '#SESSION.user_id#')

 Assuming that '#SESSION.user_id#' is the current user, that's going to produce a row for every row in t3 (for t2.rel_record_id) that is for a different user.  I think maybe what you want there is to only produce a row if there is no entry in t3 for t2.rel_record_id and the current user, which is different.

 If that's what you want, maybe just change the Join to:

LEFT JOIN video_ratings t3 ON t2.rel_record_id = t3.rel_record_id
AND t3.user_id = '#SESSION.user_id#'

 And then just check for t3.user_id IS NULL in the Where.


 OTOH, I'm a bit confused by the last part of this sentence:

 > I want to query my data so all pending videos by the discover user are shown to the
 > judges based off their user_id not existing in the t3.user_ratings table or that they've
 > already judged the video equaling their user_id existing then.

 Does the last part mean that you _want_ to include a row if that user already has an entry in t3 for that video (rel_record_id)?  If so, why are you checking t3.user_id =! '#SESSION.user_id#' in the Where?  Assuming again that '#SESSION.user_id#' is the current user,  that's going to explicitly exclude any existing entries in t3 for that user (while including any entries for other users).

 James
0
 

Author Closing Comment

by:brihol44
ID: 40278122
Sorry for the delay. Thx!
0
 
LVL 35

Expert Comment

by:James0628
ID: 40278670
You're welcome.  Glad I could help.

 James
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
Starting up a Project
Loops Section Overview

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question