Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Question Unanswered: Nasty grouping query

    Hi all.

    I'm having trouble with a query and was hoping someone could help me.

    I have two tables that JOIN on t_number and usi_system_ref. There can be many entries in table2 to the one entry in table1. The usi_category is basically a type of entry. I am trying to do a query that will give me all of the entries grouped by usi_system_ref (or t_number) where the usi_category only equals 3289 for all of the entries, ie it has only ever had this one type of entry put into it.

    The query below does not work, but to me looks like it should. Any bright ideas?

    Thanks

    Samaritan


    Code:
    SELECT     table1.t_reference, table2.usi_category, table1.t_cancelled
    FROM         table1 ,table2
    WHERE     table1.t_number IN
                              (SELECT     usi_system_ref
                                FROM          table2
                                WHERE      usi_category = 3289
    				AND usi_category NOT IN (3291, 3292, 3293, 14072, 14074, 23536, 23537, 28831, 40589, 40590, 40592)
                                GROUP BY usi_system_ref)
    and t_number = usi_system_ref 
    ORDER BY usi_system_ref, usi_category desc
    Last edited by Samaritan; 11-16-04 at 07:50.

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    I don't see any join predicate. (table1.colum = table2.column) Also, I don't understand the "table1 = usi_system_ref " is that a column or a table or what?

  3. #3
    Join Date
    Nov 2004
    Posts
    4
    Oh, sorry - let me put that in again, should have been "t_number = usi_system_ref"

    Thanks


    Code:
    SELECT     table1.t_reference, table2.usi_category, table1.t_cancelled
    FROM         table1 ,table2
    WHERE     table1.t_number IN
                              (SELECT     usi_system_ref
                                FROM          table2
                                WHERE      usi_category = 3289
    				AND usi_category NOT IN (3291, 3292, 3293, 14072, 14074, 23536, 23537, 28831, 40589, 40590, 40592)
                                GROUP BY usi_system_ref)
    and t_number = usi_system_ref 
    ORDER BY usi_system_ref, usi_category desc

  4. #4
    Join Date
    Nov 2004
    Posts
    4
    Ah, got it.

    this seems to work for me. Thanks

    Code:
    SELECT     tickets.t_reference, user_scanned_images.usi_category
    FROM         tickets ,user_scanned_images
    WHERE     tickets.t_number IN
                              (SELECT     usi_system_ref
                                FROM          user_scanned_images
                                WHERE      usi_system_ref NOT IN (SELECT DISTINCT(usi_system_ref)
                                				 FROM   user_scanned_images
                                				 WHERE  usi_category = 3291)
    				AND usi_system_ref NOT IN (SELECT DISTINCT(usi_system_ref)
                                				 FROM   user_scanned_images
                                				 WHERE  usi_category = 3292)
    				AND usi_system_ref NOT IN (SELECT DISTINCT(usi_system_ref)
                                				 FROM   user_scanned_images
                                				 WHERE  usi_category = 3293)
    				AND usi_system_ref NOT IN (SELECT DISTINCT(usi_system_ref)
                                				 FROM   user_scanned_images
                                				 WHERE  usi_category = 14072)
    				AND usi_system_ref NOT IN (SELECT DISTINCT(usi_system_ref)
                                				 FROM   user_scanned_images
                                				 WHERE  usi_category = 14074)
    				AND usi_system_ref NOT IN (SELECT DISTINCT(usi_system_ref)
                                				 FROM   user_scanned_images
                                				 WHERE  usi_category = 23536)
    				AND usi_system_ref NOT IN (SELECT DISTINCT(usi_system_ref)
                                				 FROM   user_scanned_images
                                				 WHERE  usi_category = 23537)
    				AND usi_system_ref NOT IN (SELECT DISTINCT(usi_system_ref)
                                				 FROM   user_scanned_images
                                				 WHERE  usi_category = 28831)
    				AND usi_system_ref NOT IN (SELECT DISTINCT(usi_system_ref)
                                				 FROM   user_scanned_images
                                				 WHERE  usi_category = 40589)
    				AND usi_system_ref NOT IN (SELECT DISTINCT(usi_system_ref)
                                				 FROM   user_scanned_images
                                				 WHERE  usi_category = 40590)
    				AND usi_system_ref NOT IN (SELECT DISTINCT(usi_system_ref)
                                				 FROM   user_scanned_images
                                				 WHERE  usi_category = 40592)
                                GROUP BY usi_system_ref)
    and t_number = usi_system_ref
    order by t_reference

Posting Permissions

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