Mysql select from two tables where first table field = second table field%

user_19320444 0
Viewed 23

I have two tables.

  1. table1 has a row id='12345'

  2. table2 has a row where the id = that of table1 with added chars, e.g.,

    table2.id = '12345-678qt'

table2 may have more than one id starting with '12345-' with different ending chars.

Yes, there is always a dash after table1's id that could be used in the query.

I need to get some data from both tables, say

    SELECT  table1.id, table1.field9,

        table2.id, table2.fieldZ

    FROM table1 and table2 

    WHERE (

      table1.id=table2.id's characters before the dash

      OR

      table1.field1 = 'abcde'

    )

    AND table2.dataB='something'

    ORDER BY table1.datefield DESC LIMIT 3;

Thank you.

mysql
1 Answers

The MySQL translation of:

  • "table1 and table2 WHERE" is table1 INNER JOIN table 2 ON
  • "table2.id's characters before the dash" is SUBSTRING_INDEX(table2.id, '-', 1)

The query should look like this:

SELECT  table1.id, table1.field9,
        table2.id, table2.fieldZ
FROM       table1 
INNER JOIN table2 
        ON (table1.id = SUBSTRING(table2.id, '-', 1) OR table1.field1 = 'abcde')
       AND table2.dataB = 'something'
ORDER BY table1.datefield DESC 
LIMIT 3;

This doesn't grant that your query will work if it's written like this. My fixes will remove the current errors inside your query. For more troubleshooting with a sql fiddle link, please provide tables and I'll be happy to help.

Check the official documentation about the JOIN operation and the SUBSTRING_INDEX function at the corresponding links.

user_124928900
Related