CODEWITHSUNDEEP
mysqlsql
Ross Cooper
Ross Cooper

Reputation: 245

How to add new column into temp table

I have a stored procedure in which I am getting some data into a temp table (temp2). The data is like: 

component_id        pub_id
52663               12345
52663               12346
52663               12347
52663               12348
45632               21456
45632               21457
45632               21458

Now I wish to iterate through the items of column pub_id and for each pub_id, find value of article date in some other table. I am using the following code:

SET @getid = CURSOR FOR select pub_id from #temp2
OPEN @getid
FETCH NEXT
FROM @getid INTO @pub_id
WHILE @@FETCH_STATUS = 0

BEGIN

Select Date_Value from CUSTOM_META where ITEM_ID = @pub_id and KEY_NAME = 'ArticleDate'

FETCH NEXT
FROM @getid INTO @pub_id

END

Though I am able to get the dates for each pub_id, I do not know how to insert them beside each respective pub_id. I want the output to be like:

component_id        pub_id     Date_Value
    52663           12345      12/11/11
    52663           12346      12/23/12
    52663           12347      01/30/13
    52663           12348      09/23/07
    45632           21456      09/23/07
    45632           21457      09/23/07
    45632           21458      09/23/07

How can I achieve this output?

Upvotes: 0

Views: 62

Answers (1)

user3248346
user3248346

Reputation:

Try this:

SELECT t.component_id, t.pub_id, m.Date_Value
FROM #temp2 t
JOIN CUSTOM_META m
ON t.pub_id = m.ITEM_ID
AND m.KEY_NAME = 'ArticleDate'

Upvotes: 1

Related Questions