Help with SQL 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

Other Forums

MS Access

Help with SQL query

Author

Topic

yesmein
Starting Member

1 Post

Posted - 2009-04-17 : 17:43:29

Hello;
I have the following tables and columns:
Faci (Table); PNum (Column)
FaciGrp (Table); PNum, GrID (Columns)
Profile (Table): PNum, Criteria, Value (Columns)

The content in the tables is as follows:
Faci
PNum
101
102
103
104

FaciGrp
PNum GrID
101 2002
101 2003
102 2002
103 2004

I need to transfer all the ProvNum in Facility table to Profile table and also group id's that have 2002 and put "Yes" or "No" in the values column. So my profile table would look like

Profile
PNum Criteria Value
101 2002 Yes
102 2002 Yes
103 2002 No
104 2002 No

I am having trouble getting the sql that can do this. Can anyone please help?

Thanks a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-18 : 15:47:10

[code]
INSERT INTO Profile (PNum, Criteria, Value)
SELECT f.PNum,2002 AS Criteria,
CASE WHEN fg.PNum IS NULL 'No' ELSE 'Yes' END AS Value
FROM Faci f
LEFT JOIN FaciGrp fg
ON fg.PNum=f.PNum
AND fg.GrID=2002

--see the inserted rows back
SELECT * FROM Profile
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources