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@hotmail.com) writes:
> 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...

The SELECT looks funny, as either it will return all sids in tbl2,
or it will return none at all, depending on whether there is a row with
sid = 8 in tbl1.

For these type of problems, it's usually a good idea to post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

This makes it easy to copy and paste to develop a tested solution. The
sample data usually s to clarify what you are asking for.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

shannonwhitty@hotmail.com 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'
)

On Apr 3, 1:24pm, Ed Murphy <emurph...@socal.rr.com> wrote:
> shannonwhi...@hotmail.com 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