471,607 Members | 1,741 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,607 software developers and data experts.

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 1296
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.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.