(Not) complicated sql query !

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...

I have tried this but no sucess.. tom is getting sams calls and vice versa. I am using sql server.

SELECT DISTINCT firstname,
                lastname,
                mobilename,
                callednumbers
FROM   ([testdatabase].[dbo].[LookupTable] lt
        INNER JOIN [testdatabase].[dbo].[UserInfoTable] user1
          ON lt.userid = user1.id)
       INNER JOIN [testdatabase].[dbo].[Mobile] device1
         ON lt.mobileid = device1.id
       INNER JOIN [testdatabase].[dbo].[CallLogs] log1
         ON lt.mobileid = log1.deviceid
WHERE  lt.starttime LIKE '%2000%'
ORDER  BY firstname

i did not try group_concat. i thought inner join would do the trick but no success. — user507779, Dec 30 '10 at 02:58
@user507779 `group_concat` is MySQL only. The usual technique in SQL Server to concatenate data from multiple rows is to use `XML PATH`. Example [here](http://stackoverflow.com/questions/1051362/help-with-for-xml-path-escaping-special-characters/1052353#1052353) or an alternative approach [here](http://explainextended.com/2010/06/21/group_concat-in-sql-server/) — Martin Smith, Dec 30 '10 at 03:07

Martin Smith · Accepted Answer · 2010-12-30T03:50:39.577

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

You know, I was wondering if the userid numbers were mixed up. I guess creating all new tables does sidestep that problem. — Holtorf, Dec 30 '10 at 04:15

(Not) complicated sql query !

1 Answers1