SQL Assistance required

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • shannonwhitty@hotmail.com
    #1

    SQL Assistance required

    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
    Tags: None
    • shannonwhitty@hotmail.com
      #2
      Re: SQL Assistance required

      On Apr 3, 1:24 pm, Ed Murphy <emurph...@soca l.rr.comwrote:
      shannonwhi...@h otmail.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_i d = 'role2'
              )- Hide quoted text -
      >
      - Show quoted text -
      Perfect.
      Thanks

        Comment

        Previous template Next
        Working...