Regarding Query to get one row for each worker

Posted by Thiru under Sql Server on 8/16/2012 | Points: 10 | Views : 1724 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

Latest Posts