SQL: Need help with Multiple Joins of Tables?

Question

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 7 , 1 , 123,123,321 , 13-01-2002 , 30 8 , 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...

Answer 1

DECLARE @LookupTable TABLE (
  userid     INT,
  mobileid   INT,
  startedate DATETIME,
  enddate    DATETIME,
  owner      CHAR(3))
INSERT INTO @LookupTable
SELECT 1, 1, '20001212 00:00:00.000', '20010101 00:00:00.000', N'asd' UNION ALL
SELECT 2, 2, '20001212 00:00:00.000', '20010101 00:00:00.000', N'dgs' UNION ALL
SELECT 3, 3, '20010102 00:00:00.000', '20020101 00:00:00.000', N'sdg' UNION ALL
SELECT 4, 4, '20001212 00:00:00.000', '20010101 00:00:00.000', N'sdg'
DECLARE @UserInfoTable TABLE (
  userid    INT,
  firstname VARCHAR(10),
  lastname  VARCHAR(10),
  address   VARCHAR(10))
INSERT INTO @UserInfoTable
SELECT 1, N'tom', N'do', N'test' UNION ALL
SELECT 2, N'sam', N'smith', N'asds' UNION ALL
SELECT 3, N'john', N'saw', N'asdasda' UNION ALL
SELECT 4, N'peter', N'winston', N'near by'
DECLARE @Mobile TABLE (
  mobileid   INT,
  name       VARCHAR(10),
  number     INT,
  imeinumber INT )
INSERT INTO @Mobile
SELECT 1, N'apple', 123, 1111111 UNION ALL
SELECT 2, N'nokia', 456, 2222222 UNION ALL
SELECT 3, N'vodafone', 789, 3333333
DECLARE @CallLogs TABLE (
  id            INT,
  mobileid      INT,
  callednumbers VARCHAR(50),
  [date]        DATETIME,
  totalduration INT )
INSERT INTO @CallLogs
SELECT 1, 1, N'123,123,321', '20001213 00:00:00.000', 30 UNION ALL
SELECT 2, 1, N'123,123,321', '20001214 00:00:00.000', 30 UNION ALL
SELECT 3, 2, N'123,123,321', '20001213 00:00:00.000', 30 UNION ALL
SELECT 4, 2, N'123,123,321', '20001214 00:00:00.000', 30 UNION ALL
SELECT 5, 3, N'123,123,321', '20001213 00:00:00.000', 30 UNION ALL
SELECT 6, 3, N'123,123,321', '20001214 00:00:00.000', 30 UNION ALL
SELECT 7, 1, N'123,123,321', '20020113 00:00:00.000', 30 UNION ALL
SELECT 8, 1, N'123,123,321', '20020114 00:00:00.000', 30
SELECT DISTINCT firstname,
                lastname,
                device1.name AS mobilename,
                stuff((select ',' + callednumbers 
                       from @CallLogs log1 
                       where lt.mobileid = log1.mobileid 
                       for xml path('')), 1, 1, '') AS callednumbers 
FROM   (@LookupTable lt
        INNER JOIN @UserInfoTable user1
          ON lt.userid = user1.userid)
       INNER JOIN @Mobile device1
         ON lt.mobileid = device1.mobileid
WHERE  lt.startedate > '20000101' AND  startedate < '20010101'
ORDER  BY firstname