complicated sql query !!
- by user507779
LookupTable:
userid, mobileid, startedate, enddate , owner
1 , 1 , 12-12-2000, 01-01-2001, asd
2 , 2 , 12-12-2000, 01-01-2001, dgs
3 , 3 , 02-01-2001, 01-01-2002, sdg
4 , 4 , 12-12-2000, 01-01-2001, sdg
UserInfoTable:
userid, firstname, lastname, address
1 , tom , do , test
2 , sam , smith , asds
3 , john , saw , asdasda
4 , peter , winston , near by
Mobile:
Mobileid, Name , number, imeinumber
1 , apple , 123 , 1111111
2 , nokia , 456 , 2222222
3 , vodafone , 789 , 3333333
CallLogs:
id , Mobileid, callednumbers (string), date , totalduration
1 , 1 , 123,123,321 , 13-12-2000 , 30
2 , 1 , 123,123,321 , 14-12-2000 , 30
3 , 2 , 123,123,321 , 13-12-2000 , 30
4 , 2 , 123,123,321 , 14-12-2000 , 30
5 , 3 , 123,123,321 , 13-12-2000 , 30
6 , 3 , 123,123,321 , 14-12-2000 , 30
1 , 1 , 123,123,321 , 13-01-2002 , 30
2 , 1 , 123,123,321 , 14-01-2002 , 30
I want a query which will return me the following:
firstname, lastname, mobile.name as mobilename, callednumbers (as concatinated strings from different rows in CallLogs table) and need it for year 2000
example:
firstname, lastname, mobilename, callednumbers
tom , do , apple , 123,123,321, 123,123,321
sam , smith , nokia , 123,123,321, 123,123,321
peter , winston , apple , 123,123,321, 123,123,321
any help will be highly appreciated...