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)
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