Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    70

    Unhappy Unanswered: Very very large join of 5 tables... IM quite lost, please help

    I posted before askin to join 3 tables and ARWinner gave me a solution of this

    SELECT A.ID, A.SCHEDSTARTDATE, A.DBID, B.NAME, ...12 columns all from A..., C.NAME_HOST
    FROM FirstTable A INNER JOIN ON (a.state = b.ID)
    LEFT OUTER JOIN ThirdTable C
    ON A.DBID = C.DBID

    Well it turns out I joined it incorrectly as I have to go through a parent/child links table.

    so for every A.DBID there is a corresponding LINKS.PARENT_DBID that has a list of CHILD_DBIDs. And each CHILD_DBID has an associated TYPE. So when LINKS.CHILDTYPE = 16781452 then LINKS.CHILD_DBID is to be joined with table ThirdTable and when LINKS.CHILDTYPE = 16781502 its supposed to be joined with FourthTable...

    I am completely lost on this.

    Plus, ontop of that, I have no idea what type of join to do now...
    For example, say I have one row from Table A, its DBID gives me 4 rows from Table C and 4 rows from Table D.
    What I need is to have it return 8 rows
    The first 4 rows have the 4 names Im grabbing from Table C and have 4 blanks in the column Im grabbing from Table D.
    Then the last 4 rows have 4 blanks in the column Im pulling from Table C and have the 4 names Im grabbing from Table D.

    So it would look like:
    DBID | Table A's data | TableC's Name | TableD's data
    1234_____blah___________App1__________________
    1234_____blah___________App2__________________
    1234_____blah___________App3__________________
    1234_____blah___________App4__________________
    1234_____blah_________________________Host1___
    1234_____blah_________________________Host1___
    1234_____blah_________________________Host1___
    1234_____blah_________________________Host1___

    Can that even be done since DBID is being used as a key??
    Last edited by JamesAvery22; 01-30-04 at 14:50.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Very very large join of 5 tables... IM quite lost, please help

    Originally posted by JamesAvery22
    Well it turns out I joined it incorrectly as I have to go through a parent/child links table.

    so for every A.DBID there is a corresponding LINKS.PARENT_DBID that has a list of CHILD_DBIDs. And each CHILD_DBID has an associated TYPE. So when LINKS.CHILDTYPE = 16781452 then LINKS.CHILD_DBID is to be joined with table ThirdTable and when LINKS.CHILDTYPE = 16781502 its supposed to be joined with FourthTable...

    I am completely lost on this.

    Plus, ontop of that, I have no idea what type of join to do now...
    For example, say I have one row from Table A, its DBID gives me 4 rows from Table C and 4 rows from Table D.
    What I need is to have it return 8 rows
    The first 4 rows have the 4 names Im grabbing from Table C and have 4 blanks in the column Im grabbing from Table D.
    Then the last 4 rows have 4 blanks in the column Im pulling from Table C and have the 4 names Im grabbing from Table D.

    So it would look like:
    DBID | Table A's data | TableC's Name | TableD's data
    1234_____blah___________App1__________________
    1234_____blah___________App2__________________
    1234_____blah___________App3__________________
    1234_____blah___________App4__________________
    1234_____blah_________________________Host1___
    1234_____blah_________________________Host1___
    1234_____blah_________________________Host1___
    1234_____blah_________________________Host1___

    Can that even be done since DBID is being used as a key??
    I think it should look something like this:

    select a.dbid, a.data, c.data, d.data
    from
    tableC c, tableD d, tableA a
    left outer join links l1 on a.dbid=l1.parent_dbid
    left outer join links l2 on a.dbid=l2.parent_dbid
    where
    l1.CHILDTYPE = 16781452
    and l2.CHILDTYPE = 16781502
    and l1.child_dbid=c.dbid
    and l2.child_dbid=d.dbid
    and coalesce(c.data, d.data) is not null;

  3. #3
    Join Date
    Jan 2004
    Posts
    70

    Re: Very very large join of 5 tables... IM quite lost, please help

    Originally posted by n_i
    I think it should look something like this:

    select a.dbid, a.data, c.data, d.data
    from
    tableC c, tableD d, tableA a
    left outer join links l1 on a.dbid=l1.parent_dbid
    left outer join links l2 on a.dbid=l2.parent_dbid
    where
    l1.CHILDTYPE = 16781452
    and l2.CHILDTYPE = 16781502
    and l1.child_dbid=c.dbid
    and l2.child_dbid=d.dbid
    and coalesce(c.data, d.data) is not null;
    Thats really close but its not pulling enough documents =( It gets 400something when the original got 900something.

    Did this:

    SELECT A.DBID, B.NAME, C.NAME_HOST, D.APPLICATIONNAME
    FROM
    udbucm.STATEDEF B, udbucm.HOST C, udbucm.APPLICATION D, udbucm.InfraChange A
    left outer join udbucm.PARENT_CHILD_LINKS l1 on A.dbid=l1.parent_dbid
    left outer join udbucm.PARENT_CHILD_LINKS l2 on A.dbid=l2.parent_dbid
    where
    l1.CHILD_ENTITYDEF_ID = 16781452
    and l2.CHILD_ENTITYDEF_ID = 16781502
    and l1.child_dbid = c.dbid
    and l2.child_dbid=d.dbid
    and coalesce(C.NAME_HOST, D.APPLICATIONNAME) is not null
    and A.STATE = B.ID
    -----------------------------

    That is what pulls 400 =(

    it is pulling repeat A.DBIDs (Id say there are only 50 unique A.DBIDs in the whole query when the original pulled 900+) the NAME_HOST are all correct but the majority of APPLICATIONNAME's are all blank
    Last edited by JamesAvery22; 01-30-04 at 16:29.

  4. #4
    Join Date
    Jan 2004
    Posts
    70
    i really need help =(

    I was using this:

    SELECT DISTINCT A.ID, A.SCHEDSTARTDATE, A.TITLE, B.NAME, like 20 more cols from A, C.NAME_HOST, D.APPLICATIONNAME
    FROM
    udbucm.InfraChange A
    INNER JOIN udbucm.StateDef B ON (A.STATE = B.ID)
    left outer join udbucm.PARENT_CHILD_LINKS l1 on (A.dbid=l1.parent_dbid)
    left outer join udbucm.HOST C on (l1.child_dbid = c.dbid and l1.CHILD_ENTITYDEF_ID = 16781452 )
    left outer join udbucm.PARENT_CHILD_LINKS l2 on (A.dbid=l2.parent_dbid)
    left outer join udbucm.APPLICATION D on (l2.child_dbid = d.dbid and l2.CHILD_ENTITYDEF_ID = 16781502 )

    It was grabbing the right amount of documents. about 1500 even. I was grabbing about 1000 before. I was hoping the 500 extra were the repeat rows I wanted. but they turned out to all be unique rows =(

    What am I doing wrong??

Posting Permissions

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