Problem Left join mysql [closed]

Question

Problem Left join mysql [closed]

Navigation

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

1

I need to do a left join in MySql and it's not working. I have 4 tables: articulos , subcategoria , categoria and ventas . I want to show all the independent items that have sales or not.

Each article has a subcategory and a category respectively. And I try to show filtered sales by category. this is what I am trying:

SELECT 
    A.*, 
    B.FECHA, 
    B.TOTAL
FROM
    ARTICULOS A,
    SUBCATEGORIA C,
    CATEGORIA D
        LEFT JOIN
    VENTAS B ON A.IDARTICULO = B.FK_IDARTICULO
WHERE
        A.FK_IDSUBCATEGORIA = C.ID_SUBCATEGORIA
    AND C.FK_IDCATEGORIA = D.ID_CATEGORIA
    AND D.ID_CATEGORIA = 1

This only shows results for articles that have sales. How can I do it?? thanks

mysql join

asked by daniel2017- 23.03.2017 в 15:24

source

4 answers

Next and previous buttons in form / php query Display Image in a PictureBox using Ozeki SDK and C #

score 1 · Answer 1

Try to make a query without the where , maybe that condition affects the final result:

SELECT A.*,B.FECHA,B.TOTAL
FROM ARTICULOS A
LEFT JOIN VENTAS B ON A.IDARTICULO=B.FK_IDARTICULO

You can also check the DB, to see if you actually have records in Items that do not have related sales.

score 1 · Answer 2

And so?

SELECT 
    A.*, 
    B.FECHA, 
    B.TOTAL
FROM
    ARTICULOS A 
INNER JOIN SUBCATEGORIA C ON A.FK_IDSUBCATEGORIA = C.ID_SUBCATEGORIA 
INNER JOIN CATEGORIA D ON C.FK_IDCATEGORIA = D.ID_CATEGORIA 
LEFT JOIN VENTAS B ON A.IDARTICULO = B.FK_IDARTICULO

score 0 · Answer 3

0

Given your query, the best way is to use a right join (although it seems weird), otherwise, the other option is to invest the left join (sales to the left side of the relationship).

answered by 23.03.2017 в 17:49

score -2 · Answer 4

Try it like this:

 SELECT 
A.*, 
B.FECHA, 
B.TOTAL
 FROM
ARTICULOS A,
SUBCATEGORIA C,
CATEGORIA D
 LEFT OUTER JOIN
VENTAS B ON A.IDARTICULO = B.FK_IDARTICULO
 WHERE
A.FK_IDSUBCATEGORIA = C.ID_SUBCATEGORIA
AND C.FK_IDCATEGORIA = D.ID_CATEGORIA
AND D.ID_CATEGORIA = 1

Instead of LEFT you use OUTER, this will bring you both those that match and those that do not match.