0

relative newbie here. Only got serious with SQL within the last month or so for work, so I'm still pretty rusty with it.

Is it posible for a query to pull results after a date, but also ones of the same ID that fall before the date?

Sorry for the title gore, but I'm not sure if there's a better way to word it.

I'm trying to run a query that would normally return results from a table that looks like this:

(enter image description here)

And if I were to want it to return all lines after 10/1/16, It would pull lines 1, 6, 7, 8, 9, 10, but what I want it to do is to pull everything for that ID, if just one of the lines was past a certain date.

The results should more or less look like this:

enter image description here

...if I were to enter a date of greater than 10/1/16.

Is that possible to do within SQL? For the record, I'm on SQLdbx.

Thanks a ton!

Hogan
  • 69,564
  • 10
  • 76
  • 117
jwoody000
  • 1
  • 1
  • Welcome to SO here are some tips on how to ask a good sql question http://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question/271056 It is best not to link to images because they can then disappear. The should be inlcuded in your post. And in this case you should include text of the data NOT images. You are asking for our help so don't make us do the work to type out all of the sample data etc include DML statements or at least text of the data. Thanks – Matt Nov 11 '16 at 20:34
  • I see 1 question: Is that possible to do within SQL? the answer is Yes. you just need to use an`OR` and a coolated subquery with `exists` saying or the ID exists in those whose date is >=yourdate. – xQbert Nov 11 '16 at 20:38
  • Which DBMS are you using? Also: http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 –  Nov 11 '16 at 20:46

1 Answers1

0

What you are wanting to do takes a 2 step operation.

  1. Find all of the Ids that have a Date greater than the date you specify.
  2. Find all rows that relate to that ID.

This can be done lots of ways. Here is a way using exists you can do both steps in 1 statement:

SELECT *
FROM
    Table t1
WHERE
    EXISTS (SELECT 1 FROM Table t2 WHERE t1.Id = t2.Id AND t2.Date > '2016-10-1')

Using IN

SELECT *
FROM
    Table
WHERE
    Id IN (SELECT DISTINCT Id FROM Table WHERE Id IS NOT NULL AND Date > '2016-10-1')

Note I have included where Id IS NOT NULL because when you use IN with NULL as a possibility you won't get the list you desire.

Left Self Join:

SELECT t1.*
FROM
    Table t1
    INNER JOIN Table t2
    ON t1.Id = t2.Id
    AND t2.Date > '2016-10-1'

And there are more yet.

Matt
  • 13,833
  • 2
  • 16
  • 28
  • The first example is not one query -- it is two -- it has a sub-query. The last example (with the join) is one query. The last example is the best way to do this using an inner join. – Hogan Nov 11 '16 at 20:46
  • 1
    @Hogan: the first one **is** a single query. A query using `IN` or `exists` can't necessarily be replaced with a join - those are two different things. And **if** they are equivalent modern optimizers will most likely use the same execution plan anyway –  Nov 11 '16 at 20:46
  • @Hogan yep is is single query that also utilizes a sub query. And the one to use depends on the RDBMs being used. sql-server EXISTS is typically touted as faster http://stackoverflow.com/questions/173041/not-in-vs-not-exists mysql is different etc. – Matt Nov 11 '16 at 20:49
  • @a_horse_with_no_name -- hmmm... in thinking about SQL if a query has a sub-query I don't call it single -- with a good optimizer it often does not matter, but when it does it gets ugly. It seems a good way to think and teach SQL optimization to me. Of course you are right we call the whole thing one query so my bad. – Hogan Nov 11 '16 at 20:51