CODEWITHSUNDEEP
sqlsql-server
J Santosh
J Santosh

Reputation: 3847

SQL Joins . One to many relationship

I have two tables as below 

Table 1
-----------------------------------
UserID   | UserName | Age | Salary
-----------------------------------
1        | foo      | 22  | 33000       
-----------------------------------

Table 2
------------------------------------------------
UserID   | Age      | Salary     | CreatedDate
------------------------------------------------
1        | NULL     | 35000      | 2015-01-01
------------------------------------------------
1        |  28      | NULL       | 2015-02-01
------------------------------------------------
1        |  NULL    | 28000      | 2015-03-01
------------------------------------------------

I need the result like this.

Result
-----------------------------------
UserID   | UserName | Age | Salary
-----------------------------------
1        | foo      | 28  | 28000
-----------------------------------

This is just an example. In my real project I have around 6 columns like Age and Salary in above tables.

In table 2 , each record will have only have one value i.e if Age has value then Salary will be NULL and viceversa.

UPDATE :

Table 2 has CreatedDate Column. So i want to get latest "NOTNULL" CELL Value instead of maximum value.

Upvotes: 5

Views: 178

Answers (4)

Felix Pamittan
Felix Pamittan

Reputation: 31879

To get the latest value based on CreatedDate, you can use ROW_NUMBER to filter for latest rows. Here the partition is based UserID and the other columns, Age and Salary.

SQL Fiddle

;WITH Cte AS(
    SELECT
        UserID,
        Age = MAX(Age),
        Salary = MAX(Salary)
    FROM(
        SELECT *, Rn = ROW_NUMBER() OVER(
                        PARTITION BY 
                            UserID, 
                            CASE 
                                WHEN Age IS NOT NULL THEN 1
                                WHEN  Salary IS NOT NULL THEN 2
                            END
                        ORDER BY CreatedDate DESC
                        )
        FROM Table2
    )t
    WHERE Rn = 1
    GROUP BY UserID
)
SELECT
    t.UserID,
    t.UserName,
    Age = ISNULL(c.Age, t.Age),
    Salary = ISNULL(c.Salary, t.Salary)
FROM Table1 t
LEFT JOIN Cte c
    ON t.UserID = c.UserID

Upvotes: 2

Biswabid
Biswabid

Reputation: 1411

following query should work(working fine in MSSQL) :

select a.userID,a.username,b.age,b.sal from <table1> a
inner join 
(select userID,MAX(age) age,MAX(sal) sal from <table2> group by userID) b
on a.userID=b.userID

Upvotes: 0

lc.
lc.

Reputation: 116448

Note: I'm giving you the benefit of the doubt that you know what you're doing, and you just haven't told us everything about your schema.

It looks like Table 2 is actually an "updates" table, in which each row contains a delta of changes to apply to the base entity in Table 1. In which case you can retrieve each column's data with a correlated join (technically an outer-apply) and put the results together. Something like the following:

select a.UserID, a.UserName, 
    coalesce(aAge.Age, a.Age),
    coalesce(aSalary.Salary, a.Salary)
from [Table 1] a
outer apply (
    select Age
    from [Table 2] x
    where x.UserID = a.UserID 
    and x.Age is not null
    and not exists (
        select 1
        from [Table 2] y
        where x.UserID = y.UserID
        and y.Id > x.Id
        and y.Age is not null
    )
) aAge,
outer apply (
    select Salary
    from [Table 2] x
    where x.UserID = a.UserID 
    and x.Salary is not null
    and not exists (
        select 1
        from [Table 2] y
        where x.UserID = y.UserID
        and y.Id > x.Id
        and y.Salary is not null
    )
) aSalary

Do note I am assuming you have at minimum an Id column in Table 2 which is monotonically increasing with each insert. If you have a "change time" column, use this instead to get the latest row, as it is better.

Upvotes: 3

Raging Bull
Raging Bull

Reputation: 18737

You can get this done using a simple MAX() and GROUP BY:

select t1.userid,t1.username, MAX(t2.Age) as Age, MAX(t2.Salary) as Salary
from table1 t1 join
     table2 t2 on t1.userid=t2.userid
group by t1.userid,t1.username

Result:

userid  username    Age   Salary
--------------------------------
1       foo         28    35000

Sample result in SQL Fiddle

Upvotes: 4

Related Questions