tags:

views:

24

answers:

1

I'm very confused by the subquery function in MySQL.

For my example I use 3 tables:

adr contains addresses adr_acc contains groups or users that have access to the addresses usr_grp contains the useres belonging to a group

select * from adr where 
adr.adr_id in
  (select ac1.adr_id from adr_acc as ac1 where
    (
      (ac1.acc_type = 'U' and ac1.acc_id = '".$s['user']."') or
      (ac1.acc_type = 'G' and ac1.acc_id in
        (select ug1.grp_id from usr_grp as ug1 where ug1.usr_id = '".$s['user']."')
      )
    )
  )

I get an error:

1054 - Unknown column 'adr.adr_id' in 'IN/ALL/ANY subquery'

what am I missing?

A: 

ARGGGGGGGGGGGGGG

I'm stupid, sorry for asking something incredibly stupid like that.

select * from adr where
adr.id in (select ac1.adr_id from adr_acc as ac1 where ( (ac1.acc_type = 'U' and ac1.acc_id = '".$s['user']."') or (ac1.acc_type = 'G' and ac1.acc_id in (select ug1.grp_id from usr_grp as ug1 where ug1.usr_id = '".$s['user']."') ) ) )

This is the correct query.

Stefan Immel