January 6, 2003 at 1:17 pm
I am attempting to compare two tables, one table is a static list of Required Fields tblRequired that need to be filled in on a website, the other table tblStatus tracks what fields have been filled in by the user. When a user completes a section of the form, a record is inserted into the 'tblStatus' table showing the step completed.
Is it possible to A) Show all the records from the static table, for the records that have been Completed and the records from the tblStatus, BUT NOT REPEAT the values.
I have tried IN statements, Union, having etc.
Essentially what the end result looks like is a Checklist showing the user what steps have been completed and which have yet to be completed. The closest I have come is using a Union query, the problem is that it shows Both the Completed Step from the Status table and repeats it again from the tblRequired. The user sees a completed check mark, then the field repeated with out the check mark.
Hope this makes sense.
January 6, 2003 at 1:24 pm
Can you post some DDL for the tables? I suspect what you want is a left outer join, but I'd need some code to be sure.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 6, 2003 at 2:38 pm
Thanks for your reply. I have emailed you some files, but I will post the Union query here perhaps you can make heads or tails...?
SELECT StepID,PrequalID, RequiredPageDescription, RequiredPageURL , SectionNumber, StepNumber
FROM dbo.tblRequired INNER JOIN
dbo.tblStepStatus ON dbo.tblRequired.RequiredSectionNumber = dbo.tblStepStatus.SectionNumber AND
dbo.tblRequired.RequiredStepNumber = dbo.tblStepStatus.StepNumber
WHERE PrequalID = 580
UNION ALL
SELECT DISTINCT NULL,NULL, RequiredPageDescription, RequiredPageURL,RequiredSectionNumber, RequiredStepNumber
FROM dbo.tblRequired
ORDER BY SectionNumber,StepNumber
January 6, 2003 at 3:52 pm
The issue is your WHERE clause.
If I run a left outer join like this:
SELECT
StepID,PrequalID, RequiredPageDescription, RequiredPageURL , SectionNumber, StepNumber
FROM dbo.tblRequired
left outer JOIN dbo.tblStepStatus
ON dbo.tblRequired.RequiredSectionNumber = dbo.tblStepStatus.SectionNumber
AND dbo.tblRequired.RequiredStepNumber = dbo.tblStepStatus.StepNumber
WHERE PrequalID = 580
ORDER BY SectionNumber,StepNumber
the left join returns the result you are looking for, but the unfilled in items have a prequal of NULL, so the WHERE clause filters them out. There are a couple solutions I see.
1. Use this:
SELECT
StepID,PrequalID, RequiredPageDescription, RequiredPageURL , SectionNumber, StepNumber
FROM dbo.tblRequired
left outer JOIN dbo.tblStepStatus
ON dbo.tblRequired.RequiredSectionNumber = dbo.tblStepStatus.SectionNumber
AND dbo.tblRequired.RequiredStepNumber = dbo.tblStepStatus.StepNumber
WHERE PrequalID = 580 or prequalid is null
ORDER BY RequiredSectionNumber, RequiredStepNumber
so you have prequals of null included. Kind of assuming that the prequalid is required in the tblStepStatus.
or 2. This:
select
prequalid
, requiredsectionnumber
, requiredstepnumber
, *
from tblrequired tr
left outer join ( select * from tblstepstatus where prequalid = 580) ts
on tr.requiredsectionnumber = ts.sectionnumber
and tr.requiredstepnumber = ts.stepnumber
order by
requiredsectionnumber
, requiredstepnumber
uses a derived table.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 6, 2003 at 4:30 pm
The Derived table worked! I have only seen this kind of coding in T-SQL, ala the GURU's Guide by Ken Henderson... Any tips on figuring out why this worked. What I can decude is that you created a result set with the first select statement then filtered out the second select items replacing the Null with the prequalid or something...!!! I appreciate your help.
January 6, 2003 at 5:01 pm
you are welcome.
it worked as I wrote becuase your WHERE clause was filtering out the tblrequired value.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply