Want to return one record from JOIN, not multiple depending on row amount in either table.

Question

Want to return one record from JOIN, not multiple depending on row amount in either table.

Trystan Hughes

SSCrazy

Points: 2109
More actions
July 18, 2006 at 6:00 am

#113728

Hi all,
I have a table call tbl_NSP_Inspections, and another table called tbl_NSP_AnswerSets. Now their can be many AnswerSet records that relate to an Inspections record (for example, we have six AnswerSet records per each Inspection record).
What I want to do is to do a JOIN on the table tbl_NSP_Inspection and tbl_NSP_Answer and to return only the Inspections that are not complete. As it stands, if I do a normal INNER JOIN I get back six records per Inspection (one for each AnswerSet record), but what i want to do is to just have the one row returned for each Inspection.
What I currently have is the following...
SELECT I.InspectionUID,
I.QuestionTreeUID,
I.ProjectUID,
I.Description,
I.CreatedDate,
I.CreatedByUserUID,
I.SignedOffByUserUID,
I.SignedOffDate,
I.InspectionTypeUID,
I.IsSystem
FROM tbl_NSP_Inspection I
RIGHT OUTER JOIN tbl_NSP_AnswerSet A ON (A.InspectionUID = I.InspectionUID)
WHERE (I.QuestionTreeUID = 2)
AND (A.Completed = 0)
AND (DATEPART(DAY, I.CreatedDate) < DATEPART(DAY, GETDATE()))
ORDER BY I.CreatedDate ASC
But I can do what I want with the following, but i'd ideally want to achieve this via a simple JOIN, if at all possible.
SELECT I.InspectionUID,
I.QuestionTreeUID,
I.ProjectUID,
I.Description,
I.CreatedDate,
I.CreatedByUserUID,
I.SignedOffByUserUID,
I.SignedOffDate,
I.InspectionTypeUID,
I.IsSystem
FROM tbl_NSP_Inspection I
RIGHT OUTER JOIN tbl_NSP_AnswerSet A ON (A.InspectionUID = I.InspectionUID)
WHERE (I.QuestionTreeUID = 2)
AND (A.Completed = 0)
AND (DATEPART(DAY, I.CreatedDate) < DATEPART(DAY, GETDATE()))
GROUP BY I.InspectionUID,
I.QuestionTreeUID,
I.ProjectUID,
I.Description,
I.CreatedDate,
I.CreatedByUserUID,
I.SignedOffByUserUID,
I.SignedOffDate,
I.InspectionTypeUID,
I.IsSystem
ORDER BY I.CreatedDate ASC
(NOTE: I am using SQL Server Mobile, so no sub-queries, Functions or SP allowed )
Tryst

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Trystan

Please will you provide some sample date and expected results. Do either of the two queries produce the desired output? If so, why do you not want to use them as they are? If not, in what way is the output wrong?

The only difference I can see between the two queries is the GROUP BY clause. But you have no aggregate funtions in your SELECT list, so the GROUP BY clause is redundant.

John

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 2

Query...

SELECT I.InspectionUID,

A.AnswerSetUID,

I.QuestionTreeUID

FROM tbl_NSP_Inspection I

RIGHT OUTER JOIN tbl_NSP_AnswerSet A ON (A.InspectionUID = I.InspectionUID)

WHERE (I.QuestionTreeUID = 2)

AND (A.Completed = 0)

AND (DATEPART(DAY, I.CreatedDate) < DATEPART(DAY, GETDATE()))

Results returned...

00089_0000002616,2

00089_0000002609,2

Query...

SELECT I.InspectionUID,

A.AnswerSetUID,

I.QuestionTreeUID

FROM tbl_NSP_Inspection I

RIGHT OUTER JOIN tbl_NSP_AnswerSet A ON (A.InspectionUID = I.InspectionUID)

WHERE (I.QuestionTreeUID = 2)

AND (A.Completed = 0)

AND (DATEPART(DAY, I.CreatedDate) < DATEPART(DAY, GETDATE()))

GROUP BY I.InspectionUID,

I.QuestionTreeUID,

I.ProjectUID,

I.Description,

I.CreatedDate,

I.CreatedByUserUID,

I.SignedOffByUserUID,

I.SignedOffDate,

I.InspectionTypeUID,

I.IsSystem

results returned...

00089_0000002609,2

00089_0000002616,2

I was just wondering if a type of JOIN would satisfy what I need, without having to use the GROUP BY clause

Tryst

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Tryst

I assume you have a foreign key relationship between the two tables, so that every AnswerSet record is related to an inspection? If that's the case, then you can use an INNER JOIN instead of the RIGHT OUTER JOIN. Also, if you are grouping by the same number of columns as you are selecting, you may as well use SELECT DISTINCT and leave out the GROUP BY clause.

John

Vladan SSC-Insane Points: 22072 More actions · Answer 4

Hmm.. what is an "answer set"? Does it always have 6 rows in the table for each inspection? Why and what is the difference between these rows? Which of the rows should be returned, if you only want one?

If you explain this and post table structure and an example of data (not returned result only, but values of all potentially relevant columns, including those that you don't return), we could be able to find a way to do it without grouping or DISTINCT.