Results 1 to 6 of 6

Thread: Comparison

  1. #1
    Join Date
    Dec 2013
    Posts
    11

    Unanswered: Comparison

    I need to compare data in a single table and query it on the same Line

    For Example

    ----------------------------
    Mfg P/N | Material | Cost |
    ----------------------------
    1091 | ABCD | 1.5 |
    ----------------------------
    WL-1091 | EFGH | 3.2 |
    ----------------------------

    My query needs me to see the following and I can't for the life of me figure it out

    ------------------------------------------------
    Mfg P/N | Material | Cost | Material 2 | Cost 2 |
    ------------------------------------------------
    1091 | ABCD | 1.5 | EFGH | 3.2 |
    ------------------------------------------------

    Just need a little insight as to how to make this works

    thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Which database engine are you using?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    Gladiator,

    If you are trying to equate the P/N '1091' to the P/N 'WL-1091' it can be done.

    However, it really should the same P/N and another field to define that it is
    a part of the master.

    You can create a function to "strip off the 'WL-" so you can join with it:

    Code:
    Create Function fnGetPK (strPK As Varchar(20)) Returns Varchar(20)
    As
    Begin
      If CharIndex('-', strPK, 1) > 0
         Return SubString(strPK, CharIndex('-', strPK, 1) + 1, 99)
      Else
         Return strPK
    End;
    Then you can use it in a query:

    Code:
    Select a.[Mfg P/N], a.Material, a.Cost,
           b.Material, b.Cost
    From   YourTable As a Inner Join YourTable As b on
              a.[Mfg P/N] = fnGetPK(b.[Mfg P/N])
    Where  b.[Mfg P/N] Like '%-%';
    The Where clause ensures that the RHS of the query are "child" records.

    hth,
    Wayne

  4. #4
    Join Date
    Dec 2013
    Posts
    11
    Pat, I'm using an InnoDB engine

    thanks

    Quote Originally Posted by Pat Phelan View Post
    Which database engine are you using?

    -PatP

  5. #5
    Join Date
    Dec 2013
    Posts
    11
    Wayne, thanks for pushing me down the road to making myself learn functions. I've pretty much taught myself MySQL from scratch for my job.

    My problem is that when I run this in PhpMyAdmin i get the dreaded #1064 error. Is Charindex a built in function in the Mysql Library?

    coleman

    Quote Originally Posted by WayCal View Post
    Gladiator,

    If you are trying to equate the P/N '1091' to the P/N 'WL-1091' it can be done.

    However, it really should the same P/N and another field to define that it is
    a part of the master.

    You can create a function to "strip off the 'WL-" so you can join with it:

    Code:
    Create Function fnGetPK (strPK As Varchar(20)) Returns Varchar(20)
    As
    Begin
      If CharIndex('-', strPK, 1) > 0
         Return SubString(strPK, CharIndex('-', strPK, 1) + 1, 99)
      Else
         Return strPK
    End;
    Then you can use it in a query:

    Code:
    Select a.[Mfg P/N], a.Material, a.Cost,
           b.Material, b.Cost
    From   YourTable As a Inner Join YourTable As b on
              a.[Mfg P/N] = fnGetPK(b.[Mfg P/N])
    Where  b.[Mfg P/N] Like '%-%';
    The Where clause ensures that the RHS of the query are "child" records.

    hth,
    Wayne

  6. #6
    Join Date
    Dec 2013
    Posts
    11
    Wayne,

    thanks for forcing me down the path of finally having to deal with functions. I've taught myself mysql for my job and hadn't the need to learn functions looks like till now.

    Question, when I input your script to create the function into mysql through php i get the dreaded #1064 error. any thoughts as to why

    thanks

    coleman

    Quote Originally Posted by WayCal View Post
    Gladiator,

    If you are trying to equate the P/N '1091' to the P/N 'WL-1091' it can be done.

    However, it really should the same P/N and another field to define that it is
    a part of the master.

    You can create a function to "strip off the 'WL-" so you can join with it:

    Code:
    Create Function fnGetPK (strPK As Varchar(20)) Returns Varchar(20)
    As
    Begin
      If CharIndex('-', strPK, 1) > 0
         Return SubString(strPK, CharIndex('-', strPK, 1) + 1, 99)
      Else
         Return strPK
    End;
    Then you can use it in a query:

    Code:
    Select a.[Mfg P/N], a.Material, a.Cost,
           b.Material, b.Cost
    From   YourTable As a Inner Join YourTable As b on
              a.[Mfg P/N] = fnGetPK(b.[Mfg P/N])
    Where  b.[Mfg P/N] Like '%-%';
    The Where clause ensures that the RHS of the query are "child" records.

    hth,
    Wayne

Posting Permissions

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