#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    11
    Rep Power
    0

    Non indexed table join


    Ok so this is kind of complicated, at least to me, and I promise I'll try to make things as clear as possible for everyone.

    I'm working with one of our applications, said application is hosted offsite, and I am not allowed to change the structre of the table in question. I have written a query that pulls orders based off of an order number and the date the order was shipped.

    Unfortunately the designers of this program did not think to add a column for date the order was entered, however, I do have a text field that is used for generating emails that has the date the order was accepted into my system.

    What I'm trying to do is, parse this text field (ntext) and compare it to the order number field to essentially create an "index".

    Right now my code for doing this looks as such.

    Code:
    INNER JOIN EmailLogg
    INNER JOIN [Order] ON REPLACE(CAST(EmailLogg.EmailSubject AS NVARCHAR(200)), LEFT(CAST(EmailLogg.EmailSubject AS NVARCHAR(200)), charindex('er: ',EmailLogg.EmailSubject, 1) -1) +'er: ','') = [Order].[OrderNumber]
    I did try this query first using another data type with an actual indexed table, I'm thinking there is something wrong with my syntax or my join, because when I did this with the numeric test I ran the table was already being joined elsewhere.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    11
    Rep Power
    0
    Figured out my dilema used substring

    [CODE]INNER JOIN EmailLogg
    INNER JOIN [Order] ON SUBSTRING(EmailLogg.EmailSubject, charindex('Order: ',Cast(EmailLogg.EmailSubject AS NVARCHAR(4000)),1 ) +7, 100 ) = [Order].[OrderNumber]


    Now I'm just waiting on table syntax from the application developer.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    two problems

    first, this isn't a mysql query (the forum you posted in), so on the off chance it's microsoft sql server, i'm moving the thread to that forum

    second, each JOIN should have its own ON clause, and you're missing one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    11
    Rep Power
    0
    Originally Posted by r937
    two problems

    first, this isn't a mysql query (the forum you posted in), so on the off chance it's microsoft sql server, i'm moving the thread to that forum

    second, each JOIN should have its own ON clause, and you're missing one
    Sorry, I meant to place the thread in the MSSQL thread, because that's what it is so thank you for moving it. (Also explains why I could not find the thread in the MSSQL thread.

    I realize that each join needs it's own ON statement, that being said, I have left out probably a good 85% of the query. Seeing that my question was a syntax question for the most part anyway.

    Also its possible that I'm just retarted, but I do not see a place to mark this thread as solved, so if you could do so for me that would be awesome.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    this forum doesn't follow the practice of marking threads solved, but, hopefully, anyone else that runs across it will see the number of replies it has, and open her up only if there's really nothing else good on television or teh interwebs

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    11
    Rep Power
    0
    Originally Posted by r937
    this forum doesn't follow the practice of marking threads solved, but, hopefully, anyone else that runs across it will see the number of replies it has, and open her up only if there's really nothing else good on television or teh interwebs

    It's a good thing that this forum does not follow those rules because this topic is still giving me issues.

    I recreated the essentials from the database in my own database it's an mssql server 2008 r2 express database, so that I could make use of the debugging features in SQL Management Studio.

    The version that my Application is running on is MSSQL Server 2000 and I cannot upgrade it.

    Is there any reason that this part of the query would work in MSSQL Server 2008 R2 and not in MSSQL Server 2000? The code does indeed work in my database, it works flawlessly, but I cannot get it to work in my application.

    Code:
    INNER JOIN [Order]
    ON SUBSTRING(EmailLog.EmailSubject, CHARINDEX('Order: ',Cast(EmailLog.EmailSubject AS NVARCHAR(4000)),1 ) +7, 8 ) = [Order].OrderNumber
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    someone more familiar with sql server version nuances will have to step in
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo