All geek questions in one place

MySQL query does not return all records

I have several tables in my database, and I use left outer joins to join the tables and execute the query. The problem is that not all returned records match, and for some reason the first one is always skipped from the results.

Here is a link to SQL Fiddle ,

Scheme + Test data:

mysql> select * from main; +----------+------------+----------------+---------------+-------------+-------------+----------------+----------------+-----------------+------------+------------+----------------+-----------------+---------------+--------------------------+--------------+ | username | date | water_quantity | water_chilled | smoked_what | smoke_count | sleep_duration | study_duration | screen_duration | loud_level | heat_level | humidity_level | stress_physical | stress_mental | stress_notes | menstruation | +----------+------------+----------------+---------------+-------------+-------------+----------------+----------------+-----------------+------------+------------+----------------+-----------------+---------------+--------------------------+--------------+ | test123 | 2012-09-16 | 1 | no | cigarettes | 20 | 480 | 0 | 420 | 2 | 7 | 7 | 6 | 4 | Roamed a lot on the bike | no | | test123 | 2012-09-13 | 2 | no | cigarettes | 12 | 300 | 0 | 0 | 1 | 1 | 1 | 6 | 3 | met friends | no | +----------+------------+----------------+---------------+-------------+-------------+----------------+----------------+-----------------+------------+------------+----------------+-----------------+---------------+--------------------------+--------------+ 2 rows in set (0.00 sec) mysql> select * from food; +----------+------------+--------------+ | username | date | food | +----------+------------+--------------+ | test123 | 2012-09-16 | rice | | test123 | 2012-09-16 | pizza | | test123 | 2012-09-16 | french fries | | test123 | 2012-09-16 | burger | | test123 | 2012-09-13 | naan | | test123 | 2012-09-13 | fried rice | | test123 | 2012-09-13 | lemon juice | +----------+------------+--------------+ 7 rows in set (0.00 sec) mysql> select * from alcohol; +----------+------------+--------------+------------------+ | username | date | alcohol_type | alcohol_quantity | +----------+------------+--------------+------------------+ | test123 | 2012-09-16 | beer | 0 | | test123 | 2012-09-16 | beer_ale | 0 | | test123 | 2012-09-16 | absinthe | 0 | | test123 | 2012-09-13 | rum | 0 | | test123 | 2012-09-13 | tequila | 0 | +----------+------------+--------------+------------------+ 5 rows in set (0.00 sec) mysql> select * from headache; +----------+------------+-----------+----------+---------------------+ | username | date | intensity | duration | notes | +----------+------------+-----------+----------+---------------------+ | test123 | 2012-09-16 | 6 | 12 | something something | +----------+------------+-----------+----------+---------------------+ 1 row in set (0.00 sec) mysql> select * from headache_areas; +----------+------------+-----------------+ | username | date | area | +----------+------------+-----------------+ | test123 | 2012-09-16 | left_temple | | test123 | 2012-09-16 | right_temple | | test123 | 2012-09-16 | behind_left_ear | +----------+------------+-----------------+ 3 rows in set (0.00 sec) mysql> select * from headache_symptoms; +----------+------------+-----------+ | username | date | symptoms | +----------+------------+-----------+ | test123 | 2012-09-16 | aura | | test123 | 2012-09-16 | vertigo | | test123 | 2012-09-16 | dizziness | +----------+------------+-----------+ 3 rows in set (0.00 sec) mysql> select * from alcohol; +----------+------------+--------------+------------------+ | username | date | alcohol_type | alcohol_quantity | +----------+------------+--------------+------------------+ | test123 | 2012-09-16 | beer | 0 | | test123 | 2012-09-16 | beer_ale | 0 | | test123 | 2012-09-16 | absinthe | 0 | | test123 | 2012-09-13 | rum | 0 | | test123 | 2012-09-13 | tequila | 0 | +----------+------------+--------------+------------------+ 5 rows in set (0.00 sec) mysql> select * from drugs; +----------+------------+----------+ | username | date | drug | +----------+------------+----------+ | test | 2012-08-21 | crocin | | test | 2012-08-21 | azithral | | test | 2012-08-21 | crocin | | test | 2012-08-21 | azithral | | test | 2012-08-21 | crocin | | test | 2012-08-21 | azithral | | test123 | 2012-09-13 | ching | | test123 | 2012-09-13 | chong | | test123 | 2012-09-13 | blah1 | | test123 | 2012-09-13 | blurg2 | +----------+------------+----------+ 10 rows in set (0.00 sec)

I tried the query with the result:

 mysql> SELECT m.*, -> GROUP_CONCAT(DISTINCT f.food SEPARATOR ',') AS food, -> GROUP_CONCAT(DISTINCT a.alcohol_type SEPARATOR ',') AS alcohol, -> a.alcohol_quantity, -> GROUP_CONCAT(DISTINCT d.drug SEPARATOR ',') AS drug, -> h.intensity AS headache_intensity, -> h.duration AS headache_duration, -> GROUP_CONCAT(DISTINCT ha.area) AS headache_areas, -> GROUP_CONCAT(DISTINCT hs.symptoms) AS headache_symptoms, -> h.notes AS headache_notes -> FROM main AS m -> LEFT OUTER JOIN food AS f ON f.username = m.username AND f.date = m.date -> LEFT OUTER JOIN headache AS h ON h.username = m.username AND h.date = m.date -> LEFT OUTER JOIN headache_symptoms AS hs ON hs.username = m.username AND hs.date = m.date -> LEFT OUTER JOIN headache_areas AS ha ON ha.username = m.username AND ha.date = m.date -> LEFT OUTER JOIN drugs AS d ON d.username = m.username AND d.date = m.date -> LEFT OUTER JOIN alcohol AS a ON a.username = m.username AND a.date = m.date -> ; +----------+------------+----------------+---------------+-------------+-------------+----------------+----------------+-----------------+------------+------------+----------------+-----------------+---------------+--------------------------+--------------+------------------------------------------------------------+------------------------------------+------------------+--------------------------+--------------------+-------------------+------------------------------------------+------------------------+---------------------+ | username | date | water_quantity | water_chilled | smoked_what | smoke_count | sleep_duration | study_duration | screen_duration | loud_level | heat_level | humidity_level | stress_physical | stress_mental | stress_notes | menstruation | food | alcohol | alcohol_quantity | drug | headache_intensity | headache_duration | headache_areas | headache_symptoms | headache_notes | +----------+------------+----------------+---------------+-------------+-------------+----------------+----------------+-----------------+------------+------------+----------------+-----------------+---------------+--------------------------+--------------+------------------------------------------------------------+------------------------------------+------------------+--------------------------+--------------------+-------------------+------------------------------------------+------------------------+---------------------+ | test123 | 2012-09-16 | 1 | no | cigarettes | 20 | 480 | 0 | 420 | 2 | 7 | 7 | 6 | 4 | Roamed a lot on the bike | no | rice,pizza,french fries,burger,naan,fried rice,lemon juice | beer,beer_ale,absinthe,rum,tequila | 0 | ching,chong,blah1,blurg2 | 6 | 12 | left_temple,right_temple,behind_left_ear | aura,vertigo,dizziness | something something | +----------+------------+----------------+---------------+-------------+-------------+----------------+----------------+-----------------+------------+------------+----------------+-----------------+---------------+--------------------------+--------------+------------------------------------------------------------+------------------------------------+------------------+--------------------------+--------------------+-------------------+------------------------------------------+------------------------+---------------------+ 1 row in set (0.00 sec)

He does not show me another entry in the table. Can someone please help me with this? Thanks in advance.

+6
source share
1 answer

It outputs exactly what you “asked” to do (using group concat ):

 USERNAME |...| FOOD | ALCOHOL ... + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + test123 |...| rice,pizza,french fries,burger,naan,fried rice,lemon juice | beer,beer_ale,absinthe,rum,tequila ...

When you use group concat , it combines all the values ​​of the grouped column into a single output field.

Update:
I think I know that I understand what you are trying to achieve, since this is the same username in both records of the main table will be displayed as one column to decide that you need to find the first field that is not identical for these two records - date . So all you have to do is add GROUP BY m.username, date to the end of your request .

+3
source

Source: https://habr.com/ru/post/925875/

More articles:


All Articles