Databases
StackOverflow2013
Postsdbo
POMatch between two tables where field is substring of the other
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMatch between two tables where field is substring of the other
    text
    copied!<p>Hi (sorry for the poor title),</p> <p>I have two tables in a MySQL DB, lets call them <code>CarMake</code> and <code>CarModel</code>. Both tables have two fields, <code>ID:int(11)</code> and <code>Description:varchar(100)</code>. For example:</p> <pre><code> CarMake CarModel ID Description | ID Description ----------------------------------------------------- 123456 Honda | 12345678 Accord 234567 Toyota | 12345665 Civic 369258 Lexus | 23456789 Prius </code></pre> <p>Where each car model shares the same first 6 digits of the ID of its Make. In this example, both Accord and Civic share the first 6 digits of the ID with Honda, therefore they are Honda models.</p> <p>Now, what I want to do is select all rows from CarMake that <strong>do not</strong> have a record in CarModel where the first 6 digits of the ID match. In this example, my query should return the Lexus row from CarMake, as it does not have a matching row in CarModel.</p> <p>Nothing I have tried so far has really come close to achieving what I want, so I am posting it here.</p> <p>Any help would be greatly appreciated!</p> <p><strong>EDIT: Solved with help from zerkms</strong></p> <pre><code>SELECT * FROM CarMake LEFT JOIN CarModel ON CarModel.ID LIKE CONCAT(CarMake.ID, '%') WHERE CarModel.ID IS NULL; </code></pre> <p>Follow up questions:</p> <ul> <li>This solution takes a very long time to run, is there any way to improve efficiency?</li> <li>What would be the best way to delete the records returned by that query? Is there some way I can combine that into the query itself? </li> </ul>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload