Bring all the data of a table in mysql

Navigation
1

I have the following query: 

SELECT  pr_production_units_details.id AS idProductionUnit, pr_production_units_details.production_units_detail AS  productionUnit, pr_varieties.variety
FROM (SELECT MAX(sw_sowing.id) AS id
            FROM sw_sowing
            WHERE sw_sowing.status != 0
            AND sw_sowing.id_tenant = 1
            AND YEARWEEK(sw_sowing.date) <= 201741
            GROUP BY  sw_sowing.id_production_unit_detail
)AS sw
INNER JOIN sw_sowing ON sw_sowing.id = sw.id
INNER JOIN pr_production_units_details ON pr_production_units_details.id = sw_sowing.id_production_unit_detail
INNER JOIN pr_varieties ON pr_varieties.id = sw_sowing.id_variety
WHERE pr_production_units_details.id_grouper_detail = 13
GROUP BY pr_production_units_details.id, variety

Here I bring the varieties sown in each unit, what happens is that it only brings me the units that have sown varieties.

What I need is to bring all the units, no matter they do not have varieties, I tried it with a LEFT JOIN but it does not work for me.

These are the structures of the tables: 

CREATE TABLE IF NOT EXISTS 'sw_sowing' (
  'id' int(10) unsigned NOT NULL AUTO_INCREMENT,
  'id_production_unit_detail' int(10) unsigned NOT NULL,
  'id_variety' int(10) unsigned NOT NULL,
  'date' date NOT NULL,
  'status' smallint(6) unsigned NOT NULL DEFAULT '100',
  'id_tenant' smallint(6) unsigned NOT NULL,
  PRIMARY KEY ('id'));

  INSERT INTO 'sw_sowing' ('id', 'id_production_unit_detail', 'id_variety', 'date', 'status', 'id_tenant') VALUES
(8904, 1552, 3, '2014-01-03', 100, 1),
(8905, 1554, 3, '2014-01-03', 100, 1),
(8906, 1556, 3, '2014-01-03', 100, 1),
(8907, 1558, 3, '2014-01-03', 100, 1),
(8908, 1560, 3, '2014-01-03', 100, 1),
(8967, 1609, 4, '2008-06-23', 100, 1),
(8981, 1567, 12, '2009-07-13', 100, 1),
(8992, 1571, 1, '2008-08-11', 100, 1),
(8993, 1573, 1, '2008-08-11', 100, 1),
(8994, 1593, 1, '2008-08-11', 100, 1);

CREATE TABLE IF NOT EXISTS 'pr_production_units_details' (
  'id' int(10) unsigned NOT NULL AUTO_INCREMENT,
  'id_production_unit' smallint(6) unsigned NOT NULL,
  'id_grouper_detail' int(10) unsigned NOT NULL,
  'production_units_detail' smallint(6) NOT NULL,
  'area' decimal(10,2) unsigned NOT NULL,
  'status' smallint(6) unsigned NOT NULL DEFAULT '100',
  'id_tenant' smallint(6) unsigned NOT NULL,
  PRIMARY KEY ('id'));

  INSERT INTO 'pr_production_units_details' ('id', 'id_production_unit', 'id_grouper_detail', 'production_units_detail', 'area', 'status', 'id_tenant') VALUES
