Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've provided a file that has the table structure and some sample data that I'm working with.

I don't understand why I'm getting nulls from a joined table, where there is data.

Here's my query

select
lt.taskListID,
lt.taskName,
ls.scriptName,
lts.taskScriptScriptID,
tpd.userTaskProgressUserID,
-- tpd.userTaskProgressTaskID,
--       tpd.userTaskProgressTaskScriptID,
tpd.userTaskProgressNote
from listTaskScripts lts
left join listTasks lt on lt.taskListID = lts.taskScriptTaskID
left join listScripts ls on ls.listScriptsID = lts.taskScriptScriptID
left join userTaskProgressDetail tpd on tpd.userTaskProgressTaskScriptID = ls.listScriptsID
where
lts.taskScriptTaskID = 2
and tpd.userTaskProgressTaskScriptID is null
--       and tpd.userTaskProgressUserID = 1
--       and tpd.userTaskProgressTaskID = 2

I'm getting nulls from tpd.userTaskProgressUserID and tpd.userTaskProgressNote, but (as you can see by looking at the pdf, there are values in those fields in that table for tpd.taskscripttaskid 2.

The query and result set are in the linked pdf.

Take a good look at the tables in the pdf. Maybe my problem lies in the table structure (I didn't create them - I'm just working with what was there...).

I hope that's enough info for someone to help me out.

Thanks

UPDATE

I made a small change based on foibs' comment and replaced the linked file with the new select and table change. Here's the new select:

select
lt.taskListID,
lt.taskName,
ls.scriptName,
lts.taskScriptScriptID,
tpd.userTaskProgressUserID,
tpd.userTaskProgressNote
from listTaskScripts lts
left join listTasks lt on lt.taskListID = lts.taskScriptTaskID
left join listScripts ls on ls.listScriptsID = lts.taskScriptScriptID
left join userTaskProgressDetail tpd on tpd.userTaskProgressTaskScriptID = ls.listScriptsID
where lts.taskScriptTaskID = 2
    and tpd.userTaskProgressUserID = 1
    and tpd.userTaskProgressPhaseID = 1
    and tpd.userTaskProgressSiteID = 2

However, I'm only getting the two records in userTaskProgressDetail. I need to list all records (script names) from listTaskScripts that are for the selected Task ID (2 in the where clause) - four records for TaskID 2. And then any notes from userTaskProgressDetail that may be for any of those records (scripts).

Do I need to change the table that I'm selecting from, then?

share|improve this question
    
what did you expect with and tpd.userTaskProgressTaskScriptID is null? –  foibs Feb 10 at 16:52
    
A good question and I forget why I had that there, but commenting that out gives me the same result set. –  marky Feb 10 at 16:55
    
Why would you use a left join instead of just a join on userTaskProgressDetail? A left join is going to add a row from userTaskProgressDetail whether or not the task has any real progress. –  Craig Efrein Feb 10 at 16:56
    
@CraigEfrein, Not sure if this answers your question, but I need to be able to list all scripts for the task, whether there are notes in userTaskProgressDetail for that script or not. I took out "left" in that line and the query returned no rows. Is that what you were suggesting to do? –  marky Feb 10 at 17:00
1  
lts.taskScriptTaskID returns four scriptsIDs : 20,55,95 & 96. There are no records in userTaskProgressDetail tpd that can satisfy the clause tpd.userTaskProgressTaskScriptID = ? for any of 20,55,95 or 96. Simple? –  foibs Feb 10 at 17:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.