Check if there are similar rows in a table

select

As a Rookie in SQL I have a problem creating a query.

Here is the problem : I would like to check on a table if there are lines that share the same DATES.

For example I would like to know if in TABLE A, there are cases like this :

ID | LIB | START_DATE | END_DATE
01 | A1  | 01-01-2016 | 05-03-2016
02 | A2  | 03-03-2016 | 05-06-2016

Those lines are SURVEYS and I would like to know if an admin created a survey that is starting during an other survey. I did some searches on AUTO JOIN but this wasn't really what I was looking for.

My case is a bit more complicated but I will find out myself if you explain how to proceed on this simple case.

Best Answer

Must be something like:

select   s1.id, s2.id
from     surveys s1, surveys s2
where    (s2.start_date between s1.start_date and s1.end_date
or        s2.end_date   between s1.start_date and s1.end_date)
and      s1.id!=s2.id;

This fetches you the IDs where the START_DATE or END_DATE (of s2) is in the period of another START_DATE and END_DATE (s1).

Related Solutions

Sql-server – Query table A for any rows that are like any search term in table B

...
FROM
  TableA
  JOIN
  TableB ON
          TableA.FirstName LIKE '%' + TableB.SearchTerm + '%' OR 
          TableA.LastName LIKE '%' + TableB.SearchTerm + '%'
....

Now, this isn't going to be the best of queries. Consider full text search instead (I'm not too familiar with it)

This OR could be split into a UNION

Mysql – Why this query returns rows that I don’t want to be there

I optimized your query. I use IN instead OR because your point to the same field default_users.group_id and I added the brackets after the AND with (default_users.email LIKE '%polar%' OR default_users.username LIKE '%polar%'). You can use || instead OR (works the same).

SELECT 
    default_users.*,default_profiles.* 
FROM test.default_users 
LEFT JOIN default_profiles  ON (default_profiles.user_id = default_users.id)
WHERE default_users.group_id IN (3,4,5)
    AND (default_users.email LIKE '%polar%' 
    OR default_users.username LIKE '%polar%')
ORDER BY default_users.id DESC;

Best Answer

Related Solutions

Sql-server – Query table A for any rows that are like any search term in table B

Mysql – Why this query returns rows that I don’t want to be there

Related Question