CODEWITHSUNDEEP
sqlsql-server
tomik
tomik

Reputation: 45

SQL select from nested FROM

I am trying to run sth like following, but it keeps on giving me null 'Value2' in case ID_period is not 10. I've tried WITH clause, but the script is too complex to provide multiple selects from that.

Example:

SELECT Y.ID_period, Y.ID_country, Y.Value,
       (select Y.Value where ID_period = 10 and ID_country = Y.ID_country) as Value2
FROM (select A.ID_period, A.ID_country, A.Value
      from dbo.xyz as A) Y

Thanks for any idea :)

Upvotes: 0

Views: 147

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239714

Here's one that avoids the full re-query on the assumption that the "Period" extracting subquery (A) will be cheaper than the full re-query. If it's not, it's unlikely to be much of an improvement:

declare @t table (Period_ID int, Country varchar(10),Value int)
insert into @t(Period_ID,Country,Value) values
(0,'UK',0),(10,'UK',10),(20,'UK',20),(30,'UK',30)

select
    *
from (
select
    COALESCE(A.Period_ID,Y.Period_ID) as Period_ID,
    Y.Country,
    Y.Value,
    Z.Col
from
    (select * from @t) Y
        cross apply
    (select CASE WHEN y.Period_ID = 10 THEN 'Value2' ELSE 'Value1' END as Col) Z
        outer apply
    (select Period_ID from @t t where t.Country = Y.Country
     and Y.Period_ID = 10 and t.Period_ID != 10) A
) B
pivot (MAX(Value) for Col in (Value1,Value2)) C

Result:

Period_ID   Country    Value1      Value2
----------- ---------- ----------- -----------
0           UK         0           10
20          UK         20          10
30          UK         30          10

Note that I'm excluding a row being produced for Period_ID 10 since we've still not established via the comments whether that row is desirable (and if it is, it's definitely more work to do)

(If you take out the t.Period_ID != 10 filter in A, you'll get a row back for Period_ID 10, but it has NULL for Value. I guess we could fix that with a further COALESCE).

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

self left join can help you

if you have any special condition then you can use sub-query

  SELECT Y.ID_period, Y.ID_country, Y.Value, X.value as value2     
    FROM
    (
     select A.ID_period, A.ID_country, A.Value
     from dbo.xyz as A  // where condition
      ) Y
    left join
    (
    select A.ID_period, A.ID_country, A.Value
    from dbo.xyz   // where condition
    ) as x
      on  x.ID_country = Y.ID_country and x.ID_period=10

if no condition then simple self join can work for you what already said @Tim in his answer

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521513

I would rephrase this as a self join:

SELECT
    t1.ID_period,
    t1.ID_country,
    t1.Value,
    COALESCE(t2.Value, 'NA') AS Value2
FROM dbo.xyz t1
LEFT JOIN dbo.xyz t2
    ON t1.ID_country = t2.ID_country AND t2.ID_period = 10;

Upvotes: 3

Related Questions