CODEWITHSUNDEEP
sqlsql-server
user1437001
user1437001

Reputation: 53

How to add an independent column into a query result

I try to googling for this issue and still couldn't find the solution. I have 2 column to combine like below:

First table:

ID  Name    Item
42  america 1433
42  america 1695
42  america 1234
8   london  1433
8   london  1705
8   london  1432

Second table:

ID  CreatedBy
42  John
42  Erica
8   Amy Song
8   Alfred

If I just join both table it will become like this:

ID  Name    Item    CreatedBy
42  america 1433    John
42  america 1695    John
42  america 1234    John
42  america 1433    Erica
42  america 1695    Erica
42  america 1234    Erica
8   london  1433    Amy Song
8   london  1705    Amy Song
8   london  1432    Amy Song
8   london  1433    Alfred
8   london  1705    Alfred
8   london  1432    Alfred

Column Created By is only related to ID, and not related to Name and Item. I just want to insert CreatedBy as a new column into first table that only linked to ID, so the expected result will be like below. Is there any way I could get result like this?

ID  Name    Item    CreatedBy
42  america 1433    John
42  america 1695    Erica
42  america 1234    NULL
8   london  1433    Amy Song
8   london  1705    Alfred
8   london  1432    NULL

Let say we change second table like this:

Second table:

ID  CreatedBy
42  John
8   Amy Song

Result will be like this:

ID  Name    Item    CreatedBy
42  america 1433    John
42  america 1695    NULL
42  america 1234    NULL
8   london  1433    Amy Song
8   london  1705    NULL
8   london  1432    NULL

I really apreacite for your help, and thank you in advanced masta.

NOTE: Column CreatedBy is the independent column that only relate to ID and not relate to Name and Item.

Upvotes: 1

Views: 923

Answers (1)

Jorge Campos
Jorge Campos

Reputation: 23361

So, I will answer you even though some guys that discussed it here on comments may disagree as there is no a proper logic to your problem.

It is important to note that this answer is for this SPECIFIC case if it won't get you the right data for a larger dataset you have to provide more info on it.

What I did:

I've created a subset from the first set ordering it (and indexing it, window function) by the name then I created another subset from the second set ordering it by the id so, i have:

select id, name, item, row_number() over (partition by name order by name) idx from tablea

And 

select id, createdby, row_number() over (partition by id order by id) idx from tableb

Then I LEFT JOIN the tablea with tableb by the id and the order created colum idx so

select a.id, a.name, a.item, b.createdby 
  from (select id, name, item, row_number() over (partition by name order by name) idx from tablea) a
       left join 
       (select id, createdby, row_number() over (partition by id order by id) idx from tableb) b 
              on (a.id = b.id and a.idx=b.idx)
  order by a.name

The order by is just to get the result as you asked on your question.

See it here: http://sqlfiddle.com/#!3/7ef70/3

For your second sample data: http://sqlfiddle.com/#!3/675625/1

Upvotes: 1

Related Questions