Query - Return rows based on matching dates and specific values 2

[KARR]

All,

Been rattling my brains out on getting this query to return the proper results...

Would like to return two records for each "FileNo" that has X300 and X400 for the same date. If a FileNo does not have both a X300 and X400 for the same date, do not return anything for that FileNo.

Table Example
FileNo Date Code
TEST1 2007-11-30 X300
TEST1 2007-11-30 X400
TEST2 2007-11-30 X300

Result I am looking for.

TEST1 2007-11-30 X300
TEST1 2007-11-30 X400
(TEST2 would not be returned.)

I was using this as a starting point...

SELECT Activity1.*
FROM Activity1
INNER JOIN (SELECT FileNo, Code, Max(Date) AS Date
FROM Activity1
GROUP BY FileNo, Code) Tbl1
ON Activity1.Code = Tbl1.Code AND
Activity1.FileNo = Tbl1.FileNo AND
Activity1.Date = Tbl1.Date
WHERE Activity1.Code = 'X300'Or Activity1.Code = 'X400'
ORDER BY Activity1.FileNo, Activity1.Code DESC, Activity1.Date

But it only returns the most recent date value.

Thoughts?

Thanks
Rob - KARR

 

[Crowley16]

create a self join on FileNo and Date, then check that both code columns contains a x300 or x400 and both columns aren't the same.

something like:

select *
from table t1 inner join
table t2 on t1.FileNo = t2.FileNo
and t1.date = t2.date
and t1.code <> t2.code
where
t1.code in ('x300', 'x400')
and t2.code in ('x300', 'x400')

--------------------
Procrastinate Now!

 

[KARR]

I modified the query to:

SELECT Activity1.*
FROM Activity1
INNER JOIN (Select FileNo, Code, Max(Date) As Date
From Activity1
GROUP BY FileNo, Code)
Tbl1 on
Activity1.FileNo = Tbl1.FileNo and
Activity1.Date = Tbl1.Date and
Activity1.Code <> = Tbl1.Code
Where Activity1.Code In ('X300', 'X400') and
Activity1.Code In ('X300', 'X400')


I am only using one table (Activity1) so I all references have been changed to that, however I am not getting any results back.

 

[KARR]

Disregard the above response, I had wrong codes in the table. Will re-run query now.

 

[Crowley16]

why have you modified the query to include a derived table to join on?

you DO NOT have to do this, use my exact example from above but change table to activity1

--------------------
Procrastinate Now!

 

[KARR]

I see, the results I got from your query:

TEST5 2004-01-01 X300 TEST5 2004-01-01 X400
TEST5 2004-01-01 X400 TEST5 2004-01-01 X300
TEST4 2007-11-30 X300 TEST4 2007-11-30 X400
TEST4 2007-11-30 X400 TEST4 2007-11-30 X300

How would I get the data to return like:

TEST5 2004-01-01 X300
TEST5 2004-01-01 X400

 

[KARR]

Was able to get the format I was looking for by removing the * and replacing it with t1.FileNo, t1.code, t1.date.

But I am trying to understand why SQL returned the data in that format.

 

[gmmastros]

Let me try to explain this.

First, to get this to work, Crowley used a method called 'self join'. Normally, when you join 2 (or more) tables together, they are different tables. With a self join, you actually join a table to itself.

Now, when you Select [!]*[/!], you are essentially requesting all of the columns in all of the tables that are in your query. In my opinion, you should never use * in a select because it returns all of the columns. Instead, it's better to specify the columns that you want so that you don't waste server processing time and network bandwidth by returning more data than you actually need.

Now, consider for a moment the nature of this forum. People ask for help and others provide it. In this particular case, the method (self join) is the 'interesting' part of the answer. For the sake of clarity and timeliness, * was presented in the solution because replacing the * with your actual column names is something that you should be able to do on your own (which you figured out for yourself).

By the way, you could have gotten the same results by replacing * with t1.* This syntax basically says, "Give me all the columns from the t1 table.".

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[KARR]

Makes sense, and more importantly I understand.

Thanks
Rob

 

[KARR]

All,

Need to revisit this topic with one more question...

How would I be able to return columns from another table entirley?

The working code:

select t1.FileNo, t1.Code
from table t1 inner join
table t2 on t1.FileNo = t2.FileNo
and t1.date = t2.date
and t1.code <> t2.code
where
t1.code in ('x300', 'x400')
and t2.code in ('x300', 'x400')

I was thinking of place the above in a sub-query and perhaps joining but havent had any sucess.

Thoughts?

Robb