I organize search by users using filter for selection by parameters. I have two tables:

enter image description here

My sample request:

SELECT u.uid, u.name, u.surname, e.* FROM users u, education e WHERE u.uid = e.uid OR e.startYear = 2012 OR e.overYear = 2016 OR u.name LIKE '%Андрей%' OR u.surname LIKE '%Андрей%' OR e.name LIKE '%АБВГД%'

The values ​​that are found in the database are e.startYear = 2012 and e.overYear = 2016 , the user with the first name or the last name Андрей or e.name with the value of the АБВГД not e.name .
There are only two rows in the education table.
The query displays a list of all users and education data tables.
enter image description here How do I get a list of all users using this filter?

  • And for some reason, u.uid = e.uid does not work - Shuhratjon Jumaev

1 answer 1

You don’t need to add unnecessary conditions to the columns, like OR u.name LIKE '%Андрей%' - you can simply erase them.

Now your query does the following: it takes all the rows from the users table, adds to each row all the rows from the education table (for example, if there were 5 entries in the first, 10 in the second, then 50 will be in the query). After that, only those strings are left that satisfy the request, i.e. which:

  1. u.uid = e.uid
  2. Or e.startYear = 2012
  3. Or e.overYear = 2016

For example, because of condition 1, line 2 is displayed, and because of conditions 2 and 3, all the others are displayed.

You have two options. The first is to correct the request so that it really does what it u.uid = e.uid : so that u.uid = e.uid and the condition for the year of study are fulfilled simultaneously . You can use the logical AND operator and brackets for this:

 SELECT u.uid, u.name, u.surname, e.* FROM users u, education e WHERE (u.uid = e.uid) AND (e.startYear = 2012 OR e.overYear = 2016)

The second is to use the JOIN command generally accepted in the modern world (queries through several tables in FROM are considered obsolete), at the same time it will be more efficient in time on large amounts of data. I do not know what database you are using, but it will look something like this:

 SELECT u.uid, u.name, u.surname, e.* FROM users u JOIN education e ON u.uid = e.uid WHERE e.startYear = 2012 OR e.overYear = 2016
  • I liked the second request. And if I still need to filter by name and last name? What else should be added to the request? @yeputons - Shuhratjon Jumaev
  • Just add additional conditions to WHERE as usual, forming arbitrary conditions on the lines of the result, like (e.startYear = 2012 OR e.overYear = 2016) AND e.name = 'Андрей' - find Andreev, who either started in 2012, or Finished in 2016. - yeputons
  • PS If you think that the answer completely solves your problem, you can accept it - yeputons