Nested Query Problem
-
Not sure if this really is a nested Query issue, but...
I want to return all rows from one table, tbl_NSP_QuestionTree, where the ParentUID column doesn't equal the QuestionTreeUID column (the PK) where that row has the QuestionNodeTypeUID = 6.
(the ParentUID column is the FK to QuestionTreeUID column in the same table).
What I have so far is...
SELECT dbo.tbl_NSP_QuestionTree.QuestionTreeUID, dbo.tbl_NSP_QuestionTree.ProjectUID, dbo.tbl_NSP_QuestionTree.ShortDescription
FROM dbo.tbl_NSP_QuestionTree
WHERE (dbo.tbl_NSP_QuestionTree.QuestionNodeTypeUID = 6)
AND (dbo.tbl_NSP_QuestionTree.QuestionTreeUID NOT IN(SELECT QuestionTreeUID FROM tbl_NSP_QuestionTree WHERE (The ParentUID column is equal the QuestionTreeUIDs whose rows have the QuestionNodeTypeUID equal to 6)))
I hope I have explained myself well here, although I don't think I have
If not, then please feel free to ask me to elaborate more.Thanks
Tryst
-
Something like that?... you left-join and only return the record that did not have a parentID....
SELECT Question.QuestionTreeUID, Question.ProjectUID, Question.ShortDescription
FROM dbo.tbl_NSP_QuestionTree Question
LEFT OUTER JOIN (
SELECT ParentUID
FROM tbl_NSP_QuestionTree
WHERE tbl_NSP_QuestionTree.QuestionNodeTypeUID = 6) subQ ON subQ.ParentUID = Question.QuestionTreeUID
WHERE Question.QuestionNodeTypeUID = 6 AND
subQ.ParentUID IS NULL
-
If I am reading the specs correctly... no need for a derived table / sub query
SELECT yada...
FROM dbo.tbl_NSP_QuestionTree
WHERE QuestionNoteTypeUID 6 OR
(QuestionNoteTypeUID = 6 AND ParentUID QuestionTreeUID)
-
Hmmm... not as simple as Daryl understood it I think, but very close to it. I suppose it was meant as "where the ParentUID column does not point to QuestionTreeUID column (the PK) of a row, that has QuestionNodeTypeUID = 6".. i.e. we have to consider data from 2 different rows.
This could be what you are looking for:
SELECT QT.*
FROM dbo.tbl_NSP_QuestionTree QT
LEFT JOIN dbo.tbl_NSP_QuestionTree QT1 ON QT.ParentUID = QT1.QuestionTreeUID
AND QT1.QuestionNodeTypeUID = 6
WHERE QT1.QuestionTreeUID IS NULL
-
Thats exactly what I am trying to solve Vladan
But your query also returns rows that have a QuestionNodeTypeUID of 6, though it does leave out the rows whos ParentUID is equal to the QuestionTreeUID whos relating QuestionNodeTypeUID equals 6 (hope I didn't lose you all there
). So I have changed the query a bit...SELECT QT.QuestionNodeTypeUID, QT.*
FROM dbo.tbl_NSP_QuestionTree QT
LEFT JOIN dbo.tbl_NSP_QuestionTree QT1 ON QT.ParentUID = QT1.QuestionTreeUID
AND QT1.QuestionNodeTypeUID = 6
WHERE (QT1.QuestionTreeUID IS NULL) AND (QT.QuestionNodeTypeUID 6)
ORDER BY QT.QuestionNodeTypeUID
Thanks for your help on this people
Tryst
-
"But your query also returns rows that have a QuestionNodeTypeUID of 6"
Yes it does 🙂 I didn't realize from the description that these rows shouldn't be included. Anyway, you fixed it already, so everything is OK now. You're welcome 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply