Results 1 to 4 of 4

Thread: Join Issues

  1. #1
    Join Date
    Jul 2006

    Unanswered: Join Issues

    I am trying to write a query to show me every thing from table 1 (table one is called equipment) and only the information from table 2 (called equip_out) related to the equipment item with some restrictions

    equip_location = variable
    deleted = 0

    equip_in = null

    SELECT equipment.equip_name, equip_out.student_id, equip_out.time_out
    FROM equipment
    full outer JOIN equip_out ON equipment.equip_id=equip_out.equip_id
    where equipment.deleted = 0
    there is a column called time_in in equip_out that should only be joined with the equipment list if that field is null. the equipment list should include all fields where the column deleted is 0 (bc of logging issues i do not want anything removed ever) and equip_location = variable (variable is passed in when the query is built in vb)

    this one has me stumped, if its not possible just let me know. i am going to keep working on it and will post a solution if i find one.

    thanks in advance
    Last edited by koncept; 12-04-06 at 21:54.

  2. #2
    Join Date
    Jul 2006
    well i have a solution, but it does not seem like it would be the best available one so if someone comes up with one better that would be great.

    my solution is to use a view (stored procedure equiv in sql 2k5 express) to do the limiting on equip_out and then use that view in the join

  3. #3
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    What? A view is the equivelent to a stored procedure in sql 2k5 express? When did that happen?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Apr 2002
    Toronto, Canada
    select equipment.equip_name
         , equip_out.student_id
         , equip_out.time_out
      from equipment
    left outer
      join equip_out 
        on equip_out.equip_id = equipment.equip_id 
       and equip_out.time_in is null  
     where equipment.deleted = 0 | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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