BrainDamage

[Mammu]

hi,
I have two tables as given created in SQL srever 2005

Employees
-----------------
>intEid (primary key)
>intLeaveCredit

LeaveApplications
--------------------------
>intSlno (primary key)
>intEid (Foreign key)
>strTypeOfLeave
...
>dteApplicationDate (datetime)
>bolSanctioned (boolean)

now, i need to be able to get max date of each intEid in LeaveApplications with bolSanctioned as false. I need all the fields in the LeaveApplications Table and intLeaveCredit from the Employees table, to be in the resulting joined table.

this is what I have been doing...

select e.intEid, e.intLeaveCredit, l.strTypeOfLeave, l.dteFrom, l.dteTo, l.strReason, l.dteApplicationDate, l.bolSanctioned
    from Employees e
    join LeaveApplications l
    on e.intEid = l.intEid
    where l.dteTo = (select max(dteTo) from LeaveApplications where   bolSanctioned = 'false' )
 

[Mammu]

Sorry about the title folks... did that in frustation.. and thank you in advance for any help.

[iburyak]

I don't see a problem in your query.
Do you get incorrect result?

You can do the same differently if you wish but you did OK.


[PHP]select Top 1 e.intEid, e.intLeaveCredit, l.strTypeOfLeave, l.dteFrom, l.dteTo, l.strReason, l.dteApplicationDate, l.bolSanctioned
from Employees e
join LeaveApplications l
on e.intEid = l.intEid
where bolSanctioned = 'false'
ORDERR BY l.dteTo desc[/PHP]

[Mammu]

I don't get any records. I've made only a couple of test entries in the necessary fields for each table... it gave me a result when i reteived records from only the Leave records table. but what i need is a joined table with all the fields from the LeaveApplications table and the LeaveCredit field from the Employee table.

thank you for replying

[iburyak]

It is not a query problem.

Execute following query. Did you get any records? If not it means you don't have matching intEid in both tables.


[PHP]select *
from Employees e
join LeaveApplications l
on e.intEid = l.intEid[/PHP]

If you received result above try to execute query below. If you don't have a result here it means even thou you have matching records none of them is False.

[PHP]Select *
from Employees e
join LeaveApplications l
on e.intEid = l.intEid
where bolSanctioned = 'false'[/PHP]

[Mammu]

Sorry for the delay in replying... I was not working. You are correct about the entries I made. I had no record with bolSanctioned as false. I cannot believe I wasted so much time on this... and I would have wasted more time if it weren't for you. Thank you so much.

But I still have a problem. I'm only getting one record with the max date i the whole table. what i needed was the last date for each employee listed in the table. any suggestions? and Thank you once again for helping me...

[iburyak]

Try this:


[PHP]select e.intEid, e.intLeaveCredit, l.strTypeOfLeave, l.dteFrom,
l.dteTo, l.strReason, l.dteApplicationDate, l.bolSanctioned
from Employees e
join LeaveApplications l on e.intEid = l.intEid
where l.dteTo = (select max(dteTo) from LeaveApplications where bolSanctioned = 'false' and intEid = e.intEid )
and bolSanctioned = 'false' [/PHP]

[Mammu]

thank you iburyak for your help. I finally managed to find a solution without using any join.

this is my solution. Thank you all the same.

select e.intEid , e.intLeaveCredit, l.strTypeOfLeave , l.dteFrom , l.dteTo , l.strReason , l.dteApplicationDate 
from LeaveApplications l, Employees e 
where e.intEid = l.intEid
and dteTo = (SELECT max(dteApplicationDate) from LeaveApplications l1 WHERE l1.intEid = l.intEid and l.bolSanctioned = 'False')

[iburyak]

thank you iburyak for your help. I finally managed to find a solution without using any join.

this is my solution. Thank you all the same.

select e.intEid , e.intLeaveCredit, l.strTypeOfLeave , l.dteFrom , l.dteTo , l.strReason , l.dteApplicationDate 
from LeaveApplications l, Employees e 
where e.intEid = l.intEid
and dteTo = (SELECT max(dteApplicationDate) from LeaveApplications l1 WHERE l1.intEid = l.intEid and l.bolSanctioned = 'False')

HA HA HA..... :)

Whatever you show is a join just written old way that is not recommended to use in SQL syntax.... LOL

Old syntax for join:

[PHP]from LeaveApplications l, Employees e
where e.intEid = l.intEid[/PHP]

New syntax for join:

[PHP]from Employees e
join LeaveApplications l on e.intEid = l.intEid[/PHP]

Good Luck.