SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Connect multiply user search results		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Schareina
Starting Member

4 Posts
Posted - 01/21/2013 :  08:44:24  Show Profile  Reply with Quote
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
Posting Yak Master

Denmark
100 Posts
Posted - 01/21/2013 :  13:14:59  Show Profile  Reply with Quote
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 - 01/23/2013 :  06:05:21  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000