Results 1 to 7 of 7

Thread: T-SQL Help

  1. #1
    Join Date
    Jul 2004
    Posts
    191

    Unanswered: T-SQL Help

    Select prj_id, prj_pid, tree_id,prj_owner from MHGROUP.PROJECTS
    where Prj_id = tree_id
    and prj_pid = prj_id

    It return nothing.

    If you look at the data:

    Prj_ID Prj_Pid Tree_ID
    1302 1066 1066 TEMPLATES NULL
    1303 NULL 1303 Pat Prosecution NULL
    1305 1303 1303 Substantive 46102
    1305 1303 1303 Substantive 46800
    1305 1303 1303 Substantive 46803
    1305 1303 1303 Substantive 51363
    1305 1303 1303 Substantive 51412
    1305 1303 1303 Substantive 51416
    1305 1303 1303 Substantive 53521
    1305 1303 1303 Substantive 53536


    It should return some results what am I doing wrong, please help.

    THanks

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    It returns what your where clause asks it to return.

    Re read your where clause and does that data meet the condition specified.

    AND not OR

  3. #3
    Join Date
    Jul 2004
    Posts
    191
    I need Prj_id, Prj_pid and tree_id to equal the same number to show the relationship.

    with the query I am able to match prj_id = tree_id which give me the results that I am looking for. Now I would like to add the prj_pid which returns nothing. The data does support the query above.

    all the help I am very grateful

    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    To do what you are suggesting you want to do (to match values from different rows within the table ) you are going to need a self join (or maybe two - I'm not certain exactly where you are going here)

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Try this...

    Select P1.prj_id, P1.prj_pid, P1.tree_id, P1.prj_owner
    From PROJECTS P1 JOIN PROJECTS P2
    ON P1.Prj_id = P2.prj_id AND
    P1.prj_pid = P2.prj_pid AND
    P1.tree_id = P2.tree_id

    The data you have posted will not satisfy criteria in the above query...

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by milan1115
    It should return some results what am I doing wrong, please help.
    No single row has the criteria you've specified, so no rows ought to be returned.

    I suspect that you want something just a bit more sophisticated than you've described, but I can't quite figure out what that is... Can you try to rephrase your question in English (or even in Juris, I can translate ) and we'll see if we can cook up a solution for you?

    -PatP

  7. #7
    Join Date
    Nov 2003
    Posts
    167
    Your data seems to have 5 columns, but only 3 are labeled. Assuming that columns 1, 2 & 3 are Prj_ID, Prj_Pid, and Tree_ID respectively, there are no rows where all 3 fields are equal. The first row (1302, 1066, 1066) would match if you changed 1302 to 1066. Perhaps you are trying to return rows where EITHER Prj_id = tree_id OR prj_pid = prj_id, in which case, try the following:


    Code:
    Select        prj_id, 
                  prj_pid, 
                  tree_id,
                  prj_owner 
    from          MHGROUP.PROJECTS
    where        (Prj_id = tree_id) OR
                 (prj_pid = prj_id)
    Also, don't forget you have NULL values in your data - which may cause unexpected results depending on your business rules. If prj_id is null and tree_id is null for a record, then it will not be returned because the result of NULL = NULL is NULL.

    If you are actually trying to pull data recursively, then you will need some self-joins to accomodate as pootle suggested - but I can't see any obvious advantage from such an operation (there still won't be any results) so I must be misunderstanding what you're looking for.
    Kit Lemmonds

Posting Permissions

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