(1491, 1, 13, 1, 0.00, 100, 1),
(1492, 1, 13, 2, 18.45, 100, 1),
(1493, 1, 13, 3, 0.00, 100, 1),
(1494, 1, 13, 4, 18.45, 100, 1),
(1495, 1, 13, 5, 0.00, 100, 1),
(1496, 1, 13, 6, 18.45, 100, 1),
(1497, 1, 13, 7, 0.00, 100, 1),
(1498, 1, 13, 8, 18.45, 100, 1),
(1499, 1, 13, 9, 0.00, 100, 1),
(1500, 1, 13, 10, 18.45, 100, 1),
(1501, 1, 13, 11, 4.14, 100, 1),
(1502, 1, 13, 12, 25.20, 100, 1),
(1503, 1, 13, 13, 4.14, 100, 1),
(1504, 1, 13, 14, 25.20, 100, 1),
(1505, 1, 13, 15, 4.14, 100, 1),
(1506, 1, 13, 16, 25.20, 100, 1),
(1507, 1, 13, 17, 4.14, 100, 1),
(1508, 1, 13, 18, 25.20, 100, 1),
(1509, 1, 13, 19, 4.14, 100, 1),
(1510, 1, 13, 20, 25.20, 100, 1),
(1511, 1, 13, 21, 28.80, 100, 1),
(1512, 1, 13, 22, 28.80, 100, 1),
(1513, 1, 13, 23, 28.80, 100, 1),
(1514, 1, 13, 24, 28.80, 100, 1),
(1515, 1, 13, 25, 28.80, 100, 1),
(1516, 1, 13, 26, 28.80, 100, 1),
(1517, 1, 13, 27, 28.80, 100, 1),
(1518, 1, 13, 28, 28.80, 100, 1),
(1519, 1, 13, 29, 28.80, 100, 1),
(1520, 1, 13, 30, 28.80, 100, 1),
(1521, 1, 13, 31, 28.80, 100, 1),
(1522, 1, 13, 32, 28.80, 100, 1),
(1523, 1, 13, 33, 28.80, 100, 1),
(1524, 1, 13, 34, 28.80, 100, 1),
(1525, 1, 13, 35, 0.00, 100, 1),
(1526, 1, 13, 36, 28.80, 100, 1),
(1527, 1, 13, 37, 0.00, 100, 1),
(1528, 1, 13, 38, 28.80, 100, 1),
(1529, 1, 13, 39, 0.00, 100, 1),
(1530, 1, 13, 40, 28.80, 100, 1),
(1531, 1, 13, 41, 0.00, 100, 1),
(1532, 1, 13, 42, 28.80, 100, 1),
(1533, 1, 13, 43, 0.00, 100, 1),
(1534, 1, 13, 44, 28.80, 100, 1),
(1535, 1, 13, 45, 0.00, 100, 1),
(1536, 1, 13, 46, 28.80, 100, 1),
(1537, 1, 13, 47, 0.00, 100, 1),
(1538, 1, 13, 48, 28.80, 100, 1),
(1539, 1, 13, 49, 0.00, 100, 1),
(1540, 1, 13, 50, 28.80, 100, 1),
(1541, 1, 13, 51, 0.00, 100, 1),
(1542, 1, 13, 52, 28.80, 100, 1),
(1543, 1, 13, 53, 0.00, 100, 1),
(1544, 1, 13, 54, 28.80, 100, 1),
(1545, 1, 13, 55, 0.00, 100, 1),
(1546, 1, 13, 56, 28.80, 100, 1),
(1547, 1, 13, 57, 0.00, 100, 1),
(1548, 1, 13, 58, 28.80, 100, 1),
(1549, 1, 13, 59, 0.00, 100, 1),
(1550, 1, 13, 60, 28.80, 100, 1),
(1551, 1, 13, 61, 0.00, 100, 1),
(1552, 1, 13, 62, 28.80, 100, 1),
(1553, 1, 13, 63, 0.00, 100, 1),
(1554, 1, 13, 64, 28.80, 100, 1),
(1555, 1, 13, 65, 0.00, 100, 1),
(1556, 1, 13, 66, 28.80, 100, 1),
(1557, 1, 13, 67, 0.00, 100, 1),
(1558, 1, 13, 68, 28.80, 100, 1),
(1559, 1, 13, 69, 0.00, 100, 1),
(1560, 1, 13, 70, 28.80, 100, 1),
(1561, 1, 13, 71, 28.80, 100, 1),
(1562, 1, 13, 72, 28.80, 100, 1),
(1563, 1, 13, 73, 28.80, 100, 1),
(1564, 1, 13, 74, 28.80, 100, 1),
(1565, 1, 13, 75, 28.80, 100, 1),
(1566, 1, 13, 76, 28.80, 100, 1),
(1567, 1, 13, 77, 28.80, 100, 1),
(1568, 1, 13, 78, 28.80, 100, 1),
(1569, 1, 13, 79, 28.80, 100, 1),
(1570, 1, 13, 80, 28.80, 100, 1),
(1571, 1, 13, 81, 28.80, 100, 1),
(1572, 1, 13, 82, 28.80, 100, 1),
(1573, 1, 13, 83, 28.80, 100, 1),
(1574, 1, 13, 84, 28.80, 100, 1),
(1575, 1, 13, 85, 28.80, 100, 1),
(1576, 1, 13, 86, 28.80, 100, 1),
(1577, 1, 13, 87, 28.80, 100, 1),
(1578, 1, 13, 88, 28.80, 100, 1),
(1579, 1, 13, 89, 28.80, 100, 1),
(1580, 1, 13, 90, 28.80, 100, 1),
(1581, 1, 13, 91, 28.80, 100, 1),
(1582, 1, 13, 92, 28.80, 100, 1),
(1583, 1, 13, 93, 28.80, 100, 1),
(1584, 1, 13, 94, 28.80, 100, 1),
(1585, 1, 13, 95, 28.80, 100, 1),
(1586, 1, 13, 96, 28.80, 100, 1),
(1587, 1, 13, 97, 28.80, 100, 1),
(1588, 1, 13, 98, 28.80, 100, 1),
(1589, 1, 13, 99, 28.80, 100, 1),
(1590, 1, 13, 100, 28.80, 100, 1),
(1591, 1, 13, 101, 28.80, 100, 1),
(1592, 1, 13, 102, 28.80, 100, 1),
(1593, 1, 13, 103, 28.80, 100, 1),
(1594, 1, 13, 104, 28.80, 100, 1),
(1595, 1, 13, 105, 28.80, 100, 1),
(1596, 1, 13, 106, 28.80, 100, 1),
(1597, 1, 13, 107, 28.80, 100, 1),
(1598, 1, 13, 108, 28.80, 100, 1),
(1599, 1, 13, 109, 28.80, 100, 1),
(1600, 1, 13, 110, 28.80, 100, 1),
(1601, 1, 13, 111, 28.80, 100, 1),
(1602, 1, 13, 112, 28.80, 100, 1),
(1603, 1, 13, 113, 28.80, 100, 1),
(1604, 1, 13, 114, 28.80, 100, 1),
(1605, 1, 13, 115, 28.80, 100, 1),
(1606, 1, 13, 116, 28.80, 100, 1),
(1607, 1, 13, 117, 28.80, 100, 1),
(1608, 1, 13, 118, 28.80, 100, 1),
(1609, 1, 13, 119, 28.80, 100, 1),
(1610, 1, 13, 120, 28.80, 100, 1),
(1611, 1, 13, 121, 28.80, 100, 1),
(1612, 1, 13, 122, 28.80, 100, 1),
(1613, 1, 13, 123, 28.80, 100, 1),
(1614, 1, 13, 124, 28.80, 100, 1),
(1615, 1, 13, 125, 28.80, 100, 1),
(1616, 1, 13, 126, 28.80, 100, 1),
(1617, 1, 13, 127, 28.80, 100, 1),
(1618, 1, 13, 128, 28.80, 100, 1),
(1619, 1, 13, 129, 28.80, 100, 1),
(1620, 1, 13, 130, 28.80, 100, 1),
(1621, 1, 13, 131, 28.80, 100, 1),
(1622, 1, 13, 132, 28.80, 100, 1),
(1623, 1, 13, 133, 28.80, 100, 1),
(1624, 1, 13, 134, 28.80, 100, 1),
(1625, 1, 13, 135, 28.80, 100, 1),
(1626, 1, 13, 136, 28.80, 100, 1),
(1627, 1, 13, 137, 28.80, 100, 1),
(1628, 1, 13, 138, 28.80, 100, 1),
(1629, 1, 13, 139, 28.80, 100, 1),
(1630, 1, 13, 140, 28.80, 100, 1),
(1631, 1, 13, 141, 28.80, 100, 1),
(1632, 1, 13, 142, 28.80, 100, 1),
(1633, 1, 13, 143, 28.80, 100, 1),
(1634, 1, 13, 144, 28.80, 100, 1),
(1635, 1, 13, 145, 28.80, 100, 1),
(1636, 1, 13, 146, 28.80, 100, 1),
(1637, 1, 13, 147, 28.80, 100, 1),
(1638, 1, 13, 148, 28.80, 100, 1),
(1639, 1, 13, 149, 28.80, 100, 1),
(1640, 1, 13, 150, 28.80, 100, 1);

CREATE TABLE IF NOT EXISTS 'pr_varieties' (
  'id' int(10) unsigned NOT NULL AUTO_INCREMENT,
  'variety' varchar(80) COLLATE utf8_spanish_ci NOT NULL,
  PRIMARY KEY ('id'));

  INSERT INTO 'pr_varieties' ('id', 'variety') VALUES
(1, 'COTE D AZUR'),
(2, 'CHERRY BAY'),
(3, 'SALMON BAY'),
(4, 'VIRGINIA'),
(5, 'ORANGE QUEEN'),
(6, 'PRIMADONNA'),
(8, 'SENNA'),
(12, 'VERDI'),
(14, 'SIBERIA'),
(16, 'VOYAGER'),
(17, 'ANASTACIA'),
(18, 'PATAGONIA'),
(19, 'HAWAIIAN DREAM'),
(20, 'KIMBERLY'),
(23, 'LUCERO'),
(24, 'NADYA'),
(26, 'ANNA'),
(29, 'HAPPY FEET'),
(30, 'MONTE VECCHIO'),
(32, 'FLORIANO'),
(34, 'COCKTAIL'),
(35, 'BARBADOS'),
(39, 'ISTANBUL'),
(40, 'HERCULES'),
(41, 'MONTE LAVA'),
(42, 'ROME'),
(44, 'PALERMO'),
(46, 'SET POINT'),
(47, 'CARTAGENA'),
(50, 'NATALYA'),
(52, 'K2'),
(54, 'MARIAM'),
(55, 'ARIEL'),
(56, 'ALESSO'),
(66, 'M 45'),
(85, 'TONATIUH'),
(132, 'R 1733'),
(144, 'R 1687'),
(148, 'CLEO');
    
