By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,360 Members | 1,412 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,360 IT Pros & Developers. It's quick & easy.

SQL Assistance required

P: n/a
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
Apr 2 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Apr 3, 1:24*pm, Ed Murphy <emurph...@socal.rr.comwrote:
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
Jun 27 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.