#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    4
    Rep Power
    0

    Need help on a query modelling


    Hi folks,
    I need your suggestion in modelling a query:

    I have a table 'Symbol' wih columns as below:

    |id | Name | ndx | Version | Type | rpm_id |

    And I have a list of rpm_id_list = [1,2,3,4,5,6,....]

    I am forming a query with following meaning:
    'SELECT all rows FROM Symbol, WHERE ndx <> 'UNDEF' AND rpm_id IN $rpm_id_list
    HAVING same Name AND Version AND Type '

    In above query $rpm_id_list is a variable having list of rpm_id to search for.

    I think GROUP BY clause is the solution but I am unable to form query retrieving all rows that have same Name+Version+Type, I managed to retrieve rows having same
    searchname.

    I tried:
    [MYSQL]SELECT *, count(*) FROM Symbol WHERE rpm_id IN (1,2,3,4,5,6) AND ndx <> 'UNDEF'
    GROUP BY Name HAVING COUNT(*) > 1
    [/MYSQL]

    This query is retrieving rows having same Name, however I want combination of
    Name+Version+Type, which are unique.

    Any suggestion/reply is much appreciated.
    Thank you.
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    the first part doesn't really make sense to me ...

    Should ndx be 'UNDEF' or should it not be 'UNDEF'? What's the COUNT(*) > 1 supposed to do?

    OK, I'll assume you want all unique (Name, Version, Type) combinations for which rpm_id is in (1,2,3,4,5,6) and ndx is not 'UNDEF':

    Code:
    SELECT DISTINCT
    	Name
    	, Version
    	, Type
    FROM
    	Symbol
    WHERE
    	ndx <> 'UNDEF'
    	AND rpm_id IN (1, 2, 3, 4, 5, 6)
    ;
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    since you want to retrieve the actual rows, you need to join to a subquery
    Code:
    SELECT Symbol.id
         , Symbol.Name
         , Symbol.ndx
         , Symbol.Version
         , Symbol.Type
         , Symbol.rpm_id 
      FROM ( SELECT Name
                  , Version
                  , Type
               FROM Symbol
              WHERE ndx <> 'UNDEF'
                AND rpm_id IN (1,2,3,4,5,6)
             GROUP
                 BY Name
                  , Version
                  , Type
             HAVING COUNT(*) > 1 ) AS dupes
    INNER
      JOIN Symbol
        ON Symbol.Name    = dupes.Name   
       AND Symbol.Version = dupes.Version
       AND Symbol.Type    = dupes.Type
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by Jacques1
    Hi,

    the first part doesn't really make sense to me ...

    Should ndx be 'UNDEF' or should it not be 'UNDEF'? What's the COUNT(*) > 1 supposed to do?

    OK, I'll assume you want all unique (Name, Version, Type) combinations for which rpm_id is in (1,2,3,4,5,6) and ndx is not 'UNDEF':

    Code:
    SELECT DISTINCT
    	Name
    	, Version
    	, Type
    FROM
    	Symbol
    WHERE
    	ndx <> 'UNDEF'
    	AND rpm_id IN (1, 2, 3, 4, 5, 6)
    ;
    Apologize for the typo, I modified the question. I wanted ndx <> 'UNDEF'.

    I want to query all instances which have Name+Version+Type combination of values repeated and also there rpm_ids are in (1,2,3,4,5,6).

    I hope now you get that!
    If not, the query in reply from r937 should make you understand it.

    Thank you for response.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by r937
    since you want to retrieve the actual rows, you need to join to a subquery
    Code:
    SELECT Symbol.id
         , Symbol.Name
         , Symbol.ndx
         , Symbol.Version
         , Symbol.Type
         , Symbol.rpm_id 
      FROM ( SELECT Name
                  , Version
                  , Type
               FROM Symbol
              WHERE ndx <> 'UNDEF'
                AND rpm_id IN (1,2,3,4,5,6)
             GROUP
                 BY Name
                  , Version
                  , Type
             HAVING COUNT(*) > 1 ) AS dupes
    INNER
      JOIN Symbol
        ON Symbol.Name    = dupes.Name   
       AND Symbol.Version = dupes.Version
       AND Symbol.Type    = dupes.Type
    @937:
    Wow..!
    In question I put rpm_ids = (1,2,3,4,5,6)
    But actually I am having this list of 9000 elements.
    I am running SQL from python.
    So I have properly formatted the SQL parametrized query.

    I have ran the query you specified, it is taking lots of execution time.
    I am waiting for result, but cant wait to appreciate your effort!

    And yep, I will go through the details of your book.

    Thank you Rudy.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    Originally Posted by swordphilic
    I have ran the query you specified, it is taking lots of execution time.
    do a SHOW CREATE TABLE so we can see the indexes you have defined

    also, 9000 inline values is a bit much... can you load them into another table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by swordphilic
    I hope now you get that!
    If not, the query in reply from r937 should make you understand it.
    *lol*

    If next time you manage to phrase a coherent specification without an error in every second sentence, I think I'll understand it very well.

    So I guess "unique" was supposed to mean "not unique" (just like the "=" was in fact a "<>").

    How about something like this:?

    "I want all rows for which (Name, Version, Type) occurs multiple times in the table and ndx is not 'UNDEF' and rpm_id is in $rpm_id_list."
    Last edited by Jacques1; March 22nd, 2013 at 10:46 AM.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    Originally Posted by Jacques1
    If next time you manage to phrase a coherent specification without an error in every second sentence, I think I'll understand it very well.
    I didn't have any difficulty understanding his original post

    lol
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    You didn't actually understand it, you just went straight to the query snippet and ignored all the rest -- but obviously that helped.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by Jacques1
    *lol*

    So I guess "unique" was supposed to mean "not unique" (just like the "=" was in fact a "<>").

    How about something like this:?

    "I want all rows for which (Name, Version, Type) occurs multiple times in the table and ndx is not 'UNDEF' and rpm_id is in $rpm_id_list."
    I admit, there was mistake while writing, I edited as well.

    Yeah, you wrote it exactly what I meant!
    Thank you.

IMN logo majestic logo threadwatch logo seochat tools logo