CODEWITHSUNDEEP
sqljoin
user641812
user641812

Reputation: 335

Join Between view and three tables

I have two view that result like as shown below and

enter image description here

I have lookup table for FeatureId containing also other information but here i took only required column

enter image description here

I have lookup table for parameter containing also other information but here i took only required column

enter image description here

Every featureId collects all parameters: Question: Now I want my final resultet should list all parameter and featureId with totalcount + when have FeatureId, Parameter is not present in resultset then totalcount should be Zero

Example 1. ASt-1 is not at all present in resultset so there will be 10 records with featureid ASt-1 containing all parameters with TotalCount as zero

  1. ASt-11 has one record in resultset for parameter NO2 so there will be 10 records with featureid ASt-11 containing 9 parameters with TotalCount as zero and one Parameter i,e NO2 with value 1

Upvotes: 0

Views: 34

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

Use a cross join to generate all the rows. Then bring in the existing information using a left join and group by:

select f.featureid, p.parameterid, count(lu.featureid)
from (select featureid from features f) f cross join
     (select distinct parameterid from lookup) p left join
     lookup lu
     on lu.featureid = f.featureid and lu.parameterid = lu.parameterid
group by f.featureid, p.parameterid;

Upvotes: 1

Related Questions