noob SQL question Join question 3

[aaronlglover]

Hey Guys,

I am a complete SQL noob and I have searched all over to figure out how to do this but have nothing. I have two tables; TABLE1(USER_ID, DATE, EXCEPTION, USER_NAME) and TABLE2(USER_ID, MODIFY_DATE, MU_ID). I need to return USER_ID, DATE, EXCEPTION, USER_NAME and MU_ID. The tables are joined on USER_ID. In table 2 there are multiple records for each USER_ID with different MODIFY_DATE (everytime a change is made it made a new record with a new modify date is created.)

I need to basically need to associate the MU_ID that agent had on the date of each particualr record in TABLE1

Here is an example

TABLE1:

USER_ID | DATE | EXCEPTION | USER_NAME
333 | 4/12/09 | BREAK | John Doe

TABLE2:

USER_ID | MODIFY_DATE | MU_ID
333 | 1/1/2009 | 30
333 | 2/24/2009 | 36
333 | 5/23/2009 | 02
333 | 6/17/2009 | 90

Should RETURN

USER_ID | DATE | EXCEPTION | USER_NAME | MU_ID
333 | 4/12/2009 | BREAK | John Doe | 36

Thanks

 

[gmmastros]

This is more difficult than it seems. First, you'll need to join the 2 tables together. Clearly, you'll be joining on user_id, but you also need to take the dates in to consideration. The date from table2 needs to be less than the date in table 1. so.... let's start with that.

Select Table2.user_id, Table2.Modify_Date
From   Table1
       Inner Join Table2
         On Table1.user_id = table2.user_id
         And table2.modify_date < table1.date

Beginners don't usually realize that you can specify multiple conditions in the on part of the join. Worse yet, they don't realize you can use other types of operators (like the Less than operator). Obviously, you can (because I just did it). Unfortunately, this doesn't really give us what we want because there are multiple rows for each user. Of these rows, we need to pick the one that has the later date, so we can use a group by here. Like this...

Select Table2.user_id, Max(Table2.Modify_Date) As Modify_Date
From   Table1
       Inner Join Table2
         On Table1.user_id = table2.user_id
         And table2.modify_date < table1.date
Group By Table2.user_id

This will give us the correct data from table 2, but how do we get the MU_ID from table 2, and how do we match this up with the row from table 1?

We can accomplish this by using the previous query for our 'filter' condition. We will create a derived table out of this and then inner join it to our outer query. Because we are inner joining, this will effectively remove rows we don't want (filtering the data). Like this...

Select Table1.user_id,
       Table1.Date,
       Table1.Exception, 
       Table1.user_name,
       Table2.MU_ID
From   table1
       Inner Join Table2
         On Table1.user_id = table2.user_id
       Inner Join (
         Select Table2.user_id, Max(Table2.Modify_Date) As Modify_Date
         From   @Table1
                Inner Join @Table2
                  On Table1.user_id = table2.user_id
                  And table2.modify_date < table1.date
         Group By Table2.user_id
         ) As AliasName
         On Table2.user_id = Aliasname.User_id
         And Table2.Modify_Date = AliasName.modify_Date

Notice how we took the previous query and embedded it within the bigger query. This technique is called a derived table. The parenthesis around the query identify where the derived table begins and ends. The AS AliasName that follows the close parenthesis gives this derived table a name, and the on clause allows us to ONLY return the rows that match.

There are several relatively advanced concepts here. If you don't understand any of this, let me know and I will explain it more.


-George

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

 

[aaronlglover]

OH man thanks so much. I actually new I had to do some kind of sub query or "derived table" as you call it I just could not get the syntax right. This actually makes perfect sense to me now.

I appreciate it greatly!!!!

 

[SQLSister]

Great explanation George

"NOTHING is more important in a database than integrity." ESquared

 

[aaronlglover]

Hey George,

So duplicating your example I built some SQL that uses my actual tables and I get a "Missing Operator" error starting at "ScheduleAta.AGENT_ID = agentmap.AGENT_ID" Can you see anything I clearly missed?


SELECT ScheduleDATA.AGENT_NAME,
ScheduleDATA.HEADER_DATE,
agentmap.MU_ID
FROM ScheduleDATA
Inner Join agentmap
On ScheduleDATA.AGENT_ID = agentmap.AGENT_ID
Inner Join (
Select agentmap.AGENT_ID, Max(agentmap.MODIFY_DATE_TIME) As Modify_Date_Time
From @ScheduleDATA
Inner Join @agentmap
On ScheduleDATA.Agent_ID = agentmap.AGENT_ID
AND agentmap.Modify_Date_Time < ScheduleDATA.HEADER_DATE
Group By agentmap.AGENT_ID
) As AliasName
On agentmap.AGENT_ID = AliasName.AGENT_ID
AND agentmap.MODIFY_DATE_TIME = AliasName.Modify_Date_Time

 

[markros]

(
Select agentmap.AGENT_ID, Max(agentmap.MODIFY_DATE_TIME) As Modify_Date_Time
From @ScheduleDATA S
Inner Join @agentmap A
On S.Agent_ID = Ap.AGENT_ID
AND A.Modify_Date_Time < S.HEADER_DATE
Group By A.AGENT_ID
) As AliasName

Since you were using table variables, you may need to add aliases as I showed.

 

[gmmastros]

I mistakenly left a couple @ symbols in my example. Try this:

SELECT ScheduleDATA.AGENT_NAME,
       ScheduleDATA.HEADER_DATE,
       agentmap.MU_ID
FROM   ScheduleDATA
       Inner Join agentmap
           On ScheduleDATA.AGENT_ID = agentmap.AGENT_ID
       Inner Join (
           Select agentmap.AGENT_ID, Max(agentmap.MODIFY_DATE_TIME) As Modify_Date_Time
           From   ScheduleDATA
                  Inner Join agentmap
                     On ScheduleDATA.Agent_ID = agentmap.AGENT_ID
                     AND agentmap.Modify_Date_Time < ScheduleDATA.HEADER_DATE
           Group By agentmap.AGENT_ID
           ) As AliasName
           On agentmap.AGENT_ID = AliasName.AGENT_ID
           AND agentmap.MODIFY_DATE_TIME = AliasName.Modify_Date_Time

-George

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

 

[aaronlglover]

Hey Mark and George,

I tried both of your suggestions and I get the exact same error at the exact same spot. Here is the last SQL i just tried


SELECT ScheduleDATA.AGENT_NAME,
ScheduleDATA.HEADER_DATE,
agentmap.MU_ID
FROM ScheduleDATA
Inner Join agentmap
On ScheduleDATA.AGENT_ID = agentmap.AGENT_ID
Inner Join (
Select agentmap.AGENT_ID, Max(agentmap.MODIFY_DATE_TIME) As Modify_Date_Time
From ScheduleDATA S
Inner Join agentmap A
On S.Agent_ID = A.AGENT_ID
AND A.Modify_Date_Time < S.HEADER_DATE
Group By A.AGENT_ID
) As AliasName
On agentmap.AGENT_ID = AliasName.AGENT_ID
AND agentmap.MODIFY_DATE_TIME = AliasName.Modify_Date_Time

 

[gmmastros]

Try:

SELECT ScheduleDATA.AGENT_NAME,
       ScheduleDATA.HEADER_DATE,
       agentmap.MU_ID
FROM   ScheduleDATA
       Inner Join agentmap
           On ScheduleDATA.AGENT_ID = agentmap.AGENT_ID
       Inner Join (
           Select [!]A[/!].AGENT_ID, Max([!]A[/!].MODIFY_DATE_TIME) As Modify_Date_Time
           From   ScheduleDATA S
                  Inner Join agentmap A
                     On S.Agent_ID = A.AGENT_ID
                     AND A.Modify_Date_Time < S.HEADER_DATE
           Group By A.AGENT_ID
           ) As AliasName
           On agentmap.AGENT_ID = AliasName.AGENT_ID
           AND agentmap.MODIFY_DATE_TIME = AliasName.Modify_Date_Time

-George

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

 

[aaronlglover]

Just tried that...same error. WHen I try the the derived table Join by itself it runs, and then whey I try the first join by itself it runs. Something is goofing up when I try to join those two together though.

 

[markros]

Hi Aaron,

George already spotted the error, but I just wanted to suggest to change AliasName to AgentLastInfo or something else more descriptive.

Also I forgot, which SQL Server version you're using?

 

[gmmastros]

What version of Microsoft SQL Server are you using? If you're not sure, then run this...

Select @@Version



-George

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

 

[markros]

;with cteAgentLastInfo as (Select A.AGENT_ID, Max(A.MODIFY_DATE_TIME) As Modify_Date_Time
           From   ScheduleDATA S
                  Inner Join agentmap A
                     On S.Agent_ID = A.AGENT_ID
                     AND A.Modify_Date_Time < S.HEADER_DATE
           Group By A.AGENT_ID)

SELECT ScheduleDATA.AGENT_NAME,
       ScheduleDATA.HEADER_DATE,
       agentmap.MU_ID
FROM   ScheduleDATA
       Inner Join agentmap
           On ScheduleDATA.AGENT_ID = agentmap.AGENT_ID
       Inner Join cteAgentLastInfo Last ON
           On agentmap.AGENT_ID = Last.AGENT_ID
           AND agentmap.MODIFY_DATE_TIME = Last.Modify_Date_Time

This is SQL Server 2005 and up solution - just for clarity.

 

[aaronlglover]

I am actually just querying an MDB 2000 file format from access...

 

[gmmastros]

That explains it. I haven't used Access in a long time, but.... I'm reasonably certain that you can get this query to work with some modifications. Access likes to have a bazillion parenthesis in the joins. Also... it could be the AS part in the derived table alias that's throwing things off.

try this

SELECT ScheduleDATA.AGENT_NAME,
       ScheduleDATA.HEADER_DATE,
       agentmap.MU_ID
FROM   ScheduleDATA
       (Inner Join agentmap
           On ScheduleDATA.AGENT_ID = agentmap.AGENT_ID
       (Inner Join (
           Select A.AGENT_ID, Max(A.MODIFY_DATE_TIME) As Modify_Date_Time
           From   ScheduleDATA S
                  Inner Join agentmap A
                     On S.Agent_ID = A.AGENT_ID
                     AND A.Modify_Date_Time < S.HEADER_DATE
           Group By A.AGENT_ID
           ) AliasName
           On agentmap.AGENT_ID = AliasName.AGENT_ID
           AND agentmap.MODIFY_DATE_TIME = AliasName.Modify_Date_Time))

If this does not work, I would encourage you to post this query (and a link to this thread) in one of the access forums here.



-George

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

 

[aaronlglover]

Got IT!!

I just had to add a few () and it worked. Sorry guys, I had no idea that Access treated the code different the SQL server.

Thanks so much for your help.