Query of Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query of Query

Author

Topic

jfm
Posting Yak Master

145 Posts

Posted - 2012-09-14 : 10:45:12

Hi there,

I know is friday, but I need your help.

I have 2 tables: F and R (R is part of F)

In F, I have the following columns: Type_Color (A,B or C), Last_info_date and ID

I need to take all the info from R, look in F and whenever F.ID= R.ID and create a New_Table WHERE I just take the last_info_date from A, the last_info_date from B and the last_info_date_from C

Any idea?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 10:51:21

[code]
INSERT NewTable
SELECT f.Type_color,MAX(last_info_date)
FROM F f
INNER JOIN R r
ON r.ID = f.ID
GROUP BY f.Type_color
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jfm
Posting Yak Master

145 Posts

Posted - 2012-09-14 : 11:09:58

Thanks,

I have the following error:
Msg 209 - Ambiguous column name

I modify a little bit the query:

INSERT NewTable
SELECT f.Type_color,MAX(last_info_date) as Last_extraction
FROM F f
INNER JOIN R r
ON r.ID = f.ID
GROUP BY f.Type_color

And still not working..

Does this query take from Type_Color Column, each of the 3 variables (A, B and C) and their last_extraction_date, into the new table?

Thanks a lot Visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 11:32:33

yep. it will
i think you had same column last_info_date in multiple tables which is why it errored. Try with an alias like below


INSERT NewTable
SELECT f.Type_color,MAX(f.last_info_date) as Last_extraction
FROM F f
INNER JOIN R r
ON r.ID = f.ID
GROUP BY f.Type_color

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jfm
Posting Yak Master

145 Posts

Posted - 2012-09-14 : 11:48:50

True,
last_info_date is in each table..

I tried using the alias, but I have
msg 208, level 16, state 1, line 2

INSERT NewTable
SELECT f.Type_color,MAX(f.last_info_date) as Last_extraction
FROM F f
INNER JOIN R r
ON r.ID = f.ID
GROUP BY f.Type_color

I might do sth wrong.. FROM F f ? how you define that, F is my table, but 'f' what's supposed to be?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 14:48:54

f is an alias (reference name) for table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jfm
Posting Yak Master

145 Posts

Posted - 2012-09-17 : 05:23:26

Perfect.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 10:53:42

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources