This discussion is archived
13 Replies Latest reply: Sep 13, 2013 2:02 AM by BluShadow RSS

Query problem

308aa62b-4323-4ed5-b0ba-c41579505362 Newbie
Currently Being Moderated

Hello,

I have this table:

 

serial_numberlocation
1AG01
1AG01_village
1AG01_city
2BC01
3CC01
3CC03

 

I need a query for returning only the serial_number with location with both 'AG01' and 'AG01%' (both condition must be respected :like 'AG01' and 'AG01%')

In this case only serial_number = 1.

Sorry for english.

  • 1. Re: Query problem
    Hoek Guru
    Currently Being Moderated

    And your exact problem is?

  • 2. Re: Query problem
    940315 Newbie
    Currently Being Moderated

    add condition in where clause

    where location like 'AG01%'

  • 3. Re: Query problem
    BluShadow Guru Moderator
    Currently Being Moderated

    Do you mean something like this?

     

    SQL> ed
    Wrote file afiedt.buf

      1  with t as (select 1 as serial_number, 'AG01' as location from dual union all
      2             select 1, 'AG01_village' from dual union all
      3             select 1, 'AG01_city' from dual union all
      4             select 2, 'BC01' from dual union all
      5             select 3, 'CC01' from dual union all
      6             select 3, 'CC03' from dual)
      7  --
      8      ,r as (select '&required_location' as rloc from dual)
      9  --
    10  select distinct t1.serial_number
    11  from   r cross join
    12         t t1 join t t2 on (t1.serial_number = t2.serial_number and t1.location != t2.location)
    13  where  t1.location = r.rloc
    14* and    t2.location like r.rloc||'%'
    SQL> /

    Enter value for required_location: AG01
    old   8:     ,r as (select '&required_location' as rloc from dual)
    new   8:     ,r as (select 'AG01' as rloc from dual)

     

    SERIAL_NUMBER
    -------------
                1

     

    If that's not what you want, please post more specific details and example data with expected output, as described in the FAQ: Re: 2. How do I ask a question on the forums?

  • 4. Re: Query problem
    308aa62b-4323-4ed5-b0ba-c41579505362 Newbie
    Currently Being Moderated

    I have to take step by step.

    For table

     

    PERSONDEPARTMENT
    danmk
    danit
    ionsales
    johnmk
    bobit
    stanmk
    stanit
    stan

    off

    I want a query to return only the persons in department mk and it.

    The result must be only dan. (not stan who is at department off)

     

    Thanq

  • 5. Re: Query problem
    BluShadow Guru Moderator
    Currently Being Moderated

    You mean this sort of logic?

    SQL> ed
    Wrote file afiedt.buf

      1  with t as (select 'dan' as person, 'mk' as dept from dual union all
      2             select 'dan', 'it' from dual union all
      3             select 'ion', 'sales' from dual union all
      4             select 'john', 'mk' from dual union all
      5             select 'bob', 'it' from dual union all
      6             select 'stan', 'mk' from dual union all
      7             select 'stan', 'it' from dual union all
      8             select 'stan', 'off' from dual)
      9  --
    10  select t1.person
    11  from   t t1
    12         join t t2 on (t1.person =  t2.person and t1.dept != t2.dept)
    13  where  t1.dept = 'mk'
    14  and    t2.dept = 'it'
    15* and    not exists (select * from t where t.person = t1.person and t.dept not in ('mk','it'))
    SQL> /

     

    PERS
    ----
    dan

  • 6. Re: Query problem
    BluShadow Guru Moderator
    Currently Being Moderated

    It could also be achieved like this...

     

    SQL> ed
    Wrote file afiedt.buf

      1  with t as (select 'dan' as person, 'mk' as dept from dual union all
      2             select 'dan', 'it' from dual union all
      3             select 'ion', 'sales' from dual union all
      4             select 'john', 'mk' from dual union all
      5             select 'bob', 'it' from dual union all
      6             select 'stan', 'mk' from dual union all
      7             select 'stan', 'it' from dual union all
      8             select 'stan', 'off' from dual)
      9  --
    10  select t1.person
    11  from   t t1
    12         join t t2 on (t1.person = t2.person and t2.dept in ('mk','it') and t1.dept != t2.dept)
    13  group by t1.person
    14* having count(*) = 2
    SQL> /

     

    PERS
    ----
    dan

  • 7. Re: Query problem
    Hoek Guru
    Currently Being Moderated

    Another way:

     

    SQL> -- generating sample data:
    SQL> with t as (select 'dan' as person, 'mk' as dept from dual union all
      2              select 'dan', 'it' from dual union all
      3              select 'ion', 'sales' from dual union all
      4              select 'john', 'mk' from dual union all
      5              select 'bob', 'it' from dual union all
      6              select 'stan', 'mk' from dual union all
      7              select 'stan', 'it' from dual union all
      8              select 'stan', 'off' from dual)
      9  --
    10  -- actual query:
    11  --
    12  select person
    13  from   t
    14  group by person
    15  having  3 = sum(case
    16                    when dept = 'mk' then 1
    17                    when dept = 'it' then 2
    18                    else -1
    19                  end
    20                 );

    PERS
    ----
    dan

    1 row selected.

  • 8. Re: Query problem
    308aa62b-4323-4ed5-b0ba-c41579505362 Newbie
    Currently Being Moderated

    This was very useful, thanks all.

    Now i have another problem, i will return with more info.

     

    EDIT:

    For table:

    with t as       (select 'dan' as person, 'mk' as dept from dual

          union all

          select 'dan', 'it' from dual

          union all

          select 'dan', 'it_01' from dual

          union all

          select 'dan', 'it_02' from dual

          union all

          select 'ion', 'sales' from dual

          union all

          select 'ion', 'it_03' from dual

          union all

          select 'john', 'mk' from dual

          union all

          select 'john', 'mk_01' from dual

          union all

          select 'bob', 'it' from dual

          union all

          select 'stan', 'mk' from dual

          union all

          select 'stan', 'it' from dual

          union all

          select 'stan', 'off' from dual       )

    select t.* from  t;

    I need a query to return all the person with both dep like 'XX' and 'XX%'

    Ex: to return only dan and john .

  • 9. Re: Query problem
    BluShadow Guru Moderator
    Currently Being Moderated

    I guess you didn't want to have a go at doing this yourself?

    SQL> ed
    Wrote file afiedt.buf

      1  with t as (select 'dan' as person, 'mk' as dept from dual union all
      2             select 'dan', 'it' from dual union all
      3             select 'dan', 'it_01' from dual union all
      4             select 'dan', 'it_02' from dual union all
      5             select 'ion', 'sales' from dual union all
      6             select 'ion', 'it_03' from dual union all
      7             select 'john', 'mk' from dual union all
      8             select 'john', 'mk_01' from dual union all
      9             select 'bob', 'it' from dual union all
    10             select 'stan', 'mk' from dual union all
    11             select 'stan', 'it' from dual union all
    12             select 'stan', 'off' from dual
    13            )
    14  --
    15  select person
    16  from   t
    17  group by person, substr(dept,1,2)
    18* having count(*) > 1
    SQL> /

     

    PERS
    ----
    dan
    john

     

    In future, have a go yourself, and think through the issue.  Otherwise you'll never learn SQL and will always be relying on others do it for you.

  • 10. Re: Query problem
    308aa62b-4323-4ed5-b0ba-c41579505362 Newbie
    Currently Being Moderated

    For:

     

    with t as (select 'dan' as person, 'mk' as dept from dual union all

                   select 'dan', 'it' from dual union all

                   select 'dan', 'it_01' from dual union all

                   select 'dan', 'it_02' from dual union all

                   select 'ion', 'sales' from dual union all

                  select 'ion', 'it_03' from dual union all

                   select 'john', 'mk' from dual union all

                   select 'john', 'mk_01' from dual union all

                   select 'bob', 'it_01' from dual union all

                   select 'bob', 'it_02' from dual union all

                 select 'stan', 'mk' from dual union all

                 select 'stan', 'it' from dual union all

                select 'stan', 'off' from dual

                )

     

     

    select person, substr(dept,1,2)

      from   t

      group by person, substr(dept,1,2)

    having count(*) > 1;

     

    The script returns:

    dan

    john

    bob

    Both condition 'xx' and 'xx%' should be respected.

    Bob should not be in the result list because don't have dept like 'it'.

    Sorry for misunderstanding.

  • 11. Re: Query problem
    BluShadow Guru Moderator
    Currently Being Moderated

    that's why you should be clear in your requirements from the beginning, it'll save lots of guesswork on our part and wasting our time.

     

    SQL> ed
    Wrote file afiedt.buf

      1  with t as (select 'dan' as person, 'mk' as dept from dual union all
      2             select 'dan', 'it' from dual union all
      3             select 'dan', 'it_01' from dual union all
      4             select 'dan', 'it_02' from dual union all
      5             select 'ion', 'sales' from dual union all
      6             select 'ion', 'it_03' from dual union all
      7             select 'john', 'mk' from dual union all
      8             select 'john', 'mk_01' from dual union all
      9             select 'bob', 'it_01' from dual union all
    10             select 'bob', 'it_02' from dual union all
    11             select 'stan', 'mk' from dual union all
    12             select 'stan', 'it' from dual union all
    13             select 'stan', 'off' from dual
    14            )
    15  select person, substr(dept,1,2)
    16  from   t
    17  group by person, substr(dept,1,2)
    18* having count(*) > 1 and (person, substr(dept,1,2)) in (select person, dept from t)
    SQL> /

     

    PERS SU
    ---- --
    dan  it
    john mk

  • 12. Re: Query problem
    308aa62b-4323-4ed5-b0ba-c41579505362 Newbie
    Currently Being Moderated

    Another problem: what if the table contains a duplicate row like this:

    with t as (select 'dan' as person, 'mk' as dept from dual union all

      2             select 'dan', 'it' from dual union all

      3             select 'dan', 'it_01' from dual union all

      4             select 'dan', 'it_02' from dual union all

      5             select 'ion', 'sales' from dual union all

      6             select 'ion', 'it_03' from dual union all

      7             select 'john', 'mk' from dual union all

      8             select 'john', 'mk_01' from dual union all

      9             select 'bob', 'it_01' from dual union all

    10             select 'bob', 'it_02' from dual union all

    11             select 'stan', 'mk' from dual union all

    12             select 'stan', 'it' from dual union all


    select 'stan', 'it' from dual union all--DUPLICATE ROW


    13             select 'stan', 'off' from dual

    14            )

  • 13. Re: Query problem
    BluShadow Guru Moderator
    Currently Being Moderated

    You really do need to start thinking for yourself... try things and see what you get...  Lazy programming will get you nowhere...

     

    SQL> ed
    Wrote file afiedt.buf

      1  with t as (select 'dan' as person, 'mk' as dept from dual union all
      2             select 'dan', 'it' from dual union all
      3             select 'dan', 'it_01' from dual union all
      4             select 'dan', 'it_02' from dual union all
      5             select 'ion', 'sales' from dual union all
      6             select 'ion', 'it_03' from dual union all
      7             select 'john', 'mk' from dual union all
      8             select 'john', 'mk_01' from dual union all
      9             select 'bob', 'it_01' from dual union all
    10             select 'bob', 'it_02' from dual union all
    11             select 'stan', 'mk' from dual union all
    12             select 'stan', 'it' from dual union all
    13             select 'stan', 'it' from dual union all
    14             select 'stan', 'off' from dual
    15            )
    16  select person, substr(dept,1,2)
    17  from   t
    18  group by person, substr(dept,1,2)
    19* having count(distinct dept) > 1 and (person, substr(dept,1,2)) in (select person, dept from t)
    SQL> /

    PERS SU
    ---- --
    dan  it
    john mk

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points