Forum Stats

  • 3,733,256 Users
  • 2,246,740 Discussions
  • 7,856,637 Comments

Discussions

Help in generating the query

702934
702934 Member Posts: 26
edited November 2010 in SQL & PL/SQL
Hi All,

I have the below requirement.
Master table:
Product ID 	Product Name
1			TV
2			Cellphone
3			Laptop
4			DVD Player

Details table:
Product ID	Spec type	Spec Detail
1			Color		Blue
1			Weight		20
2			Color		Blue
2			Weight		20
3			Color		Blue
3 			Weight		25
4			Color		Blue
4			Weight		20
4			Height		10
Im looking for a way to identify the products that has exact same specifications.

ie., in the above example TV and Cellphone has the exact specifications.
So the output of the query should be TV and Cellphone here.

Is it possible to do in a query or we need to have some sql block to perform this action.

Any help would be greatly appreciated.

Thanks,
Agathya

Best Answer

  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    Accepted Answer
    Hi agathya,

    We don't know how you would like to handle the situation where more than two products have the same specifications, so I extended your sample data a bit to reflect that situation as well:
    SQL> create table products (id,name)
      2  as
      3  select 1, 'TV' from dual union all
      4  select 2, 'Cellphone' from dual union all
      5  select 3, 'Laptop' from dual union all
      6  select 4, 'DVD Player' from dual union all
      7  select 5, 'Radio' from dual union all
      8  select 6, 'VCR' from dual
      9  /
    
    Tabel is aangemaakt.
    
    SQL> create table product_specifications (product_id,spec_type,spec_detail)
      2  as
      3  select 1, 'Color' , 'Blue' from dual union all
      4  select 1, 'Weight', '20'   from dual union all
      5  select 2, 'Color' , 'Blue' from dual union all
      6  select 2, 'Weight', '20'   from dual union all
      7  select 3, 'Color' , 'Blue' from dual union all
      8  select 3, 'Weight', '25'   from dual union all
      9  select 4, 'Color' , 'Blue' from dual union all
     10  select 4, 'Weight', '20'   from dual union all
     11  select 4, 'Height', '10'   from dual union all
     12  select 5, 'Color' , 'Blue' from dual union all
     13  select 5, 'Weight', '20'   from dual union all
     14  select 6, 'Color' , 'Blue' from dual union all
     15  select 6, 'Weight', '20'   from dual union all
     16  select 6, 'Height', '10'   from dual
     17  /
    
    Tabel is aangemaakt.
    
    SQL> select p1.name
      2       , p2.name
      3    from products p1
      4       , products p2
      5       , product_specifications ps1
      6       , product_specifications ps2
      7   where p1.id = ps1.product_id
      8     and p2.id = ps2.product_id
      9     and p1.id < p2.id
     10   group by p1.id
     11       , p1.name
     12       , p2.id
     13       , p2.name
     14  having sqrt(count(*)) =
     15         count(case when ps1.spec_type = ps2.spec_type and ps1.spec_detail = ps2.spec_detail then 1 end)
     16  /
    
    NAME       NAME
    ---------- ----------
    TV         Cellphone
    TV         Radio
    Cellphone  Radio
    DVD Player VCR
    
    4 rijen zijn geselecteerd.
    But why on earth did you or your predecessor model product specifications like this? You are making it far more complicated than it needs to be. And the datatype of height and weight should not be the same as for color. But now they are. And how do you restrict the allowable values for colors? Color, weight and height are attributes of your products so they should have been modeled like that.

    See how easy your questions becomes after remodeling it:
    SQL> drop table product_specifications purge
      2  /
    
    Tabel is verwijderd.
    
    SQL> drop table products purge
      2  /
    
    Tabel is verwijderd.
    
    SQL> create table products (id,name,color,weight,height)
      2  as
      3  select 1, 'TV', 'Blue', 20, null from dual union all
      4  select 2, 'Cellphone', 'Blue', 20, null from dual union all
      5  select 3, 'Laptop', 'Blue', 25, null from dual union all
      6  select 4, 'DVD Player', 'Blue', 20, 10 from dual union all
      7  select 5, 'Radio', 'Blue', 20, null from dual union all
      8  select 6, 'VCR', 'Blue', 20, 10 from dual
      9  /
    
    Tabel is aangemaakt.
    
    SQL> select * from products
      2  /
    
            ID NAME       COLO     WEIGHT     HEIGHT
    ---------- ---------- ---- ---------- ----------
             1 TV         Blue         20
             2 Cellphone  Blue         20
             3 Laptop     Blue         25
             4 DVD Player Blue         20         10
             5 Radio      Blue         20
             6 VCR        Blue         20         10
    
    6 rijen zijn geselecteerd.
    
    SQL> select p1.name
      2       , p2.name
      3    from products p1
      4       , products p2
      5   where p1.id < p2.id
      6     and ( p1.color = p2.color or (p1.color is null and p2.color is null))
      7     and ( p1.weight = p2.weight or (p1.weight is null and p2.weight is null))
      8     and ( p1.height = p2.height or (p1.height is null and p2.height is null))
      9  /
    
    NAME       NAME
    ---------- ----------
    TV         Cellphone
    TV         Radio
    Cellphone  Radio
    DVD Player VCR
    
    4 rijen zijn geselecteerd.
    Hope this helps.

    Regards,
    Rob.

Answers

  • ravikumar.sv
    ravikumar.sv Member Posts: 1,072
    what if there is new product 5 say...palmtop which has specs same as for laptop....then do u want the o/p as

    TV,cellphone,laptop,palmtop...??

    then how are going to distinguish which among two have same specifications...??

    Ravi Kumar
  • 488142
    488142 Member Posts: 18
    Hi,

    Check this Query...
    select p.* from master p,
    (select distinct dt.productid from details,
    
    (select t.spectype,t.specdetail from details t group by t.spectype,t.specdetail 
    having count(distinct t.productid) >1) temp
    where dt.spectype = temp.spectype
    and dt.specdetail= temp.specdetail)   D
    where p.productid = d.productid
    hope this will give the required result for you.

    Regards,
    S.Dinesh
  • 702934
    702934 Member Posts: 26
    >
    what if there is new product 5 say...palmtop which has specs same as for laptop....then do u want the o/p as

    TV,cellphone,laptop,palmtop...??
    >

    Yes. I need the list of distinct product that has the exact number of specifications as well as the exact value in the specifications.

    As per your above example, palmtop will also be listed.

    Thanks,
    Agathya
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    How about this
    with master
    as
    ( 
    select 1 prod_id,'TV' prod_name from dual union all
    select 2,'Cellphone' from dual union all
    select 3,'Laptop' from dual union all
    select 4,'DVD Player' from dual
    ),
    detail
    as
    (
    select 1 prod_id, 'Color' spec_type,'Blue' spec_detail from dual union all
    select 1, 'Weight','20' from dual union all
    select 2, 'Color','Blue' from dual union all
    select 2, 'Weight','20' from dual union all
    select 3, 'Color','Blue' from dual union all
    select 3, 'Weight','25' from dual union all
    select 4, 'Color','Blue' from dual union all
    select 4, 'Weight','20' from dual union all
    select 4, 'Height','10' from dual 
    ),
    detail_1
    as
    (
    select prod_id, spec_type, spec_detail, count(*) over(partition by prod_id) cnt
      from detail
    )
    select t1.prod_id, prod_name, spec_type, spec_detail
      from (
    select t.*, count(*) over(partition by t.prod_id) cnt1
      from (
    select distinct x.*
      from detail_1 x
      join detail_1 y
        on x.prod_id<>y.prod_id
       and x.spec_type = y.spec_type
       and x.spec_detail = y.spec_detail
       and x.cnt = y.cnt) t) t1
      join master m
        on t1.prod_id = m.prod_id
      where cnt = cnt1
    order by t1.prod_id
  • ravikumar.sv
    ravikumar.sv Member Posts: 1,072
    edited August 2009
    how about this.....
    with mstr as (
    select 1 prdtid,'TV' prdtname from dual union all
    select 2 prdtid,'Cellphone' prdtname from dual union all
    select 3 prdtid,'Laptop' prdtname from dual union all
    select 4 prdtid,'DVD Player' prdtname from dual union all
    select 5 prdtid,'Palmtop' prdtname from dual
    ),details as (
    select 1 prdtid,'Color' spectype,'Blue' specdetail from dual union all
    select 1 prdtid,'Weight' spectype,'20' specdetail from dual union all
    select 2 prdtid,'Color' spectype,'Blue' specdetail from dual union all
    select 2 prdtid,'Weight' spectype,'20' specdetail from dual union all
    select 3 prdtid,'Color' spectype,'Blue' specdetail from dual union all
    select 3 prdtid,'Weight' spectype,'25' specdetail from dual union all
    select 4 prdtid,'Color' spectype,'Blue' specdetail from dual union all
    select 4 prdtid,'Weight' spectype,'20' specdetail from dual union all
    select 4 prdtid,'Height' spectype,'10' specdetail from dual union all
    select 5 prdtid,'Color' spectype,'Blue' specdetail from dual union all
    select 5 prdtid,'Weight' spectype,'25' specdetail from dual 
    ),--end of data
    temptab as (
    select distinct prdtid1,prdtid2 from (
    select dt1.prdtid prdtid1,dt2.prdtid prdtid2,case when count(*) over (partition by dt2.prdtid,dt1.prdtid) = dt1.cnt then 'X' end finalchk from (select dt.*,count(*) over (partition by prdtid) cnt from details dt) dt1,(select dt.*,count(*) over (partition by prdtid) cnt from details dt) dt2 where dt1.spectype=dt2.spectype and dt1.specdetail=dt2.specdetail and dt1.prdtid>dt2.prdtid and dt1.cnt = dt2.cnt
    ) where finalchk='X'
    )
    select prdtid,prdtname from mstr where prdtid in (select prdtid1 from temptab union select prdtid2 from temptab)
    Ravi Kumar
    ravikumar.sv
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    Accepted Answer
    Hi agathya,

    We don't know how you would like to handle the situation where more than two products have the same specifications, so I extended your sample data a bit to reflect that situation as well:
    SQL> create table products (id,name)
      2  as
      3  select 1, 'TV' from dual union all
      4  select 2, 'Cellphone' from dual union all
      5  select 3, 'Laptop' from dual union all
      6  select 4, 'DVD Player' from dual union all
      7  select 5, 'Radio' from dual union all
      8  select 6, 'VCR' from dual
      9  /
    
    Tabel is aangemaakt.
    
    SQL> create table product_specifications (product_id,spec_type,spec_detail)
      2  as
      3  select 1, 'Color' , 'Blue' from dual union all
      4  select 1, 'Weight', '20'   from dual union all
      5  select 2, 'Color' , 'Blue' from dual union all
      6  select 2, 'Weight', '20'   from dual union all
      7  select 3, 'Color' , 'Blue' from dual union all
      8  select 3, 'Weight', '25'   from dual union all
      9  select 4, 'Color' , 'Blue' from dual union all
     10  select 4, 'Weight', '20'   from dual union all
     11  select 4, 'Height', '10'   from dual union all
     12  select 5, 'Color' , 'Blue' from dual union all
     13  select 5, 'Weight', '20'   from dual union all
     14  select 6, 'Color' , 'Blue' from dual union all
     15  select 6, 'Weight', '20'   from dual union all
     16  select 6, 'Height', '10'   from dual
     17  /
    
    Tabel is aangemaakt.
    
    SQL> select p1.name
      2       , p2.name
      3    from products p1
      4       , products p2
      5       , product_specifications ps1
      6       , product_specifications ps2
      7   where p1.id = ps1.product_id
      8     and p2.id = ps2.product_id
      9     and p1.id < p2.id
     10   group by p1.id
     11       , p1.name
     12       , p2.id
     13       , p2.name
     14  having sqrt(count(*)) =
     15         count(case when ps1.spec_type = ps2.spec_type and ps1.spec_detail = ps2.spec_detail then 1 end)
     16  /
    
    NAME       NAME
    ---------- ----------
    TV         Cellphone
    TV         Radio
    Cellphone  Radio
    DVD Player VCR
    
    4 rijen zijn geselecteerd.
    But why on earth did you or your predecessor model product specifications like this? You are making it far more complicated than it needs to be. And the datatype of height and weight should not be the same as for color. But now they are. And how do you restrict the allowable values for colors? Color, weight and height are attributes of your products so they should have been modeled like that.

    See how easy your questions becomes after remodeling it:
    SQL> drop table product_specifications purge
      2  /
    
    Tabel is verwijderd.
    
    SQL> drop table products purge
      2  /
    
    Tabel is verwijderd.
    
    SQL> create table products (id,name,color,weight,height)
      2  as
      3  select 1, 'TV', 'Blue', 20, null from dual union all
      4  select 2, 'Cellphone', 'Blue', 20, null from dual union all
      5  select 3, 'Laptop', 'Blue', 25, null from dual union all
      6  select 4, 'DVD Player', 'Blue', 20, 10 from dual union all
      7  select 5, 'Radio', 'Blue', 20, null from dual union all
      8  select 6, 'VCR', 'Blue', 20, 10 from dual
      9  /
    
    Tabel is aangemaakt.
    
    SQL> select * from products
      2  /
    
            ID NAME       COLO     WEIGHT     HEIGHT
    ---------- ---------- ---- ---------- ----------
             1 TV         Blue         20
             2 Cellphone  Blue         20
             3 Laptop     Blue         25
             4 DVD Player Blue         20         10
             5 Radio      Blue         20
             6 VCR        Blue         20         10
    
    6 rijen zijn geselecteerd.
    
    SQL> select p1.name
      2       , p2.name
      3    from products p1
      4       , products p2
      5   where p1.id < p2.id
      6     and ( p1.color = p2.color or (p1.color is null and p2.color is null))
      7     and ( p1.weight = p2.weight or (p1.weight is null and p2.weight is null))
      8     and ( p1.height = p2.height or (p1.height is null and p2.height is null))
      9  /
    
    NAME       NAME
    ---------- ----------
    TV         Cellphone
    TV         Radio
    Cellphone  Radio
    DVD Player VCR
    
    4 rijen zijn geselecteerd.
    Hope this helps.

    Regards,
    Rob.
  • 702934
    702934 Member Posts: 26
    Hi Ravi,

    Thanks so much. I got the desired result.
  • 702934
    702934 Member Posts: 26
    edited August 2009
    Hi Rob,

    Your query works great and I got the required result.
    Thanks very mcuh.

    It will be of great help, if you could explain the query, especially the
    having clause 
    .
    Thanks in advance.

    Edited by: agathya on Aug 27, 2009 4:18 AM
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    edited August 2009
    agathya wrote:
    It will be of great help, if you could explain the query, especially the having clause.
    If you omit this clause, and place its expressions in the select list, you can see what happens for yourself.

    In short, all combinations of two product specifications within two products are compared to each other. For example products 4 and 6 both have 3 product specifications. All possible combinations here are 9 rows in total. If sqrt(9) equals the number of matching product specifications, then it is a record we are interested in.

    Regards,
    Rob.
  • 702934
    702934 Member Posts: 26
    Hi Rob,

    >

    NAME NAME
    ---------- ----------
    TV Cellphone
    TV Radio
    Cellphone Radio
    DVD Player VCR
    In the above mentioned result, the first two rows describes Cellphone and Radio has same configurations as TV. The third row stating Cellphone and Radio has same configuration is a duplicate entry.
    Will be possible to handle this filter as well in the query. So that the o/p will be:

    >

    NAME NAME
    ---------- ----------
    TV Cellphone
    TV Radio
    DVD Player VCR
    All the Color, Weight, Height, Product concept is the example scenario of our existing production database. Hence it is impossible to change the table structure as suggested by you in ,
    SQL> drop table product_specifications purge
      2  / 
     
    Tabel is verwijderd.
     
    SQL> drop table products purge
      2  / 
     
    Tabel is verwijderd.
     
    SQL> create table products (id,name,color,weight,height)
      2  as
      3  select 1, 'TV', 'Blue', 20, null from dual union all
      4  select 2, 'Cellphone', 'Blue', 20, null from dual union all
      5  select 3, 'Laptop', 'Blue', 25, null from dual union all
      6  select 4, 'DVD Player', 'Blue', 20, 10 from dual union all
      7  select 5, 'Radio', 'Blue', 20, null from dual union all
      8  select 6, 'VCR', 'Blue', 20, 10 from dual
      9  / 
     
    Tabel is aangemaakt.
     
    SQL> select * from products
      2  / 
     
            ID NAME       COLO     WEIGHT     HEIGHT
    ---------- ---------- ---- ---------- ----------
             1 TV         Blue         20
             2 Cellphone  Blue         20
             3 Laptop     Blue         25
             4 DVD Player Blue         20         10
             5 Radio      Blue         20
             6 VCR        Blue         20         10
     
    6 rijen zijn geselecteerd.
     
    SQL> select p1.name
      2       , p2.name
      3    from products p1
      4       , products p2
      5   where p1.id < p2.id
      6     and ( p1.color = p2.color or (p1.color is null and p2.color is null))
      7     and ( p1.weight = p2.weight or (p1.weight is null and p2.weight is null))
      8     and ( p1.height = p2.height or (p1.height is null and p2.height is null))
      9  / 
     
    NAME       NAME
    ---------- ----------
    TV         Cellphone
    TV         Radio
    Cellphone  Radio
    DVD Player VCR
     
    4 rijen zijn geselecteerd.
    Thanks in advance.
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    Hi agathya,

    I interpreted your request to filter out the "duplicate" as a request to filter out any row that can be marked as a chain of products:
    SQL> with t as
      2  ( select p1.id   p1_id
      3         , p2.id   p2_id
      4         , p1.name p1_name
      5         , p2.name p2_name
      6      from products p1
      7         , products p2
      8         , product_specifications ps1
      9         , product_specifications ps2
     10     where p1.id = ps1.product_id
     11       and p2.id = ps2.product_id
     12       and p1.id < p2.id
     13     group by p1.id
     14         , p1.name
     15         , p2.id
     16         , p2.name
     17    having sqrt(count(*)) =
     18           count(case when ps1.spec_type = ps2.spec_type and ps1.spec_detail = ps2.spec_detail then 1 end)
     19  )
     20  select p1_name
     21       , p2_name
     22    from t
     23   where not exists
     24         ( select 'a chain of products'
     25             from t t2
     26            where t2.p2_id = t.p1_id
     27         )
     28  /
    
    P1_NAME    P2_NAME
    ---------- ----------
    TV         Radio
    TV         Cellphone
    DVD Player VCR
    
    3 rijen zijn geselecteerd.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited November 2010
    create table products (id,name,color,weight,height) as
    select 1, 'TV', 'Blue', 20, null from dual union all
    select 2, 'Cellphone', 'Blue', 20, null from dual union all
    select 3, 'Laptop', 'Blue', 25, null from dual union all
    select 4, 'DVD Player', 'Blue', 20, 10 from dual union all
    select 5, 'Radio', 'Blue', 20, null from dual union all
    select 6, 'VCR', 'Blue', 20, 10 from dual;
    
    create table product_specifications (product_id,spec_type,spec_detail) as
    select 1, 'Color' , 'Blue' from dual union all
    select 1, 'Weight', '20'   from dual union all
    select 2, 'Color' , 'Blue' from dual union all
    select 2, 'Weight', '20'   from dual union all
    select 3, 'Color' , 'Blue' from dual union all
    select 3, 'Weight', '25'   from dual union all
    select 4, 'Color' , 'Blue' from dual union all
    select 4, 'Weight', '20'   from dual union all
    select 4, 'Height', '10'   from dual union all
    select 5, 'Color' , 'Blue' from dual union all
    select 5, 'Weight', '20'   from dual union all
    select 6, 'Color' , 'Blue' from dual union all
    select 6, 'Weight', '20'   from dual union all
    select 6, 'Height', '10'   from dual;
    
    with work as(
    select a.id,a.name,b.spec_type,b.spec_detail
      from products a,product_specifications b
     where a.id = b.product_id)
    select a.name,b.name
      from work a,work b
     where a.id < b.id
    group by a.id,a.name,b.ID,b.name
    having count(case when a.spec_type   = b.spec_type
                       and a.spec_detail = b.spec_detail
                      then 1 end)
    = all(-1+Dense_Rank(null,null) within group(order by a.spec_type,a.spec_detail),
          -1+Dense_Rank(null,null) within group(order by b.spec_type,b.spec_detail));
    
    NAME        NAME
    ----------  ---------
    TV          Cellphone
    TV          Radio
    Cellphone   Radio
    DVD Player  VCR
    My homepage ;-)
    http://www.geocities.jp/oraclesqlpuzzle/9-56.html
    http://www.geocities.jp/oraclesqlpuzzle/9-57.html
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    There is more simple solution B-)
    with work as(
    select a.id,a.name,b.spec_type,b.spec_detail,
    count(*) over(partition by a.id) as cnt
      from products a,product_specifications b
     where a.id = b.product_id)
    select a.name,b.name
      from work a,work b
     where a.id < b.id
    group by a.id,a.name,b.ID,b.name,a.cnt,b.cnt
    having count(case when a.spec_type   = b.spec_type
                       and a.spec_detail = b.spec_detail
                      then 1 end) = all(a.cnt,b.cnt)
    order by a.id,b.ID;
    
    NAME        NAME
    ----------  ---------
    TV          Cellphone
    TV          Radio
    Cellphone   Radio
    DVD Player  VCR
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    OOPS
    There is more simple solution.
    Although maybe I will find more simple solution.
    with work as(
    select a.id,a.name,b.spec_type,b.spec_detail,
    count(*) over(partition by a.id) as cnt
      from products a,product_specifications b
     where a.id = b.product_id)
    select a.name,b.name
      from work a,work b
     where a.id < b.id
       and a.cnt = b.cnt
       and a.spec_type   = b.spec_type
       and a.spec_detail = b.spec_detail
    group by a.id,a.name,b.ID,b.name,a.cnt
    having count(*) = a.cnt
    order by a.ID,b.ID;
    
    NAME        NAME
    ----------  ---------
    TV          Cellphone
    TV          Radio
    Cellphone   Radio
    DVD Player  VCR
This discussion has been closed.