Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Posts
    16

    Question Unanswered: Help with INNER JOIN...

    I've got a problem trying to query data from our db. What I need is a uniqe ID for the results. What I am getting is duplicate ID's.

    What I need:
    ---------------
    RCIID | pending_id | Name | move_out_date
    321 420 Jeff Borden 2003-08-20
    323 429 Todd McFarlane 2003-08-11


    What I actually get:
    -----------------------
    RCIID | pending_id | Name | move_out_date
    321 420 Jeff Borden 2003-08-20
    321 421 Jeff Borden 2003-08-07
    323 429 Todd McFarlane 2003-08-11


    My query looks like:
    -----------------------
    SELECT
    DISTINCT(Pending_Main.RCIID),
    Pending_Main.pending_id,
    Renter.Name,
    Pending_Move_Outs.move_out_date

    FROM
    Pending_Move_Outs INNER JOIN (Pending_Main INNER JOIN Renter
    ON (Pending_Main.RCIID = Renter.RCI_ID))
    ON Pending_Move_Outs.pending_id = Pending_Main.pending_id

    WHERE
    Pending_Main.Property_Number = '123'
    AND Pending_Main.pending_action = 'o'



    I also tried the following but got the same result:
    --------------------------------------------------------
    SELECT
    Pending_Main.unit_number,
    Pending_Main.RCIID,
    Renter.Name,
    Pending_Move_Outs.move_out_date
    FROM
    Pending_Main,
    Renter,
    Pending_Move_Outs
    WHERE
    Pending_Main.Property_Number = '123'
    AND Pending_Main.pending_action = 'o'
    AND (Pending_Main.RCIID = Renter.RCI_ID)
    AND (Pending_Main.pending_id = Pending_Move_Outs.pending_id)


    PLEEEEEEEEEZ Help me. Thank you...

  2. #2
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Hello Jeff,

    because of not knowing your data, I just can assume that in the tables "Pending_Main" and "Pending_Move_Outs" there is more than one entry for "RCIID=321". So, if you just want to get one output line for every RCIID in your table, you have to decide if you want to get the newest, the oldest or whatever other constraint you want to use.

    I hope this helps you further!

    Greetings,
    Carsten

  3. #3
    Join Date
    Sep 2003
    Posts
    16
    Here is a link to my data layout:

    http://www.greenfork.com/data-layout.jpg

    Plz help.

  4. #4
    Join Date
    Sep 2003
    Location
    Assen, Nederland
    Posts
    55
    try this ...

    Code:
    select distinct pm.rciid, pm.pending_id, r."name", pmo.move_out_date
    from pending_move_outs pmo, pending_main pm, renter r
    where pm.rciid = r.rci_id
          and
          pmo.pending_id = pm.pending_id
          and
          pm.property_number = 123
          and
          pm.pending_action = 'o'
    Hope that helped...
    Visit My Website : http://www.oraflame.com
    _____________________________
    Tarry Singh

    OCP DBA 8i
    Currently: SQL Server DBA 7,2000
    Oracle, PHP Programmer

  5. #5
    Join Date
    Sep 2003
    Posts
    16
    that worked. thanks so much. i love you

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    SELECT
    min(Pending_Main.RCIID),
    Pending_Main.pending_id,
    Renter.Name,
    min(Pending_Move_Outs.move_out_date)

    FROM
    Pending_Move_Outs INNER JOIN (Pending_Main INNER JOIN Renter
    ON (Pending_Main.RCIID = Renter.RCI_ID))
    ON Pending_Move_Outs.pending_id = Pending_Main.pending_id

    WHERE
    Pending_Main.Property_Number = '123'
    AND Pending_Main.pending_action = 'o'
    group by Pending_Main.pending_id, Renter.Name

  7. #7
    Join Date
    Sep 2003
    Posts
    16
    Nevermind, I was amiss. I've got the query that almost has it. I need to return the move_out_date with the max pending_id. My query is as follows. If I remove the Max() from the Max(Pending_Move_Outs.move_out_date), it returns multiple lines for a unit.


    SELECT
    Pending_Main.RCIID, max(Pending_Main.pending_id), Renter.Name, Max(Pending_Move_Outs.move_out_date) FROM Pending_Move_Outs
    INNER JOIN
    (Pending_Main INNER JOIN Renter ON (Pending_Main.RCIID = Renter.RCI_ID))
    ON
    Pending_Move_Outs.pending_id = Pending_Main.pending_id
    WHERE
    Pending_Main.Property_Number = '123' AND Pending_Main.pending_action = 'o'
    GROUP
    BY Pending_Main.RCIID, Renter.Name

    Plz help.

  8. #8
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Hello Jeff,

    try this one:

    select Pending_Main.RCIID
    ,Pending_Main.pending_id
    ,Renter.Name
    ,Pending_Move_Outs.move_out_date
    from pending_main
    ,pending_move_outs
    ,renter
    ,(
    select pending_main.rciid as rciid
    ,max(pending_main.pending_id) as pending_id
    from pending_main
    inner join pending_move_outs
    on pending_main.pending_id = pending_move_outs.pending_id
    group by pending_main.rciid
    ) as tmp
    where pending_main.property_number = '123'
    and pending_main.pending_action = 'o'

    and Pending_Move_Outs.pending_id = Pending_Main.pending_id
    and Pending_Main.RCIID = Renter.RCI_ID

    and tmp.rciid = pending_main.rciid
    and tmp.pending_id = pending_main.pending_id


    I assume that the experts would advise you a much more elegant solution, but I think this one works ;-)

    Hope that helps!

    Greeting,
    Carsten
    Originally posted by jeff_borden
    Nevermind, I was amiss. I've got the query that almost has it. I need to return the move_out_date with the max pending_id. My query is as follows. If I remove the Max() from the Max(Pending_Move_Outs.move_out_date), it returns multiple lines for a unit.


    SELECT
    Pending_Main.RCIID, max(Pending_Main.pending_id), Renter.Name, Max(Pending_Move_Outs.move_out_date) FROM Pending_Move_Outs
    INNER JOIN
    (Pending_Main INNER JOIN Renter ON (Pending_Main.RCIID = Renter.RCI_ID))
    ON
    Pending_Move_Outs.pending_id = Pending_Main.pending_id
    WHERE
    Pending_Main.Property_Number = '123' AND Pending_Main.pending_action = 'o'
    GROUP
    BY Pending_Main.RCIID, Renter.Name

    Plz help.

  9. #9
    Join Date
    Sep 2003
    Posts
    16
    that one works great! thanks so much for the assistance! you are truly a talent.

Posting Permissions

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