CODEWITHSUNDEEP
pythonpython-3.xsearchsqlite
teracow
teracow

Reputation: 229

SQLite: how to search multiple columns without returning duplicate rows?

trying to solve this with my limited knowledge of SQL and I'm stuck. :)

This is being done in SQLite 3 and Python 3.

I've 'JOIN'ed 2 tables (names and data).

I'm trying to search 2 columns in the result for user supplied text (case insensitive). If a match is found in either column, then return the row.

The problem I have, is that if the text is found in the name columns, I get multiple rows returned (I guess because there are multiple rows with the name name).

The required data is the 'name_id'

Here's a mockup of the join (there are other columns I haven't included here):

    -------------------------------------------------------------------
    |  name_id    |     name       |      data_id     |    data       |
    -------------------------------------------------------------------
    |   100       |   John Smith   |        200       |  grey hair    |
    |   100       |   John Smith   |        201       |  hairy teeth  |
    |   101       |  Jerry Jones   |        202       |  white teeth  |
    |   103       | Barry Johnson  |        256       |  brown hair   |
    -------------------------------------------------------------------

So, if I search for "teeth", I get:

    |   100       |   John Smith   |        201       |  hairy teeth  |
    |   101       |  Jerry Jones   |        202       |  white teeth  |

and, if I search for "hair", I get:

    |   100       |   John Smith   |        200       |  grey hair    |
    |   100       |   John Smith   |        201       |  hairy teeth  |
    |   103       | Barry Johnson  |        256       |  brown hair   |

or, if I search for "john", I get:

    |   100       |   John Smith   |        200       |  grey hair    |
    |   100       |   John Smith   |        201       |  hairy teeth  |
    |   103       | Barry Johnson  |        256       |  brown hair   |

But what I actually want is only one row returned based on each 'name_id' so:

    |   100       |   John Smith   |        200       |  grey hair    |
    |   103       | Barry Johnson  |        256       |  brown hair   |

The remaining SQLite command looks like this:

WHERE data LIKE "%john%" OR name LIKE "%john%" COLLATE NOCASE

I've tried using 'DISTINCT' but it appears to remove duplicate rows based upon the specified column. These missing rows contain data that is then not searched.

Thanks.

Upvotes: 0

Views: 1062

Answers (2)

ayrenay
ayrenay

Reputation: 26

With the same assumptions as the previous answer (i.e. you're looking for the first record for the unique name), I'd suggest the following:

select    names.name_id
          , <names_fields>
          , group_concat(data,';')
from      names
          join  (select name_id, data from data_table)
            on names.name_id = data.name_id
where     name_id
group by  <name_fields>

More on group_concat here.

Upvotes: 0

Adam
Adam

Reputation: 4172

Alright so I'm assuming you simply want the first record for each unique value in the name_id column. If that is the case then your query should look like this.

SELECT
  *
FROM
  names n
  JOIN data d ON d.name_id = n.name_id
WHERE
  LCASE(n.name) LIKE '%john%' // Force lower so only 1 WHERE statement
GROUP BY
  n.name_id

GROUP BY should 'collapse' all the data on whatever column you select. So lets say you wanted to count the amount of results for each name_id you could change your SELECT statement as follows.

SELECT
  n.name_id,
  COUNT(n.name_id) AS result_count
FROM
// Rest of query

If you don't use an aggregate function like SUM, COUNT, etc then it simply drops all but the first result.

If this isn't what you are looking for LMK. Hope that helps.

Upvotes: 1

Related Questions