Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2012
    Posts
    3

    Unanswered: Concat then match

    Hello


    Can anybody help me please?

    I want to concat 4 data strings (all on same row) into one string (call it MATCH for ease of ref). This action to repeat for all rows in the MySQL database.

    Where any 'MATCH' string matches any other MATCH string in the database then return the MATCH strings together with several other data values in the same rows. (Sounds like it needs a temporary Field to store the MATCH values or is it best to be within the Query??).

    I have set up a MySQL ODBC driver and I wish to pull the results using MS Query on Excel (it works, I can pull the whole Table from the web host). If you are not familiar with MS Query etc, but you could provide the SQL for carrying out the above action directly on the MySQL db then that would be great.

    For simplicity please refer to the data values as ... d1, d2, d3, etc.

    Hope above makes sense, if not just let me know?

    Many Thanks
    GraHal
    Last edited by grahal; 11-10-12 at 09:31.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT this.d1
         , this.d2
         , this.d3
         , this.d4
         , this.someothercolumn
         , that.d1
         , that.d2
         , that.d3
         , that.d4   
         , that.anothercol
      FROM yourtable AS this     
    INNER
      JOIN yourtable AS that
        ON CONCAT(that.d1,that.d2,that.d3,that.d4) =  
           CONCAT(this.d1,this.d2,this.d3,this.d4)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2012
    Posts
    3
    Hey, thanks so much Rudy

    SQL seems to be near to working, but it keeps coming back with the syntax error message attached ...

    The MySQL Server Version I am using is ...

    Server: Localhost via UNIX socket
    Server version: 5.1.65-cll
    Protocol version: 10
    MySQL charset: UTF-8 Unicode (utf8)

    Any ideas what may be wrong please?

    Also, I just thought on ... the Concat requirement was only because I thought that may be the easiest way to do a 'this = that' comparison (this.d1,this.d2,this.d3,this.d4 = that.d1,that.d2,that.d3,that.d4). if it is any easier then d1, d2, ... do not have to be concatenated at all, but they do have to match to return data.

    Bit more explanation may help (sorry about this) d1, d2, d3, d4 (call is Search data) would be entered by User1 and d5, d6, d7, d8 (call it Your data) would ne entered by User2.

    Example: User1 is doing a 'search' for a detached 3 bed house in Area1 and user2 has a 'your' detached 3 bed house in Area1 ... where these match then the query returns 'Your - detached 3 bed house in Area1' and also the email addresses of both Searcher / User1 and Your / User2.

    User1 also enters 'Your data' and User2 also enters 'Search data'. All entries are on the same Table against each User, but with an incremental Primary Key.

    Hope above explanation may help / make easier? May be easier to take it in steps, e.g. ignore email address of Your / User2 for now?

    Many Thanks
    GraHal
    Attached Files Attached Files
    Last edited by grahal; 11-11-12 at 11:32. Reason: More information to make easier (hopefully?)

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can we see the SQL you re actually using. I appreciate its more sporting, more fun to make us guess where the fault lies rather than show the actual SQL that is reporting the fault.

    offhand it looks like it could be a space between userdata and userdata_0.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2012
    Posts
    3
    Hello Healdem, thank you

    You were spot on ... it didn't need the UserData_0 at all ... I had copied the Db and Table name from the 'auto generated SQL' that had pulled all Fields (by clicking on *).

    I'd tried everything except deleting UserData_0. (doh to me!)

    Yes, sorry about not putting my actual SQL in the message; I'm not trying to make it sporting / difficult honest ... I'm working on a new idea whch has not gone live yet. I'm probably being OTT so I will post actual SQL in future.

    Many Thanks
    GraHal

Posting Permissions

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