Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    18

    Unanswered: Join using like?

    I would like to create a view that shows each employee's id and there vp's information, if the vp's id is a part of the dbo.HIERARCHY.Hierarchy field (which is a string containing the employee's entire hierarchy).
    In enterprise manager it accepts this as a parsable query, but it isn't getting me the data I need. I suspect it has something to do with me not knowing how to add the wildcard characters when searching the dbo.HIERARCHY.Hierarchy field. Any idea how I can fix this?

    SELECT dbo.CCINFORMATION.*, dbo.HIERARCHY.AWID AS Employee
    FROM dbo.CCINFORMATION INNER JOIN
    dbo.HIERARCHY ON dbo.CCINFORMATION.AWID LIKE dbo.HIERARCHY.Hierarchy
    WHERE (dbo.CCINFORMATION.Title LIKE '%VP,%') OR
    (dbo.CCINFORMATION.Title LIKE '%CEO%') OR
    (dbo.CCINFORMATION.Title LIKE '%VICE PR%')
    -Doug Picanzi

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The problem is in your join clause:

    ON dbo.CCINFORMATION.AWID LIKE dbo.HIERARCHY.Hierarchy

    Since you are not using wildcards, your criteria is equivalent to

    ON dbo.CCINFORMATION.AWID = dbo.HIERARCHY.Hierarchy

    ...except for ignoring trailing spaces in AWID.

    Try:

    ON dbo.CCINFORMATION.AWID LIKE '%' + dbo.HIERARCHY.Hierarchy + '%'

    ...but I suspect there are more problems in your table design that need to be addressed.

    blindman

  3. #3
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Originally posted by blindman
    ...but I suspect there are more problems in your table design that need to be addressed.

    blindman
    Blindman, what makes you thinking that?

    dyingjoy: if hierarchy contains your employee ID, you have to reverse your join condition:

    ON dbo.HIERARCHY.Hierarchy LIKE '%' + dbo.CCINFORMATION.AWID + '%'
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  4. #4
    Join Date
    May 2003
    Posts
    26
    Blindman, what makes you thinking that?
    I'm no expert, but I think that for a one time search this query would work fine. As part of a process however, it would be much more efficient to definitavely identify employess as a 'CEO' or 'VP' in a seperate column.

    From the query it appears the Title values are something like 'VP of Marketing', 'VP of Finance', etc. For frequent searches on specific portions of this column, it would meake sense to me to standardize the portion you are searching on in its own column.

    ie.

    CCINFORMATION.EmpType CHAR(3)
    CCINFORMATION.Title VARCHAR(20)

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yup.

    Hard-coding strings like 'VP', 'CEO', 'VICE PR' just invites problems when someone enters a title like 'V.P.', 'C.E.O.', or 'Big Kahuna', or when some mid-level manager or department head requests to be included.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •