Help with complex Stored Procedure
-
I am having trouble getting all the data, exactly as needed in 1 stored procedure. I'll try sum this up as best as I can.
I have 4 tables
tblRecords (has SiteID)
tblSites
tblRegions (has SiteID)
lkup_Record_Regions (RecordID, RegionID here) <- this table represents regions that the record could be located in
lkup_Record_Regions_Serv (RecordID, RegionID here) <- this table represents regions that the record can serve
What I need is this. Simple data from Record table, given RecordID as SP Parameter. However, here's the part I'm stuck on.
I need all regions for the site of a record, as well as the following. If tblRecords.field3 = 0, I need to know all the regions for the record's site that exist in either lkup table. If tblRecord.field3 = 1, same data but only from lkup_Record_Regions. I really hope this makes sense, I find it complicating to even explain, lol.
End result is this is what I need. http://tinypic.com/view.php?pic=faty5c&s=5
Where Field 6 is the list of regions, and a checkmark image displays under each region if the record is displayed in that region. (tblRecords.field3 value determines whether or not the lkup_Record_Regions_Serv table is factored into where the record is displayed).
If this doesn't make sense I apologize, please let me know and I will try clear up anything
-
I'm leaving the design discussion out for a moment...
You might want to use a LEFT OUTER JOIN together with a CASE statement. (column based result set, meaning results for lkup_Record_Regions_Serv in one column and lkup_Record_Regions in another one) or
use a UNION query (or a cte) to get the results "row based".
Please note that I didn't take the time to link to a picture of your expected result...
If you need a more detailed answer, please provide a moer detailed question.
Have a look at the first article referenced in my signature for a good example how to do it
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply