July 14, 2009 at 10:42 am
I would like to be able to do this without joining on 2 separate sub queries, for example (field1 is primary key):
select field1
from a
inner join b on b.Field1 = b.Field1
and b.date = (select min(b2.date) from b2 where b2.field1 = b.field1)
or b.date = (select max(b2.date) from b2 where b2.field1 = b.field1)
where a.date <> b.date
Trying to do it this way on the large tables I am using dramatically slows performance. I am looking for a way to get the same result with much less time to run or taxation on the database.
Thanks in advance!
July 14, 2009 at 10:58 am
skailey (7/14/2009)
I would like to be able to do this without joining on 2 separate sub queries, for example (field1 is primary key):select field1
from a
inner join b on b.Field1 = b.Field1
and b.date = (select min(b2.date) from b2 where b2.field1 = b.field1)
or b.date = (select max(b2.date) from b2 where b2.field1 = b.field1)
where a.date b.date
Trying to do it this way on the large tables I am using dramatically slows performance. I am looking for a way to get the same result with much less time to run or taxation on the database.
Thanks in advance!
Greetings,
There is a problem in your inner join that you are joining to the same field in the same table. It is probably just a typo, but this type of join will give you an all to all type of join (Cartesian).
An option to help solve your problem may be to use a few sub selects.
SELECT
Field1
FROM a
JOIN b ON a.Field1 = b.Field1
JOIN
(
SELECT
Field1,
MIN(Date) AS MinDate,
MAX(Date) AS MaxDate
FROM b2
GROUP BY Field1
) b2 ON b.Date BETWEEN b2.MinDate AND b2.MaxDate
WHERE
a.Date b.Date
Have a good day.
Terry Steadman
July 14, 2009 at 11:20 am
What would help is if you could post the DDL for the tables, some sample data for the tables in a readily consummable format (can be cut/paste/run in SSMS to load the tables after they are created), expected results from the query based on the sample data, and the code you have currently written.
For help with this request, please read the first article I reference below in my signature block.
July 14, 2009 at 11:30 am
While I appreciate the reasoning for why you would like certain questions posted this way, I believe it is overkill for my question. I have 2 tables. I am trying to join them on a primary key and a datefield. I want the datefield in table A to = any 1 of 2 dates (min or the max) in table b. This would then pull the correct record based on date from table b. For my overall query, I want to look for any records in table a where a record did not pull in from table b because neither of the dates matched up with the table a date. Hence, the simple query I orignally posted.
I just can't figure out how to easily do that join without 2 subqueries.
July 14, 2009 at 11:34 am
terrance.steadman (7/14/2009)
skailey (7/14/2009)
I would like to be able to do this without joining on 2 separate sub queries, for example (field1 is primary key):select field1
from a
inner join b on a.Field1 = b.Field1
and b.date = (select min(b2.date) from b2 where b2.field1 = b.field1)
or b.date = (select max(b2.date) from b2 where b2.field1 = b.field1)
where a.date b.date
Trying to do it this way on the large tables I am using dramatically slows performance. I am looking for a way to get the same result with much less time to run or taxation on the database.
Thanks in advance!
Greetings,
There is a problem in your inner join that you are joining to the same field in the same table. It is probably just a typo, but this type of join will give you an all to all type of join (Cartesian).
An option to help solve your problem may be to use a few sub selects.
SELECT
Field1
FROM a
JOIN b ON a.Field1 = b.Field1
JOIN
(
SELECT
Field1,
MIN(Date) AS MinDate,
MAX(Date) AS MaxDate
FROM b2
GROUP BY Field1
) b2 ON b.Date BETWEEN b2.MinDate AND b2.MaxDate
WHERE
a.Date b.Date
Have a good day.
Terry Steadman
Thanks Terry, you are right... that was a typo. I am not sure this will work for what I need. I want to be able to pull all records from table b where they did not match up to table a based on primary key and the min or max date of the datefield in table b.
July 14, 2009 at 11:54 am
SELECT
Field1
FROM a
JOIN b ON a.Field1 = b.Field1
JOIN
(
SELECT
Field1,
MIN(Date) AS MinDate,
MAX(Date) AS MaxDate
FROM b2
GROUP BY Field1
) b2 ON b.Date BETWEEN b2.MinDate AND b2.MaxDate
WHERE
a.Date b.Date
Have a good day.
Terry Steadman
Thanks Terry, you are right... that was a typo. I am not sure this will work for what I need. I want to be able to pull all records from table b where they did not match up to table a based on primary key and the min or max date of the datefield in table b.[/quote]
Greetings,
Thank you for the extra information about what you had wanted. I can understand wanting the records from table b that do not have a match in table a. But I would like a bit more information about the MIN and MAX dates.
It sounds like that the MIN and MAX dates are coming from table b. But, then you want to use that for comparison again to table b. Are you trying to report the MIN and MAX dates of the unmatched records or on that either table b's primary key does not exist in table a OR table a's date is outside the expected date range from table b?
Here is a modification of the code from above that will at least give you the records from table b that do not have a match in table a.
SELECT
b.Field1
FROM b
LEFT OUTER JOIN a ON b.Field1 = a.Field1
WHERE
a.Field1 IS NULL
July 15, 2009 at 6:26 am
skailey (7/14/2009)
While I appreciate the reasoning for why you would like certain questions posted this way, I believe it is overkill for my question. I have 2 tables. I am trying to join them on a primary key and a datefield. I want the datefield in table A to = any 1 of 2 dates (min or the max) in table b. This would then pull the correct record based on date from table b. For my overall query, I want to look for any records in table a where a record did not pull in from table b because neither of the dates matched up with the table a date. Hence, the simple query I orignally posted.I just can't figure out how to easily do that join without 2 subqueries.
Hi skailey,
While i appreciate it may take a little time to create ddls and data in a readily consumable format for you, and this may seem like overkill, I can guarantee — from personal experience — that you will save your own time (not to mention anyone else's trying to help you) by doing so. All of the folks who regularly post and help out others on this forum are doing so for no reward, other than helping others, solving problems and learning something along the way; the easier you make it for people to help you the more help you will receive, the quicker you will get your problem resolved, and the more accurate the solution.
If you can post table structures, data that cuts and pastes, expected output and any code you already have to attempt to solve the problem the chances are you'll get at least 1 (or possibly 2 or three cross post) solution(s) in next to no time!
\\\edited typo
July 15, 2009 at 8:26 am
skailey (7/14/2009)
While I appreciate the reasoning for why you would like certain questions posted this way, I believe it is overkill for my question. I have 2 tables. I am trying to join them on a primary key and a datefield. I want the datefield in table A to = any 1 of 2 dates (min or the max) in table b. This would then pull the correct record based on date from table b. For my overall query, I want to look for any records in table a where a record did not pull in from table b because neither of the dates matched up with the table a date. Hence, the simple query I orignally posted.I just can't figure out how to easily do that join without 2 subqueries.
You think our asking for more information in the form of the DDL for the table(s), sample data to load the table(s), expected results is overkill for your problem? Okay, you have access to your database, the tables and its structures (including indexes), the data, and you know what you are expecting from your query. On the other hand, we have none of that to work with to help you. Please tell me how we are expected to help you based solely on this:
I would like to be able to do this without joining on 2 separate sub queries, for example (field1 is primary key):
select field1
from a
inner join b on b.Field1 = b.Field1
and b.date = (select min(b2.date) from b2 where b2.field1 = b.field1)
or b.date = (select max(b2.date) from b2 where b2.field1 = b.field1)
where a.date b.date
Trying to do it this way on the large tables I am using dramatically slows performance. I am looking for a way to get the same result with much less time to run or taxation on the database.
This doesn't really tell me anything that I can use to help you solve your problem. The more information you provide us the better we can help you.
Please remember most of us are helping others on this site on our own time without thought of pay or any other renumeration. We are here trying to help others learn more about SQL Server, improve thier skills, and perhaps one day they too will turn around and do the same as we are here on SSC.
July 15, 2009 at 8:34 am
No offense was meant at all guys. It was a time issue for me and I just didn't have the time yesterday. As I said yesterday, I really appreciate the reason behind you asking for that info. I simply thought my issue would be an easy answer without knowing all of the detailed information. I posted a simple generic query in hopes that would be the case because I didn't want anyone to have to do my work for me. I thought it was more of a generic logic issue than it is apparently.
I hope to get some free time this afternoon and I will post more specifics. I worked around my issue for now, but I am here to learn and would like to know how what I am trying to do can be done.
Thanks guys.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply