June 20, 2011 at 7:57 am
Hello,
I need help structuring a query. I have a table of client names. Each record in the Names table has several tables linked by the RecordID
field of the Names table. Each of those tables has an associacted reference table. So as an example of the 3 levels I have:
Names.Record -- links to -> Dates.TheDate -- which links to -> Date_Ref.DatesTypeID
[NAMES]
[HISTORY] [FLAGS] [DATES] [IMAGES] [ADDITIONAL]
[HIST_REF] [FLAG_REF] [DATE_REF] [IMAGE_REF] [ADD_REF]
So, my goal here is to display all of the associated information from all sub level tables whenever someone selects a record in the names table. I display the names table in a grid, when a user clicks a record in the grid, I want to execute a query which will gather data from all of these tables. So I started with this query.
SELECT
DR.DigitizeRefID,
DR.RecordID,
DR.Docimage,
DR.TabName,
DR.Pages,
H.History,
HR.HistoryType,
A.AdditionalValue AS [Field Value],
AR.AdditionalFieldName AS [Field Name],
FR.FlagDescription,
FR.FlagRefID,
DT.theDate,
DTR.DatesTypeID
FROM Image_Ref DR
INNER JOIN Dates DT ON DR.RecordID = DT.RecordID
INNER JOIN Dates_Ref DTR ON DT.DatesTypeID = DTR.DatesTypeID
INNER JOIN History H ON DR.RecordID = H.RecordID
INNER JOIN History_Ref HR ON HR.HistoryRefID = H.HistoryRefID
INNER JOIN AdditionalFields A ON DR.RecordID = A.RecordID
INNER JOIN AdditionalFields_Ref AR ON AR.AddRefID = A.AddRefID
INNER JOIN Flags F ON DR.RecordID = F.RecordID
INNER JOIN Flags_Ref FR ON FR.FlagRefID = F.FlagRefID
WHERE (DR.RecordID = Names.RecordID)
My query here returned empty results, even when there were records matching the Flags.RecordID and Dates.RecordID
I'm not sure how to structure this Query to achieve the desired results.
Thanks...
June 20, 2011 at 8:17 am
My first thought would be that some of the tables you're INNER JOIN-ing don't have data in them. You might try using an OUTER JOIN instead (http://msdn.microsoft.com/en-us/library/ms187518.aspx).
Example:
SELECT DR.DigitizeRefID,DR.RecordID
FROM Image_Ref DR
LEFT OUTER JOIN History H ON DR.RecordID = H.RecordID
_____________________________________________________________________
- Nate
June 20, 2011 at 8:28 am
see this articile: http://msdn.microsoft.com/en-us/library/ms190014.aspx about INNER JOINs and
this: http://msdn.microsoft.com/en-us/library/ms187518.aspx about OUTER JOINs like LEF, RIGHT ....
June 20, 2011 at 8:29 am
you'll use LEFT JOINs or RIGHT JOINs in your situation!!!!
June 20, 2011 at 8:36 am
i agree with the others left outer joins may be needed, do you have any examples tables and representative data, it would be easier to help you with this information
***The first step is always the hardest *******
June 20, 2011 at 8:50 am
Thanks ALL, LEFT OUTER JOIN is working.
Tom
June 20, 2011 at 12:45 pm
It should be important for you to know which require an inner join and which require left join. Using a left join on all tables may give you undesired results.
Thanks,
Jared
Jared
CE - Microsoft
June 20, 2011 at 1:39 pm
Use LEFT OUTER JOIN whenever you want to see rows in the primary table, whether or not matching rows exist in the joined table. INNER JOIN returns the set of matches between two tables.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply