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
Go to Top of Page

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

Go to Top of Page
   

- Advertisement -