Add another field - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Add another field

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2010-11-02 : 15:09:08

I have this sql working ok:

SELECT pc.ProductCode_ID, pc.ProductCode,
CASE
WHEN EXISTS (SELECT ProductCodeRef_ID FROM ProductCodeRef
WHERE ProductCode = pc.ProductCode_ID
AND BusinessPartner = @BP) THEN 1
ELSE 0
END 'Active'
FROM ProductCode pc

I now need to add another field somehow. The field name is zones and is in the ProductCodeRef table. This is an int.

I need all records from ProductCode then mark each row as active or not. Now I need the other field in there.

Suggestions?

Thanks,

Zath

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-02 : 15:13:49

Join to that table to include that additional column.

If you need further help, then please post sample data to make your issue more clear.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2010-11-02 : 15:33:23

This perhaps?


SELECT pc.ProductCode_ID, 
    pc.ProductCode, 
    CASE WHEN PCR.ProductCodeRef_ID IS NULL THEN 0 ELSE 1 END AS [Active],
    PCR.zones 
FROM ProductCode AS pc
    LEFT OUTER JOIN ProductCodeRef AS PCR
        ON PCR.ProductCode = pc.ProductCode_ID
       AND PCR.BusinessPartner = @BP

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2010-11-02 : 15:36:51

That did the trick Kristen.

THANKS!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources