Can Redundant Elements in SQL field be removed?

[IamScott]

Help!

Below is a snippet of MS SQL inside an ASP page that retieves Commodity info such as product names and related information and returns the results in an ASP Page. My problem is that with certain searches, elements returned in the synonym field repeat. For instance, on a correct search I get back green, red, blue, and yellow which is correct. On another similar search with a different commodity say for material, I get: Plastic, Glass,Sand - Plastic, Glass,Sand - Plastic Glass Sand. I want to remove the repeating elements returned in this field. I hope this makes sense.

PS I tried to use distinct but with no luck I want just one of each in the example below.

Thanks in Advance!

Scott

==============================

SQL = ""
SQL = "SELECT B.CIMS_MSDS_NUM," & _
"A.COMMODITY_NUMBER, " & _
"B.CIMS_TRADE_NME," & _
"B.CIMS_MFR_NME," & _
"B.CIMS_MSDS_PREP_DTE," & _
"B.APVL_CDE," & _
"COALESCE(C.REGDMATLCD,'?') AS DOTREGD," & _
"COALESCE( D.CIMS_TRADE_SYNM,'NO SYNONYMS') AS SYNONYM, " & _
"A.MSDS_CMDTY_VERIF, " & _
"A.CATALOG_ID " & _
"FROM ( MATEQUIP.VMSDS_CMDTY A " & _
" RIGHT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_INF B " & _
" ON A.CIMS_MSDS_NUM = B.CIMS_MSDS_NUM " & _
" LEFT OUTER JOIN MATEQUIP.VDOT_TRADE_PROD C " & _
" ON A.CIMS_MSDS_NUM = C.MSDSNUM " & _
" LEFT OUTER JOIN MATEQUIP.VCIMS_TRD_PROD_SYN D " & _
" ON B.CIMS_MSDS_NUM = D.CIMS_MSDS_NUM) "

SQL1 = ""
SQL1 = SQL

SQL = SQL & "WHERE " & Where & " "
==================================

Here is a segment of the problem field, note repeating colors etc.

CCM-PAINTS & COATINGS (1/26/98)F65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65A2 LIGHT GRAY F65L7 PALE BLUEF65A1 WARM GRAY F65L6 TURQUOISEF65B4 SEMI-GLOSS BLACK F65R1 VERMILIONF65B1 GLOSS BLACK F65N11 RICH BROWNF65A49 ASA #49 GRAY F65M1 MAROONF65A4 MACHINE TOOL GRAY F65L10 BRIGHT BLUEF65L4 DARK BLUEF65L3 LIGHT BLUE V65V100 MIXING CLEARF65H1 IVORY F65Y48 LIGHT YELLOWF65G41 FOREST GREEN F65Y44 LEMON YELLOWF65G40 MEDIUM GREEN F65W100 MIXING WHITEF65G39 LIGHT GREEN F65W4 TINTING WHITEF65G16 SEMI-GLOSS MACHINERY GRE F65W3 CUSTOM WHITEF65E37 INTERNATIONAL ORANGE F65W2 SEMI-GLOSS WHITEF65E36 ORANGE F65W1 GLOSS WHITEF65B50 WR. IRON FLAT BLACK F65R2 TARTAR RED DARKF65A1 WARM GRAY F65L6 TURQUOISEDISAPPROVED BY CCM-PAINTS & COATINGS (1/26/98)F65B1 GLOSS BLACK F65N11

 

[rac2]

One reason for results like those you describe is that one of the tables being joined has two or more rows which match a key value in the basic table. A table of synonyns would be a likely culprit. An example might be a product named Gizmo might have synonyns Gagdet and Widget. If the Gizmo comes in Plastic, Glass, and Sand and you join the basic Products table to the Synonyms on a product_id key, then you will get two sets of rows, one for the Gadget and another for the Widget. It can be quite challenging to reduce that to one set of rows; it depends on how you wish to eliminate the extra set, that is, how you decide which set is extra.

Another issue might be the use of the RIGHT JOIN. This means that you always wish to obtain all of the rows in VCIMS_TRD_PROD_INF for a particular B.CIMS_MSDS_NUM value. And regardless of whether there are corresponding rows in VMSDS_CMDTY with that value. Is that the case?

Here is a simplified version of the JOIN clauses used in your query.

...
FROM  VMSDS_CMDTY A 
RIGHT JOIN VCIMS_TRD_PROD_INF B 
         ON A.CIMS_MSDS_NUM = B.CIMS_MSDS_NUM 
LEFT JOIN VDOT_TRADE_PROD C 
         ON A.CIMS_MSDS_NUM = C.MSDSNUM 
LEFT JOIN VCIMS_TRD_PROD_SYN D 
         ON B.CIMS_MSDS_NUM = D.CIMS_MSDS_NUM 
...

Which is the basic table underlying the query, A, B, C, or D?

Which tables have a one-to-many relationship with the basic table?

Is the synonym table VCIMS_TRD_PROD_SYN involved merely for the purpose of searching? In other words, if the WHERE clause does not involve columns in this table then could we leave it out of the JOINs? What is in the WHERE clause?

 

[SQLBill]

In cases like this, it really helps to have the schema (table & column names), sample data, and the results you are getting and the results you want to get.

Without that, I'll suggest that maybe they aren't duplicates:

Glass
Glass
Glass (Space after the word)

are not the same thing. Spaces and other characters can make similar items unique. Also, these are not duplicates

OrderNbr ProductName
1001 Glass
1003 Glass

-SQLBill

Posting advice: FAQ481-4875