Multi-table search

[jpack23]

I would love some help with searching through Multiple tables in a database.

I have a parent table which contains an Id field, a type field, and a date field....call it TableA

(the type field corresponds to the child table....so typeB would refer to tableB)

Then I have 10 other child tables.... call them TableXs where X could be B,C,D,E etc. They contains the fields TableA.ID, TableX.ID, and TableXNotes

I want to grab each record in a given date range from TableA and then grab the correct child record from the tableXs


my end results I want to look like this

A.ID X.ID A.Date A.Type X.notes

123 1099 sept 1 TypeB notes
234 3234 oct 2 TypeC notes
454 2343 nov 1 TypeB notes
677 2342 Dec 3 TypeD notes


my biggest problem is getting the notes field from all child tables into just one notes field in my results.

I DONT want my results to look like this

A.ID X.ID A.Date A.Type B.Notes C.Notes D.Notes
123 1099 sept1 TypeB notes NULL NULL
234 2334 oct 2 TypeC NULL notes NULL
454 2343 nov1 typeB Notes NULL NULL
677 2342 Dec 3 TypeD NULL NULL Notes


I hope i explained this well enough

thanks very much for your help

 

[gmmastros]

Use the Coalesce function.

Instead of

[tt]
Select A.Id, X.id, A.Date, A.Type,
B.Notes, C.Notes, D.Notes
From .....
[/tt]

Select A.Id, X.id, A.Date, A.Type,
       [!]Coalesce([/!]B.Notes, C.Notes, D.Notes[!], '') As Notes[/!]
From   .....

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jpack23]

gmmastros

thank you, that helps a ton.