asked by Fabian Sierra 17.10.2017 в 22:59
source

1 answer

0

Fabian, first of all a question well-aligned with the principle of "minimum, complete and verifiable" is appreciated.

If I did not misunderstand your question and the business structures, what you need first is to get the last sw_sowing.id per id_production_unit_detail , that you can do it like this: 

SELECT sw1.id_production_unit_detail,
                   sw1.id_variety
            FROM sw_sowing sw1
            INNER JOIN ( SELECT id_production_unit_detail,
                                MAX(sw_sowing.id) AS id
                                FROM sw_sowing 
                                WHERE sw_sowing.status != 0
                                      AND sw_sowing.id_tenant = 1
                                      AND YEARWEEK(sw_sowing.date) <= 201741
                                GROUP BY  sw_sowing.id_production_unit_detail
                        ) sw2
                        ON sw1.id = sw2.id
                        AND sw1.id_production_unit_detail = sw2.id_production_unit_detail

Then, you should do a LEFT to this query and another LEFT to pr_varieties , which would end up being like this: 

SELECT  pr_production_units_details.id AS idProductionUnit, 
        pr_production_units_details.production_units_detail AS  productionUnit,
        pr_varieties.variety
FROM pr_production_units_details
LEFT JOIN ( SELECT sw1.id_production_unit_detail,
                   sw1.id_variety
            FROM sw_sowing sw1
            INNER JOIN ( SELECT id_production_unit_detail,
                                MAX(sw_sowing.id) AS id
                                FROM sw_sowing 
                                WHERE sw_sowing.status != 0
                                      AND sw_sowing.id_tenant = 1
                                      AND YEARWEEK(sw_sowing.date) <= 201741
                                GROUP BY  sw_sowing.id_production_unit_detail
                        ) sw2
                        ON sw1.id = sw2.id
                        AND sw1.id_production_unit_detail = sw2.id_production_unit_detail
         ) sw 
         ON sw.id_production_unit_detail = pr_production_units_details.id
LEFT JOIN pr_varieties
  ON pr_varieties.id = sw.id_variety
WHERE pr_production_units_details.id_grouper_detail = 13
ORDER BY pr_production_units_details.id;
    
answered by 17.10.2017 / 23:47
source
C, Print and save character string Access to data within a loop in the context - Django