Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Help with complex Stored Procedure Expand / Collapse
Posted Tuesday, June 11, 2013 9:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 29, 2013 6:54 AM
Points: 3, Visits: 11
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.

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

Post #1462248
Posted Tuesday, June 11, 2013 10:06 AM



Group: General Forum Members
Last Login: Wednesday, February 10, 2016 11:50 AM
Points: 6,897, Visits: 13,559
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

A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1462259
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse