Nasty grouping query

Samaritan · #1 (**permalink**) 11-15-04, 10:40

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

urquel · #2 (**permalink**) 11-15-04, 11:05

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?

Samaritan · #3 (**permalink**) 11-16-04, 06:47

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

Samaritan · #4 (**permalink**) 11-16-04, 07:40

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

Internet Services

Web Development

Digital Marketing

Consumer Tech