1

Topic: Why there is a duplication

Greetings all.
Children, prompt, please, for what reason at me in the table columns can be doubled. Duplication began, when I added a subquery t
Connection incorrect can? Or can still that is not correct?
And duplication depends on under what table I establish connection. If

on t.x_project=b.x_project

that lines which are not present in the table b, if

 are doubled
on t.x_project=a.x_project

that is doubled a line which is not present in ' a'
[spoiler]

Select pp. PROJECTCODE, pp. DESCRIPTION;
a. GeolLenght;
b. SendSample, c.assay, TakenSample
From
(
select dd. PROJECTCODE as x_project;
sum (cast ((case when name = ' totaldrilldepth ' then value end) as float)) as GeolLenght
from drillingdetail dd
where cast ([DRILLINGDATE] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE
) a
full join (
select s. PROJECTCODE as x_project;
count (sd.sampleid) as SendSample
from SAMPLEDESPATCH sd left join DESPATCHSEND ds on sd. DESPATCHNO=ds. DESPATCHNO left join sample s on s. SAMPLEID=sd. SAMPLEID
where cast ([senddate] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE
) b on a.x_project = b.x_project
full join (
select ss. PROJECTCODE as x_project;
count (ts.sampleid) as TakenSample
from SAMPLEDETAILS ts left join sample ss on ss. SAMPLEID=ts. SAMPLEID
where cast ((case when name = ' SampDateTaken ' then value end) as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE
) t on t.x_project=b.x_project
full join
(Select projectcode as x_project, count (sss) assay from (select distinct s.sampleid as sss, PROJECTCODE
from cORPSAMPLEASSAY c left join sample s on s. SAMPLEID = c. SAMPLEID
where cast ([LOADDATE] as date) between ' 2017-02-13 ' and ' 2017-03-10 ') abc
group by abc. PROJECTCODE) c on a.x_project=c.x_project
left join PROJECT pp on pp. PROJECTCODE=c.x_project or pp. PROJECTCODE=b.x_project or pp. PROJECTCODE=a.x_project or pp. PROJECTCODE=t.x_project
group by pp.PROJECTCODE,a.GeolLenght,pp.DESCRIPTION;
b. SendSample, c.assay, TakenSample
order by pp. DESCRIPTION

[/spoiler]

2

Re: Why there is a duplication

katish444;

on t.x_project=isnull (b.x_project, a.x_project)

3

Re: Why there is a duplication

Kopelly, thanks. It seems worked

4

Re: Why there is a duplication

Kopelly wrote:

katish444;

on t.x_project=isnull (b.x_project, a.x_project)

Did not work. Helped more truly, only for those specific dates which have been installed. At change does not unite, doubles...
[spoiler]

Select pp. PROJECTCODE, pp. DESCRIPTION;
a. GeolLenght;
b. SendSample, c.assay, TakenSample
From
(
select dd. PROJECTCODE as x_project;
sum (cast ((case when name = ' totaldrilldepth ' then value end) as float)) as GeolLenght
from drillingdetail dd
where cast ([DRILLINGDATE] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE
) a
full join (
select s. PROJECTCODE as x_project;
count (sd.sampleid) as SendSample
from SAMPLEDESPATCH sd left join DESPATCHSEND ds on sd. DESPATCHNO=ds. DESPATCHNO left join sample s on s. SAMPLEID=sd. SAMPLEID
where cast ([senddate] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE
) b on a.x_project =b.x_project
full join
(Select projectcode as x_project, count (sss) assay from (select distinct s.sampleid as sss, PROJECTCODE
from cORPSAMPLEASSAY c left join sample s on s. SAMPLEID = c. SAMPLEID
where cast ([LOADDATE] as date) between ' 2017-02-13 ' and ' 2017-03-10 ') abc
group by abc. PROJECTCODE) c on a.x_project=isnull (b.x_project, c.x_project)
full join (
select ss. PROJECTCODE as x_project;
count (ts.sampleid) as TakenSample
from SAMPLEDETAILS ts left join sample ss on ss. SAMPLEID=ts. SAMPLEID
where cast ((case when name = ' SampDateTaken ' then value end) as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by ss. PROJECTCODE
) t on t.x_project=isnull (b.x_project, c.x_project)
left join PROJECT pp on pp. PROJECTCODE=c.x_project or pp. PROJECTCODE=b.x_project or pp. PROJECTCODE=a.x_project or pp. PROJECTCODE=t.x_project
group by pp.PROJECTCODE,a.GeolLenght,pp.DESCRIPTION;
b. SendSample, c.assay, TakenSample
order by pp. DESCRIPTION

[/spoiler]

5

Re: Why there is a duplication

katish444;
Remaining JOIN' too it is necessary to correct:

Select pp. PROJECTCODE, pp. DESCRIPTION;
a. GeolLenght;
b. SendSample, c.assay, TakenSample
From
(
select dd. PROJECTCODE as x_project;
sum (cast ((case when name = ' totaldrilldepth ' then value end) as float)) as GeolLenght
from drillingdetail dd
where cast ([DRILLINGDATE] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE
) a
full join (
select s. PROJECTCODE as x_project;
count (sd.sampleid) as SendSample
from SAMPLEDESPATCH sd left join DESPATCHSEND ds on sd. DESPATCHNO=ds. DESPATCHNO left join sample s on s. SAMPLEID=sd. SAMPLEID
where cast ([senddate] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE
) b on a.x_project =b.x_project
full join
(Select projectcode as x_project, count (sss) assay from (select distinct s.sampleid as sss, PROJECTCODE
from cORPSAMPLEASSAY c left join sample s on s. SAMPLEID = c. SAMPLEID
where cast ([LOADDATE] as date) between ' 2017-02-13 ' and ' 2017-03-10 ') abc
group by abc. PROJECTCODE) c on a.x_project=isnull (b.x_project, c.x_project)
full join (
select ss. PROJECTCODE as x_project;
count (ts.sampleid) as TakenSample
from SAMPLEDETAILS ts left join sample ss on ss. SAMPLEID=ts. SAMPLEID
where cast ((case when name = ' SampDateTaken ' then value end) as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by ss. PROJECTCODE
) t on t.x_project=COALESCE (a.x_project, b.x_project, c.x_project)
left join PROJECT pp on pp. PROJECTCODE=c.x_project or pp. PROJECTCODE=b.x_project or pp. PROJECTCODE=a.x_project or pp. PROJECTCODE=COALESCE (a.x_project,b.x_project,c.x_project,t.x_project)
group by pp.PROJECTCODE,a.GeolLenght,pp.DESCRIPTION;
b. SendSample, c.assay, TakenSample
order by pp. DESCRIPTION

6

Re: Why there is a duplication

Kopelly;
Why the script does not allow to use that COALESCE in others .  only what are involved above. That is in the lowermost table it is possible to use with everything, and in the table with, only with an and b, in the table b only with a. That is only with what are above in a script, differently

Msg 4104, Level 16, State 1, Line 20
The multi-part identifier "t.x_project" could not be bound.

7

Re: Why there is a duplication

katish444;
Such error should not be...
[spoiler]

Select pp. PROJECTCODE, pp. DESCRIPTION;
a. GeolLenght;
b. SendSample, c.assay, TakenSample
From
(
select dd. PROJECTCODE as x_project;
sum (cast ((case when name = ' totaldrilldepth ' then value end) as float)) as GeolLenght
from drillingdetail dd
where cast ([DRILLINGDATE] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE
) a
full join (
select s. PROJECTCODE as x_project;
count (sd.sampleid) as SendSample
from SAMPLEDESPATCH sd left join DESPATCHSEND ds on sd. DESPATCHNO=ds. DESPATCHNO left join sample s on s. SAMPLEID=sd. SAMPLEID
where cast ([senddate] as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by PROJECTCODE
) b on a.x_project =b.x_project
full join
(Select projectcode as x_project, count (sss) assay from (select distinct s.sampleid as sss, PROJECTCODE
from cORPSAMPLEASSAY c left join sample s on s. SAMPLEID = c. SAMPLEID
where cast ([LOADDATE] as date) between ' 2017-02-13 ' and ' 2017-03-10 ') abc
group by abc. PROJECTCODE) c on c.x_project=isnull (b.x_project, a.x_project)
full join (
select ss. PROJECTCODE as x_project;
count (ts.sampleid) as TakenSample
from SAMPLEDETAILS ts left join sample ss on ss. SAMPLEID=ts. SAMPLEID
where cast ((case when name = ' SampDateTaken ' then value end) as date) between ' 13-feb-2017 ' and ' 20-feb-2017'
group by ss. PROJECTCODE
) t on t.x_project=COALESCE (a.x_project, b.x_project, c.x_project)
left join PROJECT pp on pp. PROJECTCODE=COALESCE (a.x_project,b.x_project,c.x_project,t.x_project)
--group by pp.PROJECTCODE,a.GeolLenght,pp.DESCRIPTION,b.SendSample, c.assay, TakenSample
order by pp. DESCRIPTION

[/spoiler]

8

Re: Why there is a duplication

Kopelly, understood, thanks huge