Results 1 to 13 of 13

Thread: JOIN for tables

  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Unanswered: JOIN for tables

    Hey Folks,
    I am stuck at one place in data migration........ I have the following source tables having no PK / INDEX / Constraint defined on any of them........


    T1
    (
    ID1 INT,
    STYLE VARCHAR(34)
    )

    T2
    (
    COL_ID VARCHAR(34),
    OBJ_ID VARCHAR(34)
    )

    T3
    (
    OUT_ID VARCHAR(34),
    ITEM VARCHAR(34),
    )


    And here is the relation..........


    Quote:
    T1.STYLE = T2.COL_ID

    T2.OBJ_ID = T3.OUT_ID

    T3.ITEM = T2.COL_ID




    Here is what I have tried with.........

    Quote:
    SELECT * FROM T3 WHERE ITEM IN
    (
    SELECT COL_ID
    FROM T1
    INNER JOIN T2
    ON
    T1.STYLE = T2.COL_ID
    INNER JOIN T3
    ON
    T2.OBJ_ID = T3.OUT_ID
    )



    But this query is not helping me out. I mean the number of output records are 0.

    Now can any one suggest me a join that includes all the 3 tables, or finetune my query?



    Thanks,
    Rahul Jha

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is only a guess, but I'd start with:
    Code:
    SELECT T3.*
       FROM T1
       INNER JOIN T3
          ON (T3.ITEM = T1.STYLE)
    -PatP

  3. #3
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Now don't ask me like why does this kind of relation exists....... Ok, let me answer this......... The reason for this lies in the fact that the DB is completely denormalised....... And I need the join statement for the data migration...........


    Thanks,
    Rahul Jha

  4. #4
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Do you think this will work PAT? I doubt........ I guess by doing this we will not be able to eliminate the records which does not satisfy the 2nd realtion (T2.OBJ_ID = T3.OUT_ID
    )

    What do you say?




    Thanks,
    Rahul Jha

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Look at your join criteria again. I'm pretty sure that the first and third criteria simplify into what I've presented.

    -PatP

  6. #6
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    theoritically it looks the same......... but don't know about the results. In both the queries the resultset is same. i.e. 0 record.



    Thanks,
    Rahul Jha

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If there are no rows being returned, then something is incorrect in the join criteria. What do you get for results when you execute:
    Code:
    SELECT Count(*) AS grand
       Count(T1.STYLE) AS t1_raw
    ,  Count(T2.COL_ID) AS t2_raw
    ,  Count(T3.ITEM) AS t3_raw
    ,  Sum(CASE WHEN T1.STYLE = T2.COL_ID THEN 1 END) AS p1
    ,  Sum(CASE WHEN T2.OBJ_ID = T3.OUT_ID THEN 1 END) AS p2
    ,  Sum(CASE WHEN T3.ITEM = T2.COL_ID THEN 1 END) AS p3
       FROM T1
       LEFT JOIN T2
          ON (T2.COL_ID = T1.STYLE)
       LEFT JOIN T3
          ON (T3.ITEM = T1.STYLE)
    -PatP

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK,

    I want to know what Ban is and why you like it galore?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ban. Now you know.

    -PatP

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    "get out of your soapbox"

    Silly young marketing execs

    That's almost as bad as this thread
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Guys what about the following code.........


    SELECT * FROM T2
    INNER JOIN
    (
    SELECT T3.ITEMS
    FROM T1
    INNER JOIN T2
    ON
    T1.STYLE = T2.COL_ID
    INNER JOIN T3
    ON
    T3.OUT_ID = T2OBJ_ID
    ) T4
    ON
    T2.COl_ID = T4.ITEMS

    Thanks,
    Rahul Jha

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What about it?
    George
    Home | Blog

  13. #13
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Don't scratch your head more and more George......... Don't waste your time in this thread........... Come back to this thread with the open mind, without been prejudice....... That might help you as well as me............ Hope you understand this.......




    Thanks,
    Rahul Jha

Posting Permissions

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