CODEWITHSUNDEEP
mysqlsqlrecord
Sylar
Sylar

Reputation: 305

Mysql - Query to show a single record of two linked tables whose condition is only in one of them

I have two tables linked by the id_factura field.

The 'factura' table generates an id for each sale that is made, can contain several products of the sale table, and also has a field called num_factura with the invoice number.

The 'venta' table has products with 'nuevo' or 'usado' status, among others. There are several queries already made in these two tables. These tables have other fields such as the registration date ...

Table factura

+---------------+-------------+
|   id_factura  | num_factura |   
+---------------+-------------+
|       1       |      1      |
|       2       |      2      |
|       3       |      1      |
|       4       |      2      |
|       5       |      3      |
+---------------+-------------+

Tabla venta

+---------------+-------------+-------------+
|   id_venta    |   estado    | id_factura  |
+---------------+-------------+-------------+
|       1       |    usado    |     1       |
|       2       |    usado    |     2       |
|       3       |    nuevo    |     1       |
|       4       |    nuevo    |     2       |
|       5       |    nuevo    |     3       |    
+---------------+-------------+-------------+

The question I want to ask is that, indicating the status of the sale-purchase table, return the last invoice number and then I add 1 to the new record.

But the problem I am having is that when doing the where estado = 'usado' it does not affect me since I take the largest invoice number regardless of the condition.

SELECT num_factura
FROM factura f
LEFT JOIN venta v ON v.id_factura = f.id_factura
WHERE estado = 'usado'
GROUP BY num_factura
ORDER BY num_factura DESC LIMIT 1;

I get the following result

+---------------+-------------+
|  num_factura  |  id_factura |   
+---------------+-------------+
|       3       |      5      |
+---------------+-------------+

I want this result, because this result is last from estado = 'usado'

+---------------+-------------+
|  num_factura  |  id_factura |
+---------------+-------------+
|       2       |      2      |
+---------------+-------------+

Upvotes: 1

Views: 45

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

You could use a join between factura and the subquery for max factura related to usado

select  f.id_factura, t.num_facture
from  factura f
inner join  (
  SELECT MAX(f.num_factura)  num_factura
  FROM factura f
  JOIN venta v ON v.id_factura = f.id_factura 
    AND  v.estado = 'usado';
) t  on t.num_factura = f.num_factura

Upvotes: 1

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Just change your LEFT JOIN to INNER JOIN, because left join is considering all the rows from factura table, irrespective of the fact that estado in venta table is usado or not.

Also, you can simply use MAX() instead without any GROUP BY to get the highest num_factura

SELECT MAX(f.num_factura) AS num_factura
FROM factura f
JOIN venta v ON v.id_factura = f.id_factura  -- changed to INNER JOIN
WHERE v.estado = 'usado';

Result

| num_factura |
| ----------- |
| 2           |

View on DB Fiddle

Upvotes: 0

Related Questions