I've got two tables in my SQLite database with a one to many relationship between them:
CREATE TABLE Reads ( RowID INTEGER NOT NULL PRIMARY KEY, EventDate INTEGER NOT NULL, Plate TEXT, State TEXT ); CREATE TABLE ExplodedPlates ( Plate TEXT, RowID INTEGER NOT NULL, PRIMARY KEY (RowID, Plate) ) WITHOUT RowId; The Reads table's Plate column contains license plates. The data may contain what we call "ambiguous license plates". For example, a plate may contain "A[0OQ]C1234", where the actual second character could be a "0", an "O", or a "Q". In this case, there are three rows in the ExplodedPlates table with the same RowID but the Plate is "A0C123", another with "AOC1234", and a third with "AQC123".
I need to join these tables and return one row for each unique RowID where the plate matches a pattern similar to the one in the example. So if the user enters "A[O0]1234" in the search box, they should get one row with the plate "A[0OQ]1234", but not 3, as well as any rows with other RowIDs that match that string.
I've written a query using Entity Framework similar to this one:
SELECT DISTINCT r.* FROM ExplodedPlates A x JOIN Reads AS r ON r.RowId = x.RowID WHERE x.Plate GLOB @Plate This works, but the query returned uses a temporary B-Tree to do the DISTINCT. My boss wants me to get rid of the DISTINCT and use WHERE EXISTS(SELECT 1 FROM ExplodedPlates WHERE Plate GLOB @Plate), but I don't know how to get this to work. I have a specific case in my database with 135 rows that match a particular pattern, but the query I'm using returns 6557 rows. Clearly, this is wrong.
How exactly can I use that WHERE clause to generate the unique list of matching reads?
