13 Replies Latest reply: Sep 13, 2013 4:02 AM by BluShadow RSS

    Query problem

    1039608

      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

          And your exact problem is?

          • 2. Re: Query problem
            940315

            add condition in where clause

            where location like 'AG01%'

            • 3. Re: Query problem
              BluShadow

              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
                1039608

                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

                  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

                    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

                      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
                        1039608

                        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

                          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
                            1039608

                            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

                              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
                                1039608

                                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

                                  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