SQL Assistance required

[shannonwhitty]

I am trying to obtain some data and I have a way to do it however it
appears really longwinded and I'm sure there must be a better way to
get this using a join of some type without such a complex statement.

tbl1
--------
sid
role_id

tbl2
---------
sid
region_id

in tbl1 I have two types of roles

What I want to do is get all the sid's where role_id=role1 and where
region_id = all regions connected to sid=role2

Here is the rubbish attempt I have so far that works but can surely be
improved:

SELECT tbl2.sid
FROM tbl2
WHERE tbl2.region_id in
(SELECT tbl2.region_id
FROM tbl1
WHERE tbl1.sid = 8)
GROUP BY tbl2.sid

I also want to exclude sid=8 from the results as this is already
prevalent...

Thanks in advance

[shannonwhitty]

On Apr 3, 1:24*pm, Ed Murphy <[e-mail]:

[e-mail] wrote:

I am trying to obtain some data and I have a way to do it however it
appears really longwinded and I'm sure there must be a better way to
get this using a join of some type without such a complex statement.

tbl1
--------
sid
role_id

tbl2
---------
sid
region_id

in tbl1 I have two types of roles

What I want to do is get all the sid's where role_id=role1 and where
region_id = all regions connected to sid=role2

I assume you mean "region_id is one of the regions connected" etc.

Here is the rubbish attempt I have so far that works but can surely be
improved:

SELECT tbl2.sid
FROM tbl2
WHERE tbl2.region_id in
* *(SELECT tbl2.region_id
* *FROM tbl1
* *WHERE tbl1.sid = 8)
GROUP BY tbl2.sid

I also want to exclude sid=8 from the results as this is already
prevalent...

Where are role1 and role2? *Anyway, moving away from hardcoded sid
values that happen to be appropriate in one specific example, and
back to the more general approach that you mentioned earlier:

select *t2.sid
from * *tbl2 t2
* join *tbl1 t1 on t2.sid = t1.sid
where * t1.role_id = 'role1'
* and * t2.region_id in (
* * * * * * * * select *other_t2.region_id
* * * * * * * * from * *tbl2 other_t2
* * * * * * * * * join *tbl1 other_t1 on other_t2.sid = other_t1.sid
* * * * * * * * where * other_t1.role_id = 'role2'
* * * * )- Hide quoted text -

- Show quoted text -

Perfect.
Thanks