Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2012
    Posts
    15

    Unanswered: Multiple self joins

    HI, this is my first post. I have many years experience developing in a Database
    Application called Filemaker, I'm now trying to use that experience to teach myself SQL (using Microsoft SQL is SQL 2008 r2)

    My question is can you have multiple self joins; if the answer is no then you don't need to read any further. If the answer is yes then I would like a little help if someone can provide it.

    I have a table which has two columns (for this example)

    spell | spellindicator | episode

    the spell is not unique e.g. there could be several records with the spell
    'A1234' the spellindicator column will contain a number 1 or 2. the data could look like
    this:

    spell-------spellindicator---------episode
    A1234-----------1 -----------------2
    B5674-----------1 -----------------1
    C8739-----------2 -----------------3
    A1234-----------1-----------------1
    B5674-----------2 -----------------1
    A123------------2 -----------------2
    A1234-----------1-----------------3

    I want to create two left self joins:

    1) to count the records that have the same order number with the spellindicator being 1.
    2) to find the max and min episode


    For 1) I've created two keys:

    pk_indicator = spell +':1'
    fk_indicator = spell + ':' + spellindicator

    example data:
    pk_indicator------fk_indicator
    A1234:1-----------A1234:1
    B5674:1-----------B5674:1
    C8739:1-----------C8739:2
    A1234:1-----------A1234:1
    B5674:1----------- B5674:2
    A1234:1-----------A1234:2
    A1234:1-----------A1234:1

    For2) The join is on the spell

    Part 1) works fine on its own
    Part 2) works fine on its own

    But put both joins in the same statement and the count function no longer works correctly. I realise that there is an issue with the self joins but I thought it would be possible to have multiple self joins. Have I got the syntax wrong??
    Thanks in advance.

    code:


    use leis
    go

    select l1.spell, l1.spellindicator, l1.episode, l1.pk_Indicator, l1.fk_indicator

    ,maxepisode = MAX(e2.episode)
    ,minepisode = MIN(e2.episode)

    ,case
    when MAX(e2.episode) > l1.episode
    then 'not last'
    else 'last'
    end lastepisode

    ,case
    when COUNT(l2.pk_indicator) > 1
    then 'invalid'
    else 'valid'
    end LEISCheck

    from dbo.LEISTest2 as l1

    left join dbo.LEISTest2 as e2
    on l1.spell = e2.spell

    left join dbo.LEISTest2 as l2
    on l1.pk_indicator = l2.fk_indicator

    group by l1.spell, l1.spellindicator, l1.episode, l1.pk_Indicator, l1.fk_indicator

    order by l1.spell
    Last edited by andrew a; 03-28-12 at 08:03.

  2. #2
    Join Date
    Mar 2012
    Location
    Somewhere In Europe
    Posts
    24
    Hi Andrew

    I don't quite sure what you are looking for, but Imho I would do in this way.
    Code:
    USE tempdb
    GO
    CREATE TABLE #temp_tbl
    (spell VARCHAR(5),
    spellinicator TINYINT,
    episode TINYINT)
    GO
    INSERT INTO #temp_tbl
    SELECT 'A1234',1,2
    UNION ALL
    SELECT 'B5674',1,1
    UNION ALL
    SELECT 'C8739',2,3
    UNION ALL
    SELECT 'A1234',1,1
    UNION ALL
    SELECT 'B5674',2,1
    UNION ALL
    SELECT 'A123',2,2
    UNION ALL
    SELECT 'A1234',1,3
    
    GO
    SELECT s.spell,s.spellinicator,s.max_episode,s.min_episode,ISNULL(z.cnt,0) AS cnt FROM
     (SELECT spell,spellinicator,MAX(episode) AS max_episode,MIN(episode) AS min_episode
    FROM #temp_tbl
     GROUP BY spell,spellinicator) AS s
     LEFT OUTER JOIN (SELECT spell,spellinicator,COUNT(*) AS cnt
     FROM #temp_tbl
     GROUP BY spell,spellinicator
     HAVING spellinicator = 1) AS z ON s.spell = z.spell AND s.spellinicator = z.spellinicator
     
     GO
     DROP TABLE #temp_tbl
     GO
    Regards
    Mike

  3. #3
    Join Date
    Mar 2012
    Posts
    15

    multiple self joins (joining issue)

    Thank you very much for your time on this Mike. I moved it on a little and have some code that you can run. To expand a little more on why I'm going down this route, I'm inserting some data into another table, there are many more case statements enclosed within this one select statement but they are straight forward decisions based on columns within the same table. I've split it into two tables now to see if it was a self join issue, it's not. I get the same results. I'm obviously not using the correct join statement though I've tried varying combinations of joins to try and sort it out. I've also tried count(*), Count(distinct, expression), count(all, expression)

    This operation is straight forward in Filemaker, but I think it's my lack knowledge of how SQL joins two tables together that's the issue here.

    Run this and you'll see what I mean. Run as is below, then run with the single commented out parts introduced to see it going wrong. The double commented out parts are just for experimentation and really mess it up but ultimately show it’s the joining that is the issue. Enjoy :-)


    use LEIS

    CREATE TABLE dbo.LEISprim
    ( spell bigint not null,
    spellindicator tinyint not null,
    episode tinyint not null,
    );

    INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
    VALUES (7032357, 1 , 1 )
    INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
    VALUES (7032357, 2 , 3 )
    INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
    VALUES (7032357, 1 , 2 )
    INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
    VALUES (7034826, 1 , 2 )
    INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
    VALUES (7034826, 2 , 1 )
    INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
    VALUES (7088650, 1 , 2 )
    INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
    VALUES (7088650, 1 , 1 )
    INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
    VALUES (7088650, 2 , 3 )
    INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
    VALUES (7088650, 1 , 4 )
    INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
    VALUES (7088650, 2 , 5 )
    INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
    VALUES (7171003, 1 , 3 )
    INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
    VALUES (7171003, 2 , 1 )
    INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
    VALUES (7171003, 2 , 2 )
    INSERT INTO dbo.LEISprim (spell, spellindicator, episode)
    VALUES (7175553, 1 , 1 )

    CREATE TABLE dbo.LEISsecon
    ( spell bigint not null,
    spellindicator tinyint not null,
    episode tinyint not null,
    fk_spellindicator varchar(9) not null
    );

    INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
    VALUES (7032357, 1 , 1 , '7032357:1')
    INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
    VALUES (7032357, 2 , 3 , '7032357:2')
    INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
    VALUES (7032357, 1 , 2 , '7032357:1')
    INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
    VALUES (7034826, 1 , 2 , '7034826:1')
    INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
    VALUES (7034826, 2 , 1 , '7034826:2')
    INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
    VALUES (7088650, 1 , 2 , '7088650:1')
    INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
    VALUES (7088650, 1 , 1 , '7088650:1')
    INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
    VALUES (7088650, 2 , 3 , '7088650:2')
    INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
    VALUES (7088650, 1 , 4 , '7088650:1')
    INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
    VALUES (7088650, 2 , 5 , '7088650:2')
    INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
    VALUES (7171003, 1 , 3 , '7171003:1')
    INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
    VALUES (7171003, 2 , 1 , '7171003:2')
    INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
    VALUES (7171003, 2 , 2 , '7171003:2')
    INSERT INTO dbo.LEISsecon (spell, spellindicator, episode, fk_spellindicator)
    VALUES (7175553, 1 , 1 , '7175553:1')

    Select l1.spell, l1.spellindicator, l1.episode

    ,case
    when COUNT(l2.fk_spellindicator) > 1
    then 'invalid'
    else 'valid'
    end LEISCheck

    --,case
    -- when MAX(e2.episode) > l1.episode
    -- then 'not last'
    -- else 'last'
    -- end lastepisode

    ,COUNT(l2.fk_spellindicator) indcount

    --,maxepisode = MAX(e2.episode)
    --,minepisode = MIN(e2.episode)
    ----,e2.fk_spellindicator

    from dbo.LEISprim l1

    inner join dbo.LEISsecon as l2
    on cast(l1.Spell as nvarchar) + ':1' = l2.fk_spellindicator

    --inner join dbo.LEISsecon as e2
    --on l1.spell = e2.spell

    group by l1.spell, l1.spellindicator, l1.episode
    ----,e2.fk_spellindicator
    order by l1.spell

    drop table dbo.LEISprim
    drop table dbo.LEISsecon
    ;
    Last edited by andrew a; 04-04-12 at 11:35.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •