Regarding Query to get one row for each worker

Posted by Thiru under Sql Server on 8/16/2012 | Points: 10 | Views : 921 | Status : [Member] | Replies : 2
hi friends,
fix a solution for my expected query.

two tables (tblbranch and tblBill)
(note: each branch is having many bills)
relationship= tblbranch.Bcde=tblBill.Bcde

i want all fields in tblbranch and last bill from tblBill


select a.*,b.* from tblbranch a innerjoin tblBill b on a.Bcde=b.Bcde ..... order by b.billdate desc limit 1
?

Actually i am requiring a query to execute in mysql db




Responses

Posted by: Pandians on: 8/16/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Check It Out in SQL Server! Convert to MySQL!
Create Table tblbranch 

(
Bcde Int,
Description Varchar(100)
)
go

Create Table tblBill
(
BillNo Int,
Bcde Int,
BillDate Datetime
)
go

Insert tblbranch values(1,'Branch1')
Insert tblbranch values(2,'Branch2')
Insert tblbranch values(3,'Branch3')
Insert tblbranch values(4,'Branch4')
go

Insert tblBill Values(1,1,'2012-01-01')
Insert tblBill Values(2,1,'2012-01-12')
Insert tblBill Values(3,2,'2012-01-15')
Insert tblBill Values(4,1,'2012-02-01')
Insert tblBill Values(5,2,'2012-02-11')
Insert tblBill Values(6,3,'2012-02-09')
go
;With LastBill

As
(
Select Bcde, Max(BillDate) BillDate From tblBill Group By Bcde
)
Select Br.*, Bl.BillDate from tblbranch Br Join LastBill Bl On (Br.Bcde = Bl.Bcde)
Order By Br.Bcde
Result
Bcde	Description	BillDate

1 Branch1 2012-02-01 00:00:00.000
2 Branch2 2012-02-11 00:00:00.000
3 Branch3 2012-02-09 00:00:00.000


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Thiru on: 8/16/2012 [Member] Starter | Points: 25

Up
0
Down
Hi iLink Multitech,
Thanks for spending your valuable time here.
and thanks for your query.

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response