Loading ...
Sorry, an error occurred while loading the content.
 

Trying to get Join Syntax right

Expand Messages
  • Charles Carroll
    I am looking at http://en.wikipedia.org/wiki/Join_%28SQL%29 and don t really find the best syntax to do this so I know you guys know this cold. I am doing a
    Message 1 of 4 , Mar 7, 2011
      I am looking at http://en.wikipedia.org/wiki/Join_%28SQL%29
      and don't really find the best syntax to do this so I know you guys
      know this cold. I am doing a ton of C# and very little SQL Server
      extremely rusty on joins. My wheres get the wrong data

      SELECT Top 10000 [LogParsed_KeyPair_ID]
      ,[JobID]
      ,[LogRaw_ID]
      ,[KeyPairGroupName]
      ,[LPKP].[KeyPairName] AS LPK_KeyPair
      ,[KeyPairValue]
      ,[KPTN].KeyPairName AS KPTN_KeyPair
      ,[KPTNM].KeyPairName AS KPTNM_KeyPair
      FROM [LogParsed_KeyPair] LPKP, [KeyPairTrueName] KPTN,
      [KeyPairTrueNameMap] KPTNM
      WHERE lower([LPKP].[KeyPairName]) = lower([KPTNM].[KeyPairName])

      just gets nonsense the pairs are not sorted out at all....

      my ON I can just never get anything to work :(

      Table A/Whatever a half dozen fields
      ====================
      one text field to join


      Table B/TrueNameMap 3 fields
      =============
      id
      textfield
      id_fk_tableC


      Table C/TrueName 2 fields
      ============
      id
      textfield


      The gist of this is a row in Table A may have rows value that are
      different words for same thing

      Pages in Doc
      DocPages
      How Many Pages


      where Table B/TrueNameMap then maps the synonyms to the universal name....
      Value TrueName_fk
      id# Pages InDoc 3
      id# DocPages 3
      id# How Many Pages 3


      and Table C/TrueName then has

      id Value
      3 PageCount


      Basically the same text value needs to map back to PageCount even
      though we call it several things. If it has no synonym in there it can
      just be its own name....

      So the result will look like


      .... many fields .... Pages In Doc PageCount
      .... many fields .... Doc Pages PageCount
      .... many fields .... How Many Pages PageCount
      .....many fields ... Whatever Whatever
      .....many fields ... Whatever2 Whatever2
    • Charles Carroll
      A subquery works i.e. Select Top 1000 [LogParsed_KeyPair_ID] ,[JobID] ,[LogRaw_ID] ,[KeyPairGroupName] ,[KeyPairName] ,(Select KeyPairName from
      Message 2 of 4 , Mar 7, 2011
        A subquery works i.e.

        Select Top 1000
        [LogParsed_KeyPair_ID]
        ,[JobID]
        ,[LogRaw_ID]
        ,[KeyPairGroupName]
        ,[KeyPairName]
        ,(Select KeyPairName from [dbo].KeyPairTrueName where
        KeyPairTrueName_ID=(Select KeyPairTrueName_ID
        from [dbo].KeyPairTrueNameMap
        WHERE lower(KeyPairName)=LOWER([dbo].[LogParsed_KeyPair].[KeyPairName]))
        ) AS TrueName
        ,[KeyPairValue]
        from
        [dbo].[LogParsed_KeyPair]

        but has some issues....

        1. where TrueName IS NOT NULL
        throws error =>
        invalid column name

        2. order by TrueName
        throws error=>
        Subquery returned more than 1 value. This is not permitted when
        the subquery follows =, !=, <, <= , >, >= or when the subquery is used
        as an expression.

        Plus I am not sure if a subquery is a better choice than JOIN
        performance wise and conceptually....

        On Mon, Mar 7, 2011 at 5:15 PM, Charles Carroll <911@...> wrote:
        > I am looking at http://en.wikipedia.org/wiki/Join_%28SQL%29
        > and don't really find the best syntax to do this so I know you guys
        > know this cold. I am doing a  ton of C# and very little SQL Server
        > extremely rusty on joins. My wheres get the wrong data
        >
        > SELECT Top 10000 [LogParsed_KeyPair_ID]
        >       ,[JobID]
        >      ,[LogRaw_ID]
        >      ,[KeyPairGroupName]
        >      ,[LPKP].[KeyPairName] AS LPK_KeyPair
        >      ,[KeyPairValue]
        >      ,[KPTN].KeyPairName AS KPTN_KeyPair
        >      ,[KPTNM].KeyPairName AS KPTNM_KeyPair
        >  FROM [LogParsed_KeyPair] LPKP, [KeyPairTrueName] KPTN,
        > [KeyPairTrueNameMap] KPTNM
        >  WHERE lower([LPKP].[KeyPairName]) = lower([KPTNM].[KeyPairName])
        >
        >   just gets nonsense the pairs are not sorted out at all....
        >
        > my ON I can just never get anything to work :(
        >
        > Table A/Whatever a half dozen fields
        > ====================
        > one text field to join
        >
        >
        > Table B/TrueNameMap  3 fields
        > =============
        > id
        > textfield
        > id_fk_tableC
        >
        >
        > Table C/TrueName  2 fields
        > ============
        > id
        > textfield
        >
        >
        > The gist of this is a row in Table A may have rows value that are
        > different words for same thing
        >
        > Pages in Doc
        > DocPages
        > How Many Pages
        >
        >
        > where Table B/TrueNameMap then maps the synonyms to the universal name....
        >        Value                     TrueName_fk
        >  id#   Pages InDoc          3
        >  id#   DocPages              3
        >  id#   How Many Pages   3
        >
        >
        > and Table C/TrueName then has
        >
        > id     Value
        > 3      PageCount
        >
        >
        > Basically the same text value needs to map back to PageCount even
        > though we call it several things. If it has no synonym in there it can
        > just be its own name....
        >
        > So the result will look like
        >
        >
        > .... many fields ....       Pages In Doc         PageCount
        > .... many fields ....       Doc Pages             PageCount
        > .... many fields ....       How Many Pages    PageCount
        > .....many fields ...        Whatever                Whatever
        > .....many fields ...        Whatever2                Whatever2
        >
      • Charles Carroll
        Forget about error 2, Data problem. Select top 1000 [LogParsed_KeyPair_ID] ,[JobID] ,[LogRaw_ID] ,[KeyPairGroupName] ,[KeyPairName] ,(Select KeyPairName from
        Message 3 of 4 , Mar 7, 2011
          Forget about error 2, Data problem.

          Select top 1000
          [LogParsed_KeyPair_ID]
          ,[JobID]
          ,[LogRaw_ID]
          ,[KeyPairGroupName]
          ,[KeyPairName]
          ,(Select KeyPairName from [dbo].KeyPairTrueName where
          KeyPairTrueName_ID=(Select KeyPairTrueName_ID
          from [dbo].KeyPairTrueNameMap
          WHERE lower(KeyPairName)=LOWER([dbo].[LogParsed_KeyPair].[KeyPairName]))
          ) AS TrueName
          ,[KeyPairValue]
          from
          [dbo].[LogParsed_KeyPair]

          works well.

          HOWEVER
          where TrueName IS NOT NULL
          does not....

          Invalid column name 'TrueName'

          On Mon, Mar 7, 2011 at 6:09 PM, Charles Carroll <911@...> wrote:
          > 2. order by TrueName
          >    throws error=>
          >    Subquery returned more than 1 value. This is not permitted when
          > the subquery follows =, !=, <, <= , >, >= or when the subquery is used
          > as an expression.

          Select top 1000
          [LogParsed_KeyPair_ID]
          ,[JobID]
          ,[LogRaw_ID]
          ,[KeyPairGroupName]
          ,[KeyPairName]
          ,(Select KeyPairName from [dbo].KeyPairTrueName where
          KeyPairTrueName_ID=(Select KeyPairTrueName_ID
          from [dbo].KeyPairTrueNameMap
          WHERE lower(KeyPairName)=LOWER([dbo].[LogParsed_KeyPair].[KeyPairName]))
          ) AS TrueName
          ,[KeyPairValue]
          from
          [dbo].[LogParsed_KeyPair]

          works well and fast anecdo
        • Charles Carroll
          ok elect top 1000 [LogParsed_KeyPair_ID] ,[JobID] ,[LogRaw_ID] ,[KeyPairGroupName] ,[KeyPairName] ,(Select KeyPairName from [dbo].KeyPairTrueName where
          Message 4 of 4 , Mar 7, 2011
            ok

            elect top 1000
            [LogParsed_KeyPair_ID]
            ,[JobID]
            ,[LogRaw_ID]
            ,[KeyPairGroupName]
            ,[KeyPairName]
            ,(Select KeyPairName from [dbo].KeyPairTrueName where
            KeyPairTrueName_ID=(Select KeyPairTrueName_ID
            from [dbo].KeyPairTrueNameMap
            WHERE
            lower(KeyPairName)=LOWER([dbo].[LogParsed_KeyPair].[KeyPairName]))
            ) AS TrueName
            ,[KeyPairValue]
            from
            [dbo].[LogParsed_KeyPair]

            works well.

            Issues #1
            where TrueName IS NOT NULL
            does not work....

            Invalid column name 'TrueName'

            Issue #2 VERY IMPORTANT
            What is easiest way to get KeyPairName in TrueName if it has no
            mapping synonym for example:

            31278630 33 2326 PERSISTENCE LXM::NumberOfFiles DocumentCount 1
            31278640 33 2326 PERSISTENCE LXM::FileSize NULL 769832

            should be:

            31278630 33 2326 PERSISTENCE LXM::NumberOfFiles DocumentCount 1
            31278640 33 2326 PERSISTENCE LXM::FileSize LXM::FileSize 769832


            SINCE:

            LXM::NumberOfFiles has a entry in KeyPairTrueNameMap i.e.

            ID KeyPairName KeyPairTrueName_ID
            5 kmo::username 4

            but lets say LXM::FileSize NEVER will have a row in KeyPairTrueNameMap!


            On Mon, Mar 7, 2011 at 6:57 PM, Charles Carroll <911@...> wrote:
            > Forget about error 2, Data problem.
            >
            > Select top 1000
            >        [LogParsed_KeyPair_ID]
            >    ,[JobID]
            >    ,[LogRaw_ID]
            >    ,[KeyPairGroupName]
            >    ,[KeyPairName]
            >    ,(Select KeyPairName from [dbo].KeyPairTrueName where
            > KeyPairTrueName_ID=(Select KeyPairTrueName_ID
            >                from [dbo].KeyPairTrueNameMap
            >                        WHERE lower(KeyPairName)=LOWER([dbo].[LogParsed_KeyPair].[KeyPairName]))
            >        ) AS TrueName
            >    ,[KeyPairValue]
            >        from
            >        [dbo].[LogParsed_KeyPair]
            >
            > works well.
            >
            > HOWEVER
            > where TrueName IS NOT NULL
            > does not....
            >
            > Invalid column name 'TrueName'
            >
            > On Mon, Mar 7, 2011 at 6:09 PM, Charles Carroll <911@...> wrote:
            >> 2. order by TrueName
            >>    throws error=>
            >>    Subquery returned more than 1 value. This is not permitted when
            >> the subquery follows =, !=, <, <= , >, >= or when the subquery is used
            >> as an expression.
            >
            > Select top 1000
            >        [LogParsed_KeyPair_ID]
            >    ,[JobID]
            >    ,[LogRaw_ID]
            >    ,[KeyPairGroupName]
            >    ,[KeyPairName]
            >    ,(Select KeyPairName from [dbo].KeyPairTrueName where
            > KeyPairTrueName_ID=(Select KeyPairTrueName_ID
            >                from [dbo].KeyPairTrueNameMap
            >                        WHERE lower(KeyPairName)=LOWER([dbo].[LogParsed_KeyPair].[KeyPairName]))
            >        ) AS TrueName
            >    ,[KeyPairValue]
            >        from
            >        [dbo].[LogParsed_KeyPair]
            >
            > works well and fast anecdo
            >
          Your message has been successfully submitted and would be delivered to recipients shortly.