Join Issues

koncept · #1 (**permalink**) 12-04-06, 21:50

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

equipment
equip_location = variable
deleted = 0

equip_out
equip_in = null

Code:

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

koncept · #2 (**permalink**) 12-04-06, 22:13

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

Teddy · #3 (**permalink**) 12-04-06, 23:11

What? A view is the equivelent to a stored procedure in sql 2k5 express? When did that happen?

r937 · #4 (**permalink**) 12-05-06, 06:06

Code:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech