View : Add a field from a table, with a filter.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

View : Add a field from a table, with a filter.

Author

Topic

lurked
Starting Member

6 Posts

Posted - 2009-09-11 : 10:44:33

I know the title is a bit hard to understand, but my issue is a bit complicated to explain in a few words... Though I think the answer is pretty simple, I'm only still a bit new in the awesome SQL world.

I have a table with description fields, here you can see a simple preview of what it looks like :

Table TradV2
CODE_INT  |  FICHE  |  CHAMP  |  CONTENU
00000001     11111     DESC      Description 1
00000002     11111     DESC2     Description 2 with more details
00000003     11111     NAME      Machine1 name
00000004     11112     DESC      Description 1
00000005     11112     NAME      Machine2 name
00000006     11113     NAME      Machine3 name
00000007     11113     DESC2     Description 2 with more details

And I'm creating a view, which gets its data from 5 different tables/views. The goal of the view, is to have a more detailed description of each product, in a list. Here is the request I'm using for the view :

SELECT DISTINCT 
                      dbo.prodv2.CODE_INT AS Ci_Prod, RIGHT(LEFT(dbo.prodv2.CODE, 6), 3) AS Client, dbo.prodv2.CODE AS Code_prod, 
                      dbo.prodv2.DESCRIP AS Desc_prod, dbo.prodv2.NOM AS Nom_prod, dbo.prodv2.REVDESS AS Rev_prod, dbo.prodv2.GENERIQ AS Generique, 
                      dbo.prodv2.DIVERS1 AS Mat_modele, dbo.prodv2.STATUT AS Statut_prod, dbo.prodv2.NOTSH AS CodeSH, dbo.prodv2.NOTSHUS AS CodeSHUS, 
                      dbo.prodv2.PAYSORIG AS Pays_Origine, dbo.prodv2.MEPBLOQ AS MEP_Blocage, dbo.Personnes.CODE AS Manufacturier, 
                      dbo.v_Rel_Prod_Domaine.Domaine, dbo.v_Rel_Prod_Domaine.Categorie AS CATEGORIE_NOM, dbo.TradV2.CONTENU AS desc2_prod
FROM         dbo.prodv2 LEFT OUTER JOIN
                      dbo.v_Rel_Prod_Domaine ON dbo.prodv2.CODE_INT = dbo.v_Rel_Prod_Domaine.CODE_INT LEFT OUTER JOIN
                      dbo.EtatV3 ON dbo.prodv2.CODE_INT = dbo.EtatV3.PRODUIT LEFT OUTER JOIN
                      dbo.Personnes ON dbo.prodv2.MANUF = dbo.Personnes.CODE_INT FULL OUTER JOIN
                      dbo.TradV2 ON dbo.prodv2.CODE_INT = dbo.TradV2.FICHE
WHERE     (dbo.TradV2.CHAMP = 'DESC2')

QUESTION :
So, my issue is that it returns only the records where there is a field where TradV2.CHAMP = 'DESC2'. If there isn't a value for DESC2 for the current product(or record), the line simply isnt returned. What can I do to filter TradV2.CHAMP to get the value of 'TradV2.CONTENU' only for the records where TradV2.CHAMP is 'DESC2'?

Using the table exemple that I wrote above, I would only get a result for product(FICHE) number 11111 and 11113, the 11112 wouldn't be returned because it doesn't have a record in TradV2 where CHAMP is 'DESC2'.

Any idea?
Thanks, Frank

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-11 : 10:53:16

[code]SELECT DISTINCT
dbo.prodv2.CODE_INT AS Ci_Prod, RIGHT(LEFT(dbo.prodv2.CODE, 6), 3) AS Client, dbo.prodv2.CODE AS Code_prod,
dbo.prodv2.DESCRIP AS Desc_prod, dbo.prodv2.NOM AS Nom_prod, dbo.prodv2.REVDESS AS Rev_prod, dbo.prodv2.GENERIQ AS Generique,
dbo.prodv2.DIVERS1 AS Mat_modele, dbo.prodv2.STATUT AS Statut_prod, dbo.prodv2.NOTSH AS CodeSH, dbo.prodv2.NOTSHUS AS CodeSHUS,
dbo.prodv2.PAYSORIG AS Pays_Origine, dbo.prodv2.MEPBLOQ AS MEP_Blocage, dbo.Personnes.CODE AS Manufacturier,
dbo.v_Rel_Prod_Domaine.Domaine, dbo.v_Rel_Prod_Domaine.Categorie AS CATEGORIE_NOM,
case
when dbo.TradV2.CHAMP = 'DESC2' then dbo.TradV2.CONTENU
else ''
end AS desc2_prod
FROM dbo.prodv2 LEFT OUTER JOIN
dbo.v_Rel_Prod_Domaine ON dbo.prodv2.CODE_INT = dbo.v_Rel_Prod_Domaine.CODE_INT LEFT OUTER JOIN
dbo.EtatV3 ON dbo.prodv2.CODE_INT = dbo.EtatV3.PRODUIT LEFT OUTER JOIN
dbo.Personnes ON dbo.prodv2.MANUF = dbo.Personnes.CODE_INT FULL OUTER JOIN
dbo.TradV2 ON dbo.prodv2.CODE_INT = dbo.TradV2.FICHE
--WHERE (dbo.TradV2.CHAMP = 'DESC2')
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

lurked
Starting Member

6 Posts

Posted - 2009-09-11 : 10:55:29

Woah, it works perfectly.

Thanks a LOT for the fast answer ;)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-11 : 10:57:44

my pleasure

No, you're never too old to Yak'n'Roll if you're too young to die.

lurked
Starting Member

6 Posts

Posted - 2009-09-11 : 11:12:38

I just modified my request using what you answered, and I had to add a second field using the same method. It works well, but it duplicates each record to add the second field. Like, there is never a record with the field desc2_prod AND desc_ang at the same time, and it added 30000records to the view... All those records are duplicates, the only difference between the 2 is that if desc2_Prob is filled in one record, it's empty in the other one.


SELECT DISTINCT 
                      dbo.prodv2.CODE_INT AS Ci_Prod, RIGHT(LEFT(dbo.prodv2.CODE, 6), 3) AS Client, dbo.prodv2.CODE AS Code_prod, 
                      dbo.prodv2.DESCRIP AS Desc_prod, dbo.prodv2.NOM AS Nom_prod, dbo.prodv2.REVDESS AS Rev_prod, dbo.prodv2.GENERIQ AS Generique, 
                      dbo.prodv2.DIVERS1 AS Mat_modele, dbo.prodv2.STATUT AS Statut_prod, dbo.prodv2.NOTSH AS CodeSH, dbo.prodv2.NOTSHUS AS CodeSHUS, 
                      dbo.prodv2.PAYSORIG AS Pays_Origine, dbo.prodv2.MEPBLOQ AS MEP_Blocage, dbo.Personnes.CODE AS Manufacturier, 
                      dbo.v_Rel_Prod_Domaine.Domaine, dbo.v_Rel_Prod_Domaine.Categorie AS CATEGORIE_NOM, 
                      CASE WHEN dbo.TradV2.CHAMP = 'DESC2' THEN dbo.TradV2.CONTENU ELSE '' END AS desc2_prod, 
                      CASE WHEN dbo.TradV2.CHAMP = 'DESC' THEN dbo.TradV2.CONTENU ELSE '' END AS desc_ang
FROM         dbo.prodv2 LEFT OUTER JOIN
                      dbo.v_Rel_Prod_Domaine ON dbo.prodv2.CODE_INT = dbo.v_Rel_Prod_Domaine.CODE_INT LEFT OUTER JOIN
                      dbo.EtatV3 ON dbo.prodv2.CODE_INT = dbo.EtatV3.PRODUIT LEFT OUTER JOIN
                      dbo.Personnes ON dbo.prodv2.MANUF = dbo.Personnes.CODE_INT LEFT OUTER JOIN
                      dbo.TradV2 ON dbo.prodv2.CODE_INT = dbo.TradV2.FICHE

Any idea why it started duplicating the records?

lurked
Starting Member

6 Posts

Posted - 2009-09-11 : 13:16:22

Nevermind this thread, I decided to create a second view and it works well, thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources