CODEWITHSUNDEEP
mysqlsql
Erwin Kloibhofer
Erwin Kloibhofer

Reputation: 63

How can I select related items within the same table in one SQL query

I have a table looking like this:

id | date       | related_id
 1   2018-01-01
 2   2018-01-01
 3   2018-01-02
 4   2018-01-05   2
 5   2018-01-06

A query SELECT * FROM table WHERE date='2018-01-01' should produce the following result:

id | date       | related_id
 1   2018-01-01
 2   2018-01-01
 4   2018-01-05   2

How can I achieve that in one MySql query?

Upvotes: 0

Views: 2679

Answers (5)

Nikhil
Nikhil

Reputation: 3950

this will work with the date function:

SELECT * FROM table WHERE date=DATE('2018-01-01');

or

SELECT * FROM table WHERE date=STR_TO_DATE(DATE, '%d/%m/%Y')

Upvotes: 0

Marcelo Rodrigues de Jesus
Marcelo Rodrigues de Jesus

Reputation: 1

You can simply do this:

SELECT table.* FROM table LEFT JOIN table related
ON table.related_id = related.id
WHERE
    table.date = '2018-01-01'
    OR related.date = '2018-01-01';

Upvotes: 0

forpas
forpas

Reputation: 164194

Extend your WHERE condition to the related_id's date:

SELECT * FROM table t 
WHERE 
  t.date = '2018-01-01'
  OR 
  (SELECT date FROM table WHERE id = t.related_id) = '2018-01-01'

or with a self join:

SELECT t.* 
FROM table t LEFT JOIN table tt
ON tt.id = t.related_id
WHERE 
  t.date = '2018-01-01'
  OR 
  tt.date = '2018-01-01'

or with EXISTS:

SELECT t.*
FROM table t
WHERE 
  t.date = '2018-01-01' 
  OR
  EXISTS (
    SELECT 1 FROM table  
    WHERE id = t.related_id AND date = '2018-01-01'
  )

Upvotes: 2

Uueerdo
Uueerdo

Reputation: 15961

If you have only one "layer", you can do this:

SELECT t.*
FROM theTable AS t
LEFT JOIN theTable AS rt ON t.related_id = rt.id
WHERE t.`date` = searchValue OR rt.`date` = searchValue
;

If there are an indefinite number of layers, and you have MySQL 8.0, you can use a CTE:

WITH RECURSIVE myCte AS (
SELECT * FROM theTable WHERE `date` = searchValue
UNION
SELECT t.* 
FROM theTable AS t 
INNER JOIN myCTE ON t.related_id = myCTE.id
)
SELECT * FROM myCTE;

Disclaimer: I am more familiar with MS-SQL CTE's, so there could be some problems with that latter option.

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use EXISTS :

SELECT t.*
FROM table t
WHERE t.date = '2018-01-01' OR
      EXISTS (SELECT 1 FROM table t1 WHERE t.related_id = t1.id);

Upvotes: 1

Related Questions