none
JOIN USING CHARINDEX?

    Question

  • Hi,

    I have a SP which is trying to JOIN to tables.  Table 1 has a primary key (int).. Table 2 has a varchar field which contains a comma delimited list ..

     

    I need to JOIN table 1 to table 2 where the primary key field in table 1 is contained in that field in table 2.

     

    I know I could use CHARINDEX in my WHERE clause if just searching Table 2, but not sure of the syntax for the JOIN.

     

    Here's my current SP:

     

    Code Snippet

    SELECT     ISNULL(SUM(TotalOutstanding_Net), 0) AS OutstandingTotal_Net, ISNULL(SUM(TotalOutstanding_Gross), 0) AS OutstandingTotal_Gross
    FROM         (SELECT     Suppliers_POs.ProjectID, Suppliers_POs.SubTotal - ISNULL(SUM(Suppliers_Invoices.InvoiceSubTotal), 0) AS TotalOutstanding_Net,
                                                  Suppliers_POs.GrandTotal - ISNULL(SUM(Suppliers_Invoices.InvoiceGrandTotal), 0) AS TotalOutstanding_Gross
                           FROM          Suppliers_POs INNER JOIN
                                                  Suppliers_Invoices ON Suppliers_POs.POID = Suppliers_Invoices.POID
                           WHERE      InvoiceStatus <> 'Deleted'
                           GROUP BY Suppliers_POs.ProjectID, Suppliers_POs.SubTotal, Suppliers_POs.GrandTotal
                           HAVING      (Suppliers_POs.SubTotal - SUM(Suppliers_Invoices.InvoiceSubTotal) > 0)) DERIVEDTBL
    WHERE     (ProjectID = @ProjectID)
    HAVING      (ROUND(SUM(TotalOutstanding_Net), 2) > 0)

     

     

    Tuesday, November 18, 2008 9:56 AM

Answers

  •  mtm81 wrote:

     

    I just need to know how to get the charindex (or soemthing similar) working in my query

     

    You can join DERIVED TABLEs just like regular tables.

     

    You can do JOIN using expressions, including expressions with CHARINDEX:

    Code Snippet

     

    /****************************************************

    * FAIR WARNING

    * This query is only a demo of JOIN with expressions

    * It may not be meaningful business-wise at all

    * It is not a performance champion either

    ****************************************************/

    use AdventureWorks;

     

    select top 10 *

    from Production.Product p

    join Sales.SalesOrderDetail sod

    on LEFT(convert(varchar,p.ProductID), CHARINDEX('5',convert(varchar,p.ProductID))-1)

     = LEFT(convert(varchar,sod.ProductID), CHARINDEX('7',convert(varchar,sod.ProductID))-1)

    where CHARINDEX('5',convert(varchar,p.ProductID))> 0

     and CHARINDEX('7',convert(varchar,sod.ProductID)) > 0

     

     

    Let us know if helpful.

    Tuesday, November 18, 2008 3:45 PM

All replies

  • Hi,

    May I ask why you are storing a comma seperated list?
    Tuesday, November 18, 2008 12:17 PM
  • Yeah no  problem..

     

    the original database had a one-to one relationship.. so the field in table2 only had one value which would match with the primary key in table 1..

     

    however that has since changed and the the field in table two can now link up to several records in the first table.

     

    I know the correct method for this would be to have an additional table which would list these linkings as seperate records which would then used in the Join statements.

     

    but there is only this one SP which needs altering using this method, whereas if I use the "correct" method of the additional table. I would have to alter several tens of SP's and sections of script on the site that uses them..

     

     

    Tuesday, November 18, 2008 12:25 PM
  • Still i would say better to go with Correct method. Maintaining such code /logic will be hard. ANd also performance will be another issue

     

    Madhu

    Tuesday, November 18, 2008 1:01 PM
  • I certainly feel your pain however I too would adgree with Madhu on this one.

    It will be worth the extra effort in the long run.
    Tuesday, November 18, 2008 1:09 PM
  •  

    I do know what you guys are saying, however it really isn't worth it..

     

    this query (if I can get it working) is for one SP on one page.. a page which is only used by the end-user VERY infrequently and therefore it doesn't make any sense for me to make wholesale changes across the board just to compenstate for this one page...

     

    I have no problem working the "correct" way in every other page of the system I'm building, and in fact have several sections like this already working no problem..

     

    however, this is for one page and is simply used to reference some old data..

     

    therefore overall - in this instance - this method (however performance etc is affected) is the correct one.

     

    I just need to know how to get the charindex (or soemthing similar) working in my query

    Tuesday, November 18, 2008 3:05 PM
  •  mtm81 wrote:

     

    I just need to know how to get the charindex (or soemthing similar) working in my query

     

    You can join DERIVED TABLEs just like regular tables.

     

    You can do JOIN using expressions, including expressions with CHARINDEX:

    Code Snippet

     

    /****************************************************

    * FAIR WARNING

    * This query is only a demo of JOIN with expressions

    * It may not be meaningful business-wise at all

    * It is not a performance champion either

    ****************************************************/

    use AdventureWorks;

     

    select top 10 *

    from Production.Product p

    join Sales.SalesOrderDetail sod

    on LEFT(convert(varchar,p.ProductID), CHARINDEX('5',convert(varchar,p.ProductID))-1)

     = LEFT(convert(varchar,sod.ProductID), CHARINDEX('7',convert(varchar,sod.ProductID))-1)

    where CHARINDEX('5',convert(varchar,p.ProductID))> 0

     and CHARINDEX('7',convert(varchar,sod.ProductID)) > 0

     

     

    Let us know if helpful.

    Tuesday, November 18, 2008 3:45 PM
  •  

    Hi,

    Yes that's helpful.. just need a little more info.

     

    using my original inner JOIn statement:

    Code Snippet

     

    FROM          Suppliers_POs INNER JOIN
                                                  Suppliers_Invoices ON Suppliers_POs.POID =Suppliers_Invoices.POID

     

     

    How would I convert that so, instead of just joining with "Suppliers_Invoices.POID

     

    it would look in that field for the matching POID but with a comma

     

    I have tried:

    Code Snippet

     

    FROM          Suppliers_POs INNER JOIN
                                                  Suppliers_Invoices ON CONVERT(varchar, Suppliers_POs.POID) = CHARINDEX(Suppliers_Invoices.POID, CONVERT(varchar,
                                                  Suppliers_POs.POID) + ',')

     

     

    But obviously that won't work as the right side of my query:

    CHARINDEX(Suppliers_Invoices.POID, CONVERT(varchar,
                                                  Suppliers_POs.POID) + ',')

     

    would just return a number value based on where that POID occurs in the Suppliers_Invoices.POID string.

     

    Any ideas?

     

     

    Tuesday, November 18, 2008 5:00 PM
  • That looks like it's doing a whole lot of work which I don't think I need to do..

    the charindex function works fine for my needs - I just need to work out the correct syntax to get it working within the INNER JOIN statement from the WHERE statement...

     

     

    Wednesday, November 19, 2008 4:35 PM
  • ???????????????????????

     

    I don't think you grasped my last post.....

     

    That's why I'm posting this thread and was my original question - I can't work out what the correct syntax is......

    Wednesday, November 19, 2008 11:16 PM
  •  

    anyone got any ideas??
    Friday, November 21, 2008 11:14 AM