How to "key/join" procedural vs. relational records??

  • Have an interesting problem can’t quite get my head around to parse in tsql.

    We have these 3 tables as the result of a procedurally created flat file (the Fortran type e.g. written from right to left, top to bottom) and read into sql. The file is created basically by reading in scanned paper orders and here's the results I'm forced to work with:

    3 resultant tables:

    ***Meal Table***

    Item Type | item descr | Order found in flat file

    MEAL | Happy Meal | 1

    MEAL |Big Breakfast | 5

    MEAL| Fish Special | 12

    MEAL| Chicken Platter | 14

    ***Food Table***

    Item Type | item descr | Order found in flat file

    FOOD | small burger | 2

    FOOD |small fries | 3

    FOOD| Bacon | 6

    FOOD| Pancakes | 7

    FOOD| Hash Browns | 8

    FOOD| Cup of Oatmeal | 9

    FOOD| Cod Planks | 13

    FOOD| Chicken Tenders | 15

    FOOD| Corn on the cob | 16

    FOOD| Coleslaw | 17

    ***Beverage Table ****

    Item Type | item descr | Order found in flat file

    DRINK| small Coke| 4

    DRINK| Coffee| 10

    DRINK| Orange Juice| 11

    DRINK| Budweiser| 18

    The question is how can I key/join the tables using only the "Order found in flat file" column and assuming Meals is the parent, Food is the child, and beverages are the optional grandchild?? And how best to key/join them expecting 100K records per day?

    Any and all suggestion would be of GREAT help.

    Thanks,

    Nick

  • Is this homework, cus it feels that way..

    CEWII

  • Man, I feel like slime posting this but for its real, I'm taSked to solve it and for the life of me can't get started.

    Even hints would help.

    MUCH OBLIGED:-)

  • If its real then fine, but I don't "do homework". Let me think about it over lunch..

    CEWII

  • Thanks.

    Much appreciated.

    Nick

  • What is the desired output?

    CEWII

  • The desired output was an inner join from Meals to Food and a left join from Food to Beverage.

    This helps. Thanks.

  • This:

    SELECT 'MEAL',

    F1.meal_seq AS meal_start_seq,

    (MAX(F2.meal_seq)-1) AS meal_end_seq,

    MAX(F1.item_descr) AS item_descr

    FROM MealFlatFile AS F1

    LEFT OUTER JOIN

    MealFlatFile AS F2

    ON F1.meal_seq < F2.meal_seq

    GROUP BY F1.meal_seq;

    Actually reutrns this:

    (No column name)meal_start_seqmeal_end_seqitem_descr

    MEAL113Happy Meal

    MEAL513Big Breakfast

    MEAL1213Fish Special

    MEAL14NULLChicken Platter

    Did I miss something?

  • Not sure, is the problem you didn't get a name for MEAL? You could do a 'MEAL' AS Meal

    I didn't look over the query..

    CEWII

  • In retrospect I should have used "Condiments" instead of "Beverages" since they should join only to the "Food" items. So for instance, if you subsituted the "Coffee" and "Orange Juice" for "Raisins" and "Brown Sugar" they should join only to the "Oatmeal" and not to the meal itself. Some Food items will not have any "condiments" while some may. Their appearance in the "order" column dictates to which "Food" item they should map to.

    In short, should be multiple levels deep.

  • I would use a technique like this. Some notes:

    1) Notice how I built out the dataset to be easily consumed for testing.

    2) This can be continuously expanded as you go down levels. I've used some generic naming here since your original data is not what you're actually trying to solve directly, but is an example.

    3) It is chewy and uses correlated subqueries, but it will get you there. There are probably cleaner ways but not simpler to explain if you have some difficulties. Let's get the concepts into play first. πŸ™‚ Then we'll go for optimizations.

    IF OBJECT_ID ( 'tempdb..#DataItems') IS NOT NULL

    DROP TABLE #DataItems

    IF OBJECT_ID ( 'tempdb..#Level1') IS NOT NULL

    DROP TABLE #Level1

    IF OBJECT_ID ( 'tempdb..#Level2') IS NOT NULL

    DROP TABLE #Level2

    -- This would be the staging table, get everything from the file IN first.

    CREATE TABLE #DataItems (LevelCode VARCHAR(15), ItemDesc VARCHAR(50), FilePosition INT)

    INSERT INTO #DataItems VALUES ( 'LEVEL 1', 'A', 1)

    INSERT INTO #DataItems VALUES ( 'LEVEL 1', 'B', 5)

    INSERT INTO #DataItems VALUES ( 'LEVEL 1', 'C', 12)

    INSERT INTO #DataItems VALUES ( 'LEVEL 1', 'D', 19)

    INSERT INTO #DataItems VALUES ( 'LEVEL 1', 'E', 20)

    INSERT INTO #DataItems VALUES ( 'LEVEL 2', 'A.a', 2)

    INSERT INTO #DataItems VALUES ( 'LEVEL 2', 'A.b', 3)

    INSERT INTO #DataItems VALUES ( 'LEVEL 3', 'A.b.1', 4)

    INSERT INTO #DataItems VALUES ( 'LEVEL 2', 'B.a', 6)

    INSERT INTO #DataItems VALUES ( 'LEVEL 3', 'B.a.1', 7)

    INSERT INTO #DataItems VALUES ( 'LEVEL 3', 'B.a.2', 8)

    INSERT INTO #DataItems VALUES ( 'LEVEL 2', 'B.b', 9)

    INSERT INTO #DataItems VALUES ( 'LEVEL 3', 'B.b.1', 10)

    INSERT INTO #DataItems VALUES ( 'LEVEL 3', 'B.b.2', 11)

    INSERT INTO #DataItems VALUES ( 'LEVEL 2', 'C.a', 13)

    INSERT INTO #DataItems VALUES ( 'LEVEL 2', 'C.b', 14)

    INSERT INTO #DataItems VALUES ( 'LEVEL 2', 'C.c', 15)

    INSERT INTO #DataItems VALUES ( 'LEVEL 2', 'C.d', 16)

    INSERT INTO #DataItems VALUES ( 'LEVEL 3', 'C.d.1', 17)

    INSERT INTO #DataItems VALUES ( 'LEVEL 3', 'C.d.1', 18)

    INSERT INTO #DataItems VALUES ( 'LEVEL 2', 'E.a', 21)

    select * from #DataItems

    ORDER BY FilePosition

    -- The rest of your staging tables:

    CREATE TABLE #Level1 ( L1ID INT IDENTITY( 1, 1) NOT NULL, ItemDesc VARCHAR(50), FilePos INT)

    INSERT INTO #Level1 (ItemDesc, FilePos)

    SELECTItemDesc, FilePosition

    FROM#DataItems

    WHERELevelCode = 'LEVEL 1'

    CREATE TABLE #Level2 ( L2ID INT IDENTITY( 1, 1) NOT NULL, L1ID INT NOT NULL, ItemDesc VARCHAR(50), FilePos INT)

    INSERT INTO #Level2 (L1ID, ItemDesc, FilePos)

    SELECT

    (SELECT L1ID FROM #Level1 WHERE FilePos = drv.MaxFP) AS L1ID,

    di.ItemDesc, di.FilePosition

    FROM

    #DataItems AS di

    CROSS APPLY

    (SELECT MAX( FilePos) AS MaxFP FROM #Level1 AS L1 WHERE di.FilePosition > L1.FilePos) AS drv

    WHERE

    LevelCode = 'LEVEL 2'

    SELECT * FROM #Level1

    SELECT * FROM #Level2


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Many thanks!!

    Will give it a go!!

    I luv this site!!

  • Craig-

    This works perfectly.

    Was not familiar with "Cross Apply",

    What would you see in the way of optimizations? Indexes on the "id"s on the Level tables?

    Much obliged for your assistance.

    Nick

  • al_nick (5/27/2011)


    Craig-

    This works perfectly.

    Was not familiar with "Cross Apply",

    Glad to hear, and my pleasure. πŸ™‚

    What would you see in the way of optimizations? Indexes on the "id"s on the Level tables?

    That will depend on where this structure is going when it's done. Is it a Delta load? If so, it'll depend on how you're detecting the deltas. How often are you modifying the final tables... things like that.

    However, yes, some quick indexes on the L1ID/L2IDs to allow for faster joining and an NC index or two on the descriptions would probably speed you up tremendously.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

Viewing 14 posts - 1 through 13 (of 13 total)

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