Connect multiply user search results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Connect multiply user search results

Author

Topic

Schareina
Starting Member

4 Posts

Posted - 2013-01-21 : 08:44:24

Hello everyone, I'm again with one for spectacular statemten.

I must connect two or more search result in three combinations:

First combination is each search-term in one whole projekt.
Second combination is each search-term by one worker.
Third combination is any searchterm.

The second combination is very hard to combine for me.

INPUT: two or more result tables.

Table: searchTag2 AS t2

project_id				worker_id				s_time
-------------------------------------------------------------------------------------------
0xc80a7036cda94f6498b6d17e45fab3dd	0x857f3f4f9e0142309125fdb6de13651a	16.0
0xe47fd99d0abb45c5b636e4745a1fcd0c	0x857f3f4f9e0142309125fdb6de13651a	24.0
0xe47fd99d0abb45c5b636e4745a1fcd0c	0x857f3f4f9e0142309125fdb6de13651a	24.0
0x84034a9b157a4b538712331e772e881b	0x857f3f4f9e0142309125fdb6de13651a	8.4
0xe47fd99d0abb45c5b636e4745a1fcd0c	0x857f3f4f9e0142309125fdb6de13651a	5.0
0xe47fd99d0abb45c5b636e4745a1fcd0c	0x857f3f4f9e0142309125fdb6de13651a	5.0
0xe47fd99d0abb45c5b636e4745a1fcd0c	0x857f3f4f9e0142309125fdb6de13651a	5.0
0x59dce970d30247b2806e0a68921c302f	0x857f3f4f9e0142309125fdb6de13651a	1.0
0x59dce970d30247b2806e0a68921c302f	0x857f3f4f9e0142309125fdb6de13651a	1.0

Table: searchTag1 AS t1

project_id				worker_id				s_time
-------------------------------------------------------------------------------------------
0x2885ba10a8cc499fa2f22d2c0b1968ea	0xf6559dd8d13f44f8951b831a0f075790	5.0
0x3f8c12411fdb4a03b6e3d7b5bfb3af02	0xf6559dd8d13f44f8951b831a0f075790	5.0
0xc80a7036cda94f6498b6d17e45fab3dd	0x857f3f4f9e0142309125fdb6de13651a	16.0
0xe47fd99d0abb45c5b636e4745a1fcd0c	0x857f3f4f9e0142309125fdb6de13651a	24.0
0x84034a9b157a4b538712331e772e881b	0x857f3f4f9e0142309125fdb6de13651a	8.4
0x928a3fb11e2e46d89f0bb755bc5c1bee	0x857f3f4f9e0142309125fdb6de13651a	5.0
0x8ae8d231375343f39f267f548b8df64e	0x857f3f4f9e0142309125fdb6de13651a	1.0
0x59dce970d30247b2806e0a68921c302f	0x857f3f4f9e0142309125fdb6de13651a	1.0

OUTPUT: The timespan sum without double rows (like distinct), but from both tables. The worker_id = '0xf6559dd8d13f44f8951b831a0f075790' should't appear in the result table - cause he isn't in bothe tables.
Table: resultVector1 AS rv1

worker_id				s_time_span
----------------------------------------------------

0xf6559dd8d13f44f8951b831a0f075790	10.0
0x857f3f4f9e0142309125fdb6de13651a	60.4

If one entry is not present in (t1 and t2) result - then don't care about in rv1.

My idea was: Do a full join with T1 and T2

ON (t1.worker_id = t2.worker_id) WHERE ((t1.project_id IS NULL) OR (t2.project_id IS NULL))

Then combine

UNION

the JOIN with t1 and t2

ON (t1.worker_id = t2.worker_id)

To get the the double results just once.

But then he compute the worng sum. Why

Help me, please.
------
Schareina

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-01-21 : 13:14:59

This might work for you:


select t4.worker_id
      ,sum(t4.s_time)
  from (select t3.worker_id
              ,t3.s_time
          from (select t1.worker_id
                      ,t1.project_id
                      ,t1.s_time
                  from searchtag1 as t1
                 where exists (select *
                                 from searchtag2 as t2
                                where t2.worker_id=t1.worker_id
                              )
                union all
                select t2.worker_id
                      ,t2.project_id
                      ,t2.s_time
                  from searchtag2 as t2
                 where exists (select *
                                 from searchtag1 as t1
                                where t1.worker_id=t2.worker_id
                              )
               ) as t3
         group by t3.worker_id
                 ,t3.project_id
       ) as t4
 group by t4.worker_id

Maybe we can cut away one subselect, like this:


select distinct t3.worker_id
      ,sum(t3.s_time)
  from (select t1.worker_id
              ,t1.project_id
              ,t1.s_time
          from searchtag1 as t1
         where exists (select *
                         from searchtag2 as t2
                        where t2.worker_id=t1.worker_id
                      )
        union all
        select t2.worker_id
              ,t2.project_id
              ,t2.s_time
          from searchtag2 as t2
         where exists (select *
                         from searchtag1 as t1
                        where t1.worker_id=t2.worker_id
                      )
       ) as t3
 group by t3.worker_id
         ,t3.project_id

ps.: syntax error might occur, as my only developement tool right now is notepad :-D

Schareina
Starting Member

4 Posts

Posted - 2013-01-23 : 06:05:21

bitsmed, thank you.

I correct your statement for me and it works perfectly. Yes - one subselect is to much. Pretty cool

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources