469,133 Members | 1,114 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,133 developers. It's quick & easy.

Need help adding 3rd table to a Join

Hi can someone help me I'm brainless, I need to add a field from a third database to this view

CREATE View mProduct_Search

As

Select

mProduct.*,

(IsNull(mProduct.PRICE,0) * 2) as [DISPLAY_PRICE],

(IsNull(mProduct.LINER_PRICE,0) * 2) as [DISPLAY_LINER_PRICE],

(mProduct.ITEM_ID + ' ' +IsNull(mProduct.[SHORT_DESCRIPTION],' ') + ' ' + IsNull(mProduct.[CAT_PG],' ') + ' '+ IsNull(mProduct.[CATEG],' ') + ' ' + IsNull(mProduct.[DESCRIPTION1],' ') + ' ' + IsNull(mProduct.[DESCRIPTION2],' ') + ' '

+ IsNull(mProduct.[DESCRIPTION3],' ')) as [GENARAL_SERACH],

A.PERC as [PERC], A.START_DATE AS [START_DATE], A.END_DATE AS [END_DATE], A.DESCR as [DESCR]

from mProduct Left Outer Join (Select * from mSpecialPricing ) A

ON mProduct.ITEM_ID = A.ITEM_ID


The field I need to add is COLLECTION, with Left Outer Join

mCollection
ITEM_ID
COLLECTION
Aug 7 '06 #1
3 1261
And COLLECTION is a member of which table?
Aug 8 '06 #2
I need the COLLECTION field from the mCollection Table to be included in the mProduct_Search view
Aug 9 '06 #3
Nevermind my brain came back to life, I have answered my own question...
CREATE View mProduct_Search

As

Select

mProduct.*,

(IsNull(mProduct.PRICE,0) * 2) as [DISPLAY_PRICE],

(IsNull(mProduct.LINER_PRICE,0) * 2) as [DISPLAY_LINER_PRICE],

(mProduct.ITEM_ID + ' ' +IsNull(mProduct.[SHORT_DESCRIPTION],' ') + ' ' + IsNull(mProduct.[CAT_PG],' ') + ' '+ IsNull(mProduct.[CATEG],' ') + ' ' + IsNull(mProduct.[DESCRIPTION1],' ') + ' ' + IsNull(mProduct.[DESCRIPTION2],' ') + ' '

+ IsNull(mProduct.[DESCRIPTION3],' ')) as [GENARAL_SERACH], B.COLLECTIONS as [COLLECTIONS],

A.PERC as [PERC], A.START_DATE AS [START_DATE], A.END_DATE AS [END_DATE], A.DESCR as [DESCR]

from mProduct Left Outer Join (Select * from mSpecialPricing ) A

ON mProduct.ITEM_ID = A.ITEM_ID

Left Outer Join (Select * from mCollections) B

on mProduct.ITEM_ID = B.ITEM_ID
Aug 9 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by netpurpose | last post: by
2 posts views Thread by Viorel | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.