-4

I'm trying to create a MySQL query to select two teams

Using the query below I keep getting this error and its not something I've come across before, google seems to offer a variety of answers but I'm unable to relate the fix to my query.

SELECT 
        m.idm,
        m.buteq1,
        m.buteq2,
        tf.eq_name,
        ts.eq_name  
        FROM wpap_match  m
             INNER JOIN wpap_equipes tf
                on tf.eq_name = tf.id
             INNER JOIN wpap_equipes ts
                on ts.eq_name = ts.id

Result:

 Warning: #1292 Truncated incorrect DOUBLE value: 'ESS'

Can any one help to fix this ?

c newbie
  • 3
  • 5
  • 2
    This looks suspect: `tf.eq_name = tf.id`. Semantically should a "name" really equal an "id"? – David Jun 14 '19 at 21:17

1 Answers1

0

Usually when joining 2 tables the ON clause contains a condition for equality of 2 columns of the 2 tables.
But your condition is about an equality of 2 columns (possibly of different data types) of the same table!
Change to something like this:

INNER JOIN wpap_equipes tf on m.??? = tf.id
INNER JOIN wpap_equipes ts on m.??? = ts.id

Replace ??? with the column (id of each team) of the table wpap_match

forpas
  • 160,666
  • 10
  • 38
  • 76
  • seems like this answer is suggesting that the cause of the problem is an implicit datatype conversion. when there's an equality comparison of two different datatypes, mysql will do a conversion of one (or both) of values being compared, following the rules documented here https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html looks like OP case is getting here "In all other cases, the arguments are compared as floating-point (real) numbers." and the 1292 warning/error is thrown when an attempt is made to convert an invalid value ... – spencer7593 Jun 14 '19 at 21:31
  • Exactly, but there is a logical error here, because the OP probably does not know how to join 2 tables. – forpas Jun 14 '19 at 21:34
  • yeah, the condition in the ON clause is a bit odd, ... comparing two columns from the same table, with no reference to preceding tables. i think more problematic would be this query *not* returning an error, and instead returning a semi-Cartesian product. – spencer7593 Jun 14 '19 at 21:34
  • It's a simple match/teams relation. I guess in table `wpap_match` there are 2 columns like team1id and team2id to use for the ON clause. – forpas Jun 14 '19 at 21:37
  • 1
    seems like a reasonable guess. when the only thing we have to go on is broken sql, without a data model, without a table definition, without sample data, and without an example of the expected result... we are really just guessing. – spencer7593 Jun 14 '19 at 21:40