Help Creating SQL view to join product name and version name

Help Creating SQL view to join product name and version name

Home » Kartris ASP.NET Ecommerce Software » Kartris technical issues

https://forum.kartris.com/Topic446.aspx

Print Topic | Close Window

By saturation - Tue 15 Mar 2011

I need to create a SQL view that shows the name of the product in one column, each of the version names for each product in the next, and then each of the attributes as columns after the version. I'm not sure how to join the product and version tables with the tblKartrisLanguageElements--it's a little confusing.

Can anyone help?

By Mohammad - Wed 16 Mar 2011

Try this one:


SELECT  vKartrisProductsVersions.P_Name, vKartrisProductsVersions.V_Name, 
              vKartrisTypeAttributes.ATTRIB_Name, vKartrisTypeAttributeValues.ATTRIBV_Value
FROM     vKartrisTypeAttributeValues INNER JOIN  vKartrisTypeAttributes ON 
              vKartrisTypeAttributeValues.ATTRIBV_AttributeID = vKartrisTypeAttributes.ATTRIB_ID 
              INNER JOIN vKartrisProductsVersions ON vKartrisTypeAttributes.LANG_ID = 
              vKartrisProductsVersions.LANG_ID AND 
              vKartrisTypeAttributeValues.ATTRIBV_ProductID = vKartrisProductsVersions.P_ID

By saturation - Thu 17 Mar 2011

Thanks, Mohammad. That helped.

How do the product and version tables actually join with the language elements table? I can't seem to figure it out--I'm trying to create my own custom view and would like only the necessary fields joined together rather than joining SQL views with other SQL views that bloat the view I'm trying to use.

Any help is appreciated.

By Mohammad - Fri 18 Mar 2011

Hi.

You can study the views that are prefixed by vKartrisType like:
vKartrisTypeCategories, vKartrisTypeProducts, vKartrisTypeVersions .. etc.

All those views contain the join of language elements table with a language element type table(products, categories, attributes, versions, news ..etc), so you can for example export Create View SQL Script for any of those views, then change the name of the view and select the needed fields.

By saturation - Sat 19 Mar 2011

Thanks! That definitely helped me out.