Consultation with LEFT JOIN Mysql

Question

Consultation with LEFT JOIN Mysql

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (0 votes)
#4 by (0 votes)

0

I have three tables with the following data:

varieties

------------------------------------
  id  |   variety        | status  |
------------------------------------
  1   |     VS1          |    1    |
------------------------------------
  2   |     VS2          |    1    |
------------------------------------
  3   |     VS3          |    1    |
------------------------------------
  4   |     VS4          |    1    |
------------------------------------
  5   |     VS5          |    1    |
------------------------------------

units

----------------------------------------------
  id   | id_grouper |   unit     |   status  |
----------------------------------------------
  1    |     13     |     1      |    1      |
----------------------------------------------
  2    |     13     |     2      |    1      |
----------------------------------------------
  3    |     13     |     3      |    1      |
----------------------------------------------
  4    |     13     |     4      |    1      |
----------------------------------------------
  5    |     13     |     5      |    1      |
----------------------------------------------
  6    |     13     |     6      |    1      |
----------------------------------------------
  7    |     13     |     7      |    1      |
----------------------------------------------

sowing

-----------------------------------------------------------------
  id  |  id_grouper | id_unit  |  id_variety  | type  |  status |
-----------------------------------------------------------------
  1   |       13    |    1     |      1       |   SW  |    1    |
-----------------------------------------------------------------
  2   |       13    |    3     |      1       |   SW  |    1    |
-----------------------------------------------------------------
  3   |       13    |    4     |      1       |   SW  |    1    |
-----------------------------------------------------------------
  4   |       13    |    6     |      3       |   SW  |    1    |
-----------------------------------------------------------------
  5   |       13    |    7     |      4       |   ER  |    1    |
-----------------------------------------------------------------

The table varieties shows me varieties, the table units are beds and the table sowing are symmetrical, in the latter it records that varieties planted There is in each bed and the type of planting.

The type SW means that it is currently planted and ER that was eradicated

What I want to do is show the varieties sown in each bed with type SW and if a bed is not in the table sowing or it is type ER that the bed with variety NULL likewise shows me in the following way:

----------------------------------
    unit     |       variety     |
----------------------------------
     1       |         VS1       |
---------------------------------- 
     2       |         NULL      |
---------------------------------- 
     3       |         VS1       |
---------------------------------- 
     4       |         VS1       |
---------------------------------- 
     5       |         NULL      |
---------------------------------- 
     6       |         VS3       |
---------------------------------- 
     7       |         VS4       |
----------------------------------

I made the following query using LEFT JOIN but it does not work for me:

SELECT units.unit, varieties.variety
FROM (sowing
LEFT JOIN units ON sowing.id_unit =  units.id)
LEFT JOIN varieties ON sowing.id_variety = varieties.id
WHERE units.id_grouper = 13
AND sowing.type= 'SW'
AND sowing.status = 1
AND units.status = 1
GROUP BY units.id;

returns the following result:

----------------------------------
    unit     |       variety     |
----------------------------------
     1       |         VS1       |
---------------------------------- 
     3       |         VS1       |
---------------------------------- 
     4       |         VS1       |
---------------------------------- 
     6       |         VS3       |
---------------------------------- 
     7       |         VS4       |
----------------------------------

I'm a bit of a rookie with this kind of questions, any comments or help is welcome, I hope you understand the question.

mysql sql join

asked by Fabian Sierra 18.09.2017 в 18:55

source

4 answers

2

I think you are filtering too much data unnecessarily. For example you say:

What I want to do is show the varieties sown in each bed with type SW and if a bed is not in the sowing table or is type ER that similarly I show the bed with NULL variety of the following way ...

So, you apply a filter like this:

AND sowing.type= 'SW'

How will you show the sowing of type ER ? It is impossible.

The result you want can be obtained simply by this:

Query:

VIEW DEMO

SELECT u.unit, v.variety
FROM       
units u
LEFT JOIN sowing s ON u.id = s.id_unit
LEFT JOIN varieties v ON s.id_variety = v.id
WHERE u.id_grouper = 13 AND u.status = 1;

Result:

unit    variety
1        VS1
2        NULL
3        VS1
4        VS1
5        NULL
6        VS3
7        VS4

answered by 18.09.2017 в 22:18

0

Based on your expected response, it seems that the main table of your query should be units (since you want all the rows of that table).

Test with (I discussed the status conditions, because the tables have status 1 and your query asked status 100):

SELECT units.unit, varieties.variety
FROM units
LEFT JOIN sowing ON sowing.id_unit =  units.id)
LEFT JOIN varieties ON sowing.id_variety = varieties.id
WHERE units.id_grouper = 13
AND sowing.type= 'SW'
-- AND sowing.status = 100
-- AND units.status = 100
GROUP BY units.id;

answered by 18.09.2017 в 19:06

0

I think this consultation can help you:

SELECT u.unit, ( SELECT variety FROM units
    LEFT JOIN sowing 
      ON units.id = sowing.id_unit 
    LEFT JOIN varieties 
      ON sowing.id_variety = varieties.id
    WHERE units.id_grouper = 13 AND sowing.type= 'SW'
     AND sowing.status = 1  AND units.status = 1 
     AND units.unit = u.unit
    )
FROM units as u 
order by 1;

answered by 18.09.2017 в 23:49

Add Cast in SQL SERVER Promises with node.js

score 1 · Accepted Answer

You can not place in the WHERE clause a column that participates in a LEFT JOIN combination because it "forces" the combination to require matching.

SELECT u.units, v.variety
FROM       
    units u
    LEFT JOIN sowing s ON u.id = s.id_unit
       AND s.type = 'SW' AND s.status = u.status
    LEFT JOIN varieties v ON s.id_variety = v.id
WHERE u.id_grouper = 13 AND u.status = 1;

Evaluate if it is necessary to group by the columns 'u.units' and 'v.variety'.