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 😉



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply