CODEWITHSUNDEEP
sqloracle-database
trueinViso
trueinViso

Reputation: 1394

Comparing duplicate rows in one table, and returning values in another table

I need to compare rows in the same table of a query and return results of id's matching records in another table.

Here is an example of the first table:

id    checkin     checkout 
1     01/15/13    01/31/13   
1     01/31/13    05/20/13
2     01/15/13    05/20/13
3     01/15/13    01/19/13
3     01/19/13    05/20/13
4     01/15/13    02/22/13
5     01/15/13    03/01/13

I want to only get records in this table of id's that have no matching record after today's date. So ID's 1 and 3 would not be returned because the second record checkout date is after today's date. I then want to match those id's with the a record in another table and return values from that second table.

Example second table:

id    address     zip
1     abc         98734
2     uvx         12345
3     ;alksdf     12347
4     bhg         34567
5     ;alkjdf     56789

So in this case I would return the address and zip of id 4 and 5.

Upvotes: 2

Views: 2690

Answers (2)

hrezs
hrezs

Reputation: 782

Something like?

 SELECT *
 FROM table2 
 WHERE table2.id NOT IN (SELECT DISTINCT table1.id
                         FROM table1
                         WHERE table1.checkin > sysdate OR
                           table1.checkout > sysdate) AND
 table2.id IN (SELECT DISTINCT table1.id
               FROM table1)

Edited to exclude those with dates in the future as well as make sure there is a corresponding record

Upvotes: 3

Dan Bracuk
Dan Bracuk

Reputation: 20804

to get only those records from today or earlier, do this:

where checkout <= trunc(sysdate)

Edit starts here

to exclude records with checkout dates in the future, do this:

where not exists
(select 1 record
from yourTable t2
where checkout > trunc(sysdate + 1)
and t2.id = yourTable.id
)

Upvotes: 2

Related Questions