April 5, 2011 at 1:21 pm
Good Afternoon,
The data im using is from an ERP, this means i have to adapt to the data and the data can't be adapted to me.
I have this table which has 5 Categories of Products (i.e. Category/Type/Line/Quality/Size) configured in same place but pointing to different Id's that comes from another table which holds the configuration for each category like this
Example:
TABLE NAME: CONFIGURATION
IDWindow IDField IDCategory NameDescription
XXXXXXX 1 1 YYYYYYYY
XXXXXXX 1 2 ZZZZZZZZ
XXXXXXX 2 3 RRRRRRRRR
XXXXXXX 2 4 PPPPPPPPP
******** SAME GOES FOR 3 MORE IDFields ******************
This previous table holds then the different things u can find to configure the category of the product
Now after configure the product the tables turns in something like this.
TABLE NAME: PRODUCTCATEGORY
IDWindow IDProduct IDField IDCategory
XXXXXXX VVVVVVV 1 1
XXXXXXX VFVFVFVF 1 2
XXXXXXX VVVVVVV 2 3
XXXXXXX VFVFVFVF 2 4
******** SAME GOES FOR 3 MORE IDFields ******************
Notice there is no description on this table and the way i need the data for an integration is like
IDProduct NameDescriptionIDField1 NameDescriptionIDField2 ...... n
VVVVVVV YYYYYYYY RRRRRRR
VFVFVFVF ZZZZZZZZ PPPPPPP
******** SAME GOES FOR 3 MORE IDFields ******************
So what i did is this:
Built a view with subquery as this:
SELECT PRODUCTCATEGORY.IDPRODUCT, PRODUCTCATEGORY.IDFIELD,
SQUERYTABLECATEGORY.NAMECATEGORY,
SQUERYTABLETYPE.NAMETYPE,
SQUERYTABLELINE.NAMELINE,
SQUERYTABLEQUALITY.NAMEQUALITY,
SQUERYTABLESIZE.NAMESIZE
FROM
TABLECONF LEFT OUTER JOIN
(SELECT IDFIELD, IDCATEGORY, NAMEDESCRIPTION AS NAMECATEGORY FROM CONFIGURATION WHERE IDFIELD = 1) AS CATEGORY ON PRODUCTCATEGORY.IDFIELD = CATEGORY.IDFIELD AND PRODUCTCATEGORY.IDCATEGORY = CATEGORY.IDCATEGORY
LEFT OUTER JOIN
(SELECT IDFIELD, IDCATEGORY, NAMEDESCRIPTION AS NAMECATEGORY FROM CONFIGURATION WHERE IDFIELD = 2) AS TYPE ON PRODUCTCATEGORY.IDFIELD = TYPE.IDFIELD AND PRODUCTCATEGORY.IDCATEGORY = TYPE.IDCATEGORY
The results from this query gives me a single result for each IDFIELD and where it brings values shows it, but where not only NULL values and i want it on a single result, and the way i could figure this out but not a good practice is by saving this query as a view and making another query with new subqueries to get the results i want....
Maybe im doing something wrong or there is a better way to approach this and i havent figured out...
All the help provided would be awesome...
#SQLHelp
April 5, 2011 at 1:43 pm
Is this what you are looking for?
SELECT PRODUCTCATEGORY.IDPRODUCT,
PRODUCTCATEGORY.IDFIELD,
SQUERYTABLECATEGORY.NAMECATEGORY,
SQUERYTABLETYPE.NAMETYPE,
SQUERYTABLELINE.NAMELINE,
SQUERYTABLEQUALITY.NAMEQUALITY,
SQUERYTABLESIZE.NAMESIZEFROMTABLECONF,
CATEGORY.NAMEDESCRIPTION AS NAMECATEGORY,
TYPE.NAMEDESCRIPTION AS NAMETYPE
LEFT JOIN CONFIGURATION AS CATEGORY
ON PRODUCTCATEGORY.IDFIELD = CATEGORY.IDFIELD
AND PRODUCTCATEGORY.IDCATEGORY = CATEGORY.IDCATEGORY
And (Category.IDFIELD = 1
Or Category.IDFIELD Is Null)
LEFT JOIN CONFIGURATION AS TYPE
ON PRODUCTCATEGORY.IDFIELD = TYPE.IDFIELD
AND PRODUCTCATEGORY.IDCATEGORY = TYPE.IDCATEGORY
And (TYPE.IDFIELD = 2
Or TYPE.IDFIELD Is Null)
Where Category.IDFIELD Is Not Null
Or TYPE.IDFIELD Is Not Null
April 5, 2011 at 2:01 pm
Thanks for quick Reply Robert
Sadly no, that will bring me the same data, lets see if with this example i can demonstrate what brings the query
Here you can see that for same productId it repeats the same number of IDFIeld...
Even if i Group By taking off the select list the IDField will bring the same result since i have to validate the data against the IDField and IDCategory mandatory... follow what i mean??
April 5, 2011 at 2:39 pm
Guys i found a better approach to my problem,
Instead of using as master table the ProductCategory one, im using the MaintenanteItem Table that way i wont have repeated records....
Best Regards and thanks for the help
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply