Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2009
    Posts
    6

    Unanswered: Simple Select query maybe?

    Hi, really hope you can help me.
    I have a table called RM_Areas with 2 columns in it, Name and Ptype. In Name there are all different names and some of the same. In Ptype there is one of the following numbers 1,2,3,4,5,6,7,8.
    What I need to do is write a select query for, if there is an identical name with a ptype of 3 and 1 or 2.

    example of table:-
    Code:
    Name		ptype
    Apple		3
    Banana		3
    Pear		3
    Banana		2
    Apple		1
    grape		3
    
    Example of result:- 
    
    Name		ptype
    Apple		3
    Banana		3
    Banana		2
    Apple		1
    Here is how i need it to look after the select query.
    It shows all names with a ptype of 3 and 1 or 2.

    I hope this is clear ?
    Thanks, any help would be greatly appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    select t1.name, t2.ptype from RM_Areas as T1
    left join RM_Areas as t2 on t1.name = t2.name and t1.ptype <> t2.ptype
    order by t1.name
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2009
    Posts
    6
    This works but for all Ptype combinations.
    I need it to show only the names that have 2 combinations of Ptypes.

    The Ptype combinations are.
    Any names what have a Ptype of 3 and 1 or 3 and 2.
    and no other combination.

    Thank you for your quick reply.

    Here is a more detailed example:-
    Code:
    example of table:-
    Code:
    Name		ptype
    Apple		3
    Banana		3
    Pear		3
    Banana		2
    Apple		1
    grape		3
    Pear            5
    Pear            7
    
    
    Example of result:- 
    
    Name		ptype
    Apple		3
    Banana		3
    Banana		2
    Apple		1

  4. #4
    Join Date
    Oct 2009
    Posts
    6
    Hi thanks for the quick reply
    This seems to work but only for duplicate names with any combination of PType.

    I would only like to see the duplicate names that have a Ptype of 3 and 1 or a Ptype of 3 and 2. No other combinations.

    Is this possible?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its all in the where clause

    Id suggest you develop a where clause to go with the above

    ...an alternative approach would be to use a sub select
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (Q1) Are there some Names with 3 or more rows?

    Like...
    Code:
    Name    ptype
    Cabbage 1
    Cabbage 2
    Cabbage 3
    Celery  1
    Celery  3
    Celery  4

    (Q2) If these were possible, which do you want?
    "Cabbage and Celery"?
    or, "Cabbage only"?
    or, "No Cabbage No Celery"?

  7. #7
    Join Date
    Oct 2009
    Posts
    6
    No there is only ever 2 rows with the same name. All names will have a ptype of 3 but the duplicate name may have a 1 or 2 or 4 or 5 or 6 or 7 or 8

    I only want to see the names that have a ptype of 3 and 1 or 3 and 2. Not 3 and 4 or 3 and 5 etc.

    Q2 ill give another example

    Code:
    Name       Ptype          Disabled
    Cabbage      3              False
    Cabbage      1              False
    Celery       3              False
    Celery       2              False
    Radish       3              False 
    Radish       4              False
    Potato       3              False 
    Potato       6              False
    I only want it to display this

    Code:
    Name       Ptype          Disabled
    Cabbage      3              False
    Cabbage      1              False
    Celery       3              False
    Celery       2              False
    I need this query so i can change the disabled column fields from false to true for all that the query returns.

    Does this answer up your questions?
    Thanks for your help

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about these examples?

    Example 1:
    Code:
    SELECT Name , ptype
     FROM  RM_Areas AS r1
     WHERE EXISTS
           (SELECT 0
             FROM  RM_Areas AS r2
             WHERE r2.Name = r1.Name
              AND
              (    r1.ptype = 3
               AND r2.ptype IN (1 , 2)
               OR  r1.ptype IN (1 , 2)
               AND r2.ptype = 3
              )
           )
    ;

    Example 2:
    Code:
    SELECT Name , ptype
     FROM  (SELECT r.*
                 , MIN(ptype) OVER(PARTITION BY Name) AS min_ptype
                 , MAX(ptype) OVER(PARTITION BY Name) AS max_ptype
             FROM  RM_Areas AS r
           ) AS s
     WHERE min_ptype IN (1 , 2)
      AND  max_ptype = 3
    ;

    If considering "All names will have a ptype of 3",
    Example 1 or 2 may be simplified.

    Example 3:
    Code:
    SELECT Name , ptype
     FROM  RM_Areas AS r1
     WHERE EXISTS
           (SELECT 0
             FROM  RM_Areas AS r2
             WHERE r2.Name = r1.Name
              AND
              (    r2.ptype IN (1 , 2)
               OR  r1.ptype IN (1 , 2)
              )
           )
    ;

    Example 4:
    Code:
    SELECT Name , ptype
     FROM  (SELECT r.*
                 , MIN(ptype) OVER(PARTITION BY Name) AS min_ptype
             FROM  RM_Areas AS r
           ) AS s
     WHERE min_ptype IN (1 , 2)
    ;

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 5:
    Code:
    WITH
     selected AS (
    SELECT Name , ptype
     FROM  RM_Areas
     WHERE ptype IN (1 , 2)
    )
    SELECT s.Name , s.ptype
     FROM  selected AS s
    UNION ALL
    SELECT r.Name , r.ptype
     FROM  RM_Areas AS r
     INNER JOIN
           selected AS s
      ON   r.Name  = s.Name
      AND  r.ptype = 3
    ;

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 6:
    Code:
    SELECT r2.Name , r2.ptype
     FROM  RM_Areas AS r1
     INNER JOIN
           RM_Areas AS r2
      ON   r2.Name = r1.Name
     WHERE r1.ptype IN (1 , 2)
    ;

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 7:
    Code:
    SELECT Name , ptype
     FROM  RM_Areas
     WHERE Name
           IN (SELECT Name
                FROM  RM_Areas
                WHERE ptype IN (1 , 2)
              )
    ;

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    A little improved/shorter version of Example 3.

    Example 3a:
    Code:
    SELECT Name , ptype
     FROM  RM_Areas AS r1
     WHERE EXISTS
           (SELECT 0
             FROM  RM_Areas AS r2
             WHERE r2.Name = r1.Name
              AND  r2.ptype IN (1 , 2)
           )
    ;

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Example 8:

    Code:
    SELECT Name , ptype
     FROM  (SELECT r.*
                 , ROW_NUMBER() OVER(PARTITION BY Name
                                         ORDER BY ptype ASC) AS rn
             FROM  RM_Areas AS r
           ) AS s
     WHERE ptype - rn <= 1
    ;

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Directlinq,

    Had my examples worked?

    If some examples had not worked(error message(s) and/or incorrect output were returned),
    please show your test data, executed query and the result you got from the query(and the data).
    Last edited by tonkuma; 08-11-14 at 17:27.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •