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

Question

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.

And what did you expected to happen in such cases? How should your app behave when multiple rows are meeting your criteria? — karlosss
– karlosss, Commented Aug 18, 2016 at 21:03
If you simply want the first row for each name_id you could use GROUP BY name_id But I believe the order in which the results are returned is determined by the order of the WHERE clause. — Adam
– Adam, Commented Aug 18, 2016 at 21:12

Adam · Accepted Answer · 2016-08-18 21:41:53Z

1

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.

edited Aug 18, 2016 at 21:41

answered Aug 18, 2016 at 21:23

Adam

4,1922 gold badges21 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Adam Over a year ago

yeah I know I was simply stating that is how the SELECT portion should look like. Updated w/ comment for clarity.

teracow Over a year ago

Thanks Adam. GROUP BY was the magic phrase I needed here. I've added that to my query and it's now working correctly. :)

Adam Over a year ago

No problem anytime.

Jon Clements Over a year ago

@teracow For reference - you could also use a correlated subquery, eg:

select name_id from names where name like '%john%' or exists (select * from data where data like '%hairy%' and names.name_id = name_id)

which will give you 100 and 103.

teracow Over a year ago

@JonClements - wow, this SQL thing is pretty amazing ... I've lots to learn. :)

ayrenay · Accepted Answer · 2016-08-18 21:54:35Z

0

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>

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related