none
Duvida - Select - RRS feed

  • Pergunta

  • Pessoal, como ficaria a consulta abaixo usando LEFT JOIN/ INNER JOIN?
        
        
        SELECT
            PV.NuOrder,
            PT.idProductType,
            PT.DsName AS ProductType,
            DG.idProductDescriptionGroup,
            DG.DsName AS [Group],
            DF.idProductDescriptionFields,
            DF.DsName AS Field,
            DF.fgFieldsValue,
            PV.DsFieldValue,
            CASE WHEN PV.idProductDescriptionValue IS NULL THEN 0 ELSE PV.idProductDescriptionValue END AS idProductDescriptionValue,
            DF.fgFieldsValue
        FROM
            tbProductType PT,
            tbProductTypeDescriptionGroup PTG,
            tbProductDescriptionGroup DG,
            tbProductDescriptionGroupFields PGF,
            tbProductDescriptionFields DF,
            tbProductDescriptionValue PV
        WHERE
            PT.idProductType = PTG.idProductType AND
            PTG.idProductDescriptionGroup = DG.idProductDescriptionGroup AND
            PGF.idProductDescriptionGroup = DG.idProductDescriptionGroup AND
            PGF.idProductDescriptionFields = DF.idProductDescriptionFields AND
            PT.idProductType = @idProductType AND
            PGF.idProductDescriptionGroup *= PV.idProductDescriptionGroup AND
            PGF.idProductDescriptionFields *= PV.idProductDescriptionFields AND
            PV.idProduct = @idProduct
        ORDER BY
            PT.idProductType,DF.fgFieldsValue,DF.DsName
    quinta-feira, 13 de dezembro de 2012 13:24

Respostas

  • tente assim:

    SELECT 
            PV.NuOrder, 
            PT.idProductType, 
            PT.DsName AS ProductType, 
            DG.idProductDescriptionGroup, 
            DG.DsName AS [Group], 
            DF.idProductDescriptionFields, 
            DF.DsName AS Field, 
            DF.fgFieldsValue,
             PV.DsFieldValue, 
            CASE WHEN PV.idProductDescriptionValue IS NULL THEN 0 ELSE PV.idProductDescriptionValue END AS idProductDescriptionValue,
             DF.fgFieldsValue
         FROM tbProductType as PT
         inner join tbProductTypeDescriptionGroup as PTG on PT.idProductType = PTG.idProductType 
         inner join tbProductDescriptionGroup as DG on PTG.idProductDescriptionGroup = DG.idProductDescriptionGroup 
         inner join tbProductDescriptionGroupFields as PGF on PGF.idProductDescriptionGroup = DG.idProductDescriptionGroup 
         inner join tbProductDescriptionFields as DF on PGF.idProductDescriptionFields = DF.idProductDescriptionFields
         left join tbProductDescriptionValue as PV on PGF.idProductDescriptionGroup = PV.idProductDescriptionGroup
    		and PGF.idProductDescriptionFields = PV.idProductDescriptionFields
         WHERE PT.idProductType = @idProductType 
         and PV.idProduct = @idProduct
         ORDER BY PT.idProductType,DF.fgFieldsValue,DF.DsName


    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    Alexandre só uma obs.

    se deixar a condição

    and PV.idProduct = @idProduct

    dentro do WHERE o LEFT JOIN ira funcionar com inner join


    Se a minha ajuda lhe for útil não esqueça de classificar. Att. Leonardo Marcelino

    • Sugerido como Resposta Roberson Naves sexta-feira, 14 de dezembro de 2012 19:26
    • Marcado como Resposta Ricardo Russo quarta-feira, 2 de janeiro de 2013 12:46
    quinta-feira, 13 de dezembro de 2012 13:38

Todas as Respostas

  • montei aqui, veja se retorna o resultado esperado.

    SELECT 
            PV.NuOrder, 
            PT.idProductType, 
            PT.DsName AS ProductType, 
            DG.idProductDescriptionGroup, 
            DG.DsName AS [Group], 
            DF.idProductDescriptionFields, 
            DF.DsName AS Field, 
            DF.fgFieldsValue,
            PV.DsFieldValue, 
            CASE WHEN PV.idProductDescriptionValue IS NULL THEN 0 ELSE PV.idProductDescriptionValue END AS idProductDescriptionValue, 
            DF.fgFieldsValue
    FROM 
            tbProductType                   AS PT  
    INNER JOIN 
            tbProductTypeDescriptionGroup   AS PTG ON PT.idProductType = PTG.idProductType 
    INNER JOIN
            tbProductDescriptionGroup       AS DG  ON PTG.idProductDescriptionGroup = DG.idProductDescriptionGroup 
    INNER JOIN
            tbProductDescriptionGroupFields AS PGF ON PGF.idProductDescriptionGroup = DG.idProductDescriptionGroup 
    INNER JOIN
            tbProductDescriptionFields      AS DF  ON PGF.idProductDescriptionFields = DF.idProductDescriptionFields 
    LEFT JOIN
            tbProductDescriptionValue       AS PV  ON PGF.idProductDescriptionGroup = PV.idProductDescriptionGroup AND 
                                                      PGF.idProductDescriptionFields = PV.idProductDescriptionFields AND 
                                                      PV.idProduct = @idProduct
    WHERE 
        PT.idProductType = @idProductType 
    ORDER BY 
        PT.idProductType,DF.fgFieldsValue,DF.DsName


    Se a minha ajuda lhe for útil não esqueça de classificar. Att. Leonardo Marcelino


    • Editado Leonardo Marcelino quinta-feira, 13 de dezembro de 2012 13:34 ficou um AND a mais
    quinta-feira, 13 de dezembro de 2012 13:33
  • tente assim:

    SELECT 
            PV.NuOrder, 
            PT.idProductType, 
            PT.DsName AS ProductType, 
            DG.idProductDescriptionGroup, 
            DG.DsName AS [Group], 
            DF.idProductDescriptionFields, 
            DF.DsName AS Field, 
            DF.fgFieldsValue,
             PV.DsFieldValue, 
            CASE WHEN PV.idProductDescriptionValue IS NULL THEN 0 ELSE PV.idProductDescriptionValue END AS idProductDescriptionValue,
             DF.fgFieldsValue
         FROM tbProductType as PT
         inner join tbProductTypeDescriptionGroup as PTG on PT.idProductType = PTG.idProductType 
         inner join tbProductDescriptionGroup as DG on PTG.idProductDescriptionGroup = DG.idProductDescriptionGroup 
         inner join tbProductDescriptionGroupFields as PGF on PGF.idProductDescriptionGroup = DG.idProductDescriptionGroup 
         inner join tbProductDescriptionFields as DF on PGF.idProductDescriptionFields = DF.idProductDescriptionFields
         left join tbProductDescriptionValue as PV on PGF.idProductDescriptionGroup = PV.idProductDescriptionGroup
    		and PGF.idProductDescriptionFields = PV.idProductDescriptionFields
         WHERE PT.idProductType = @idProductType 
         and PV.idProduct = @idProduct
         ORDER BY PT.idProductType,DF.fgFieldsValue,DF.DsName


    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quinta-feira, 13 de dezembro de 2012 13:34
  • Leonardo, desculpe, não vi que voce ja havia postado.

    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quinta-feira, 13 de dezembro de 2012 13:36
  • tente assim:

    SELECT 
            PV.NuOrder, 
            PT.idProductType, 
            PT.DsName AS ProductType, 
            DG.idProductDescriptionGroup, 
            DG.DsName AS [Group], 
            DF.idProductDescriptionFields, 
            DF.DsName AS Field, 
            DF.fgFieldsValue,
             PV.DsFieldValue, 
            CASE WHEN PV.idProductDescriptionValue IS NULL THEN 0 ELSE PV.idProductDescriptionValue END AS idProductDescriptionValue,
             DF.fgFieldsValue
         FROM tbProductType as PT
         inner join tbProductTypeDescriptionGroup as PTG on PT.idProductType = PTG.idProductType 
         inner join tbProductDescriptionGroup as DG on PTG.idProductDescriptionGroup = DG.idProductDescriptionGroup 
         inner join tbProductDescriptionGroupFields as PGF on PGF.idProductDescriptionGroup = DG.idProductDescriptionGroup 
         inner join tbProductDescriptionFields as DF on PGF.idProductDescriptionFields = DF.idProductDescriptionFields
         left join tbProductDescriptionValue as PV on PGF.idProductDescriptionGroup = PV.idProductDescriptionGroup
    		and PGF.idProductDescriptionFields = PV.idProductDescriptionFields
         WHERE PT.idProductType = @idProductType 
         and PV.idProduct = @idProduct
         ORDER BY PT.idProductType,DF.fgFieldsValue,DF.DsName


    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    Alexandre só uma obs.

    se deixar a condição

    and PV.idProduct = @idProduct

    dentro do WHERE o LEFT JOIN ira funcionar com inner join


    Se a minha ajuda lhe for útil não esqueça de classificar. Att. Leonardo Marcelino

    • Sugerido como Resposta Roberson Naves sexta-feira, 14 de dezembro de 2012 19:26
    • Marcado como Resposta Ricardo Russo quarta-feira, 2 de janeiro de 2013 12:46
    quinta-feira, 13 de dezembro de 2012 13:38