Using a nested group by statement or sub query to filter this result sets

This question is a continuation of Changing this query to group rows and filter out all rows apart from the one with smallest value but with an extra bit at the end....

I have the following results set:

275     72.87368055555555555555555555555555555556   foo    70
275     72.87390046296296296296296296296296296296   foo    90 
113     77.06431712962962962962962962962962962963   foo    80
113     77.07185185185185185185185185185185185185   foo    60 

that I got from this query:

SELECT id, (tbl2.date_modified - tbl1.date_submitted)/86400, some_value
FROM tbl1, tbl2, tbl3
WHERE tbl1.id = tbl2.fid 
AND tbl1.id = tbl3.fid

Notice there are 4 rows with 2 ids. I wanted to filter the rows to get only the minimum number in the second column. This fixed it:

SELECT id, min((tbl2.date_modified - tbl1.date_submitted)/86400), max(some_value)
FROM tbl1, tbl2, tbl3
WHERE tbl1.id = tbl2.fid 
AND tbl1.id = tbl3.fid
GROUP BY tbl1.id

so I got:

275     72.87368055555555555555555555555555555556   foo    70
113     77.06431712962962962962962962962962962963   foo    80

How can I change it to do the same but not include rows where the are other rows with some_value=90 ? I.e.

113     77.06431712962962962962962962962962962963   foo    80

I think I need some nested group or nested query ?!

Many thanks :).

Answers


You should be able to use NOT EXISTS:

SELECT id, 
  min((tbl2.date_modified - tbl1.date_submitted)/86400), 
  max(some_value)
FROM tbl1, tbl2, tbl3
WHERE tbl1.id = tbl2.fid 
  AND tbl1.id = tbl3.fid
  AND NOT EXISTS (SELECT id 
                  FROM tbl2 
                  WHERE tbl1.id = tbl2.fid
                    AND some_value = 90)
GROUP BY tbl1.id

Or using ANSI join syntax:

SELECT id, 
  min((tbl2.date_modified - tbl1.date_submitted)/86400), 
  max(some_value)
FROM tbl1
INNER JOIN tbl2
   ON tbl1.id = tbl2.fid 
INNER JOIN tbl3
   ON tbl1.id = tbl3.fid
WHERE NOT EXISTS (SELECT id 
                  FROM tbl2 
                  WHERE tbl1.id = tbl2.fid
                    AND some_value = 90)
GROUP BY tbl1.id

First, you should write your query using standard JOIN syntax. If you just want to exclude rows with some_value = 90, do this in the WHERE clause:

SELECT id, min((tbl2.date_modified - tbl1.date_submitted)/86400), max(some_value)
FROM tbl1 join
     tbl2
     on tbl1.id = tbl2.fid join
     tbl3
     on tbl1.id = tbl3.fid
WHERE somevalue = 90
GROUP BY tbl1.id

I'm not sure what you mean by "not include rows where the are other rows with some_value=90". If what you mean to exclude all result rows if there is a 90, then use a HAVING clause:

SELECT id, min((tbl2.date_modified - tbl1.date_submitted)/86400), max(some_value)
FROM tbl1 join
     tbl2
     on tbl1.id = tbl2.fid join
     tbl3
     on tbl1.id = tbl3.fid
GROUP BY tbl1.id
HAVING sum(case when somevalue = 90 then 1 else 0 end) > 0

Another tip: always include aliases for your columns, so everyone knows what table they are coming from.


You can use where not exists

select a.id, min((b.date_modified - a.date_submitted)/86400), max(some_value)
  from tbl1 a
  join tbl2 b
    on a.id = b.fid 
  join tbl3 c
    on a.id = c.fid
 where not exists ( select 1 
                      from tbl2
                     where fid = a.id 
                       and some_value >= 90 )
 group by a.id

or not in

select a.id, min((b.date_modified - a.date_submitted)/86400), max(some_value)
  from tbl1 a
  join tbl2 b
    on a.id = b.fid 
  join tbl3 c
    on a.id = c.fid
 where a.id not in ( select fid 
                       from tbl2 
                        and some_value >= 90 )
 group by a.id

Need Your Help

ASP.Net C# How to make a page on website accessible after authenticating via logon

c# asp.net-mvc-3 login restrict

I've been playing with some of the MVC tutorials on making a database website with C# and have a question about how to make a section of the website only accessible ONCE a user has logged in with a

X code Storyboard Scrolling

xcode

is there a simple way in x code storyboard to create a scrolling page including both text and images? Also, is this possible without using code? (Code is fine, but preferably none) Thanks! -Jcpopp

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.