INNER JOIN skips a record

Question

INNER JOIN skips a record

Navigation

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

0

I would like to know what I am doing wrong, I have consulted with inner joins that should show 11 records and only show me 10, the tenth record is skipped, I do not know if I am doing wrong join or I have passed some field, I attach images and my tables, I thank you for your time!

Asset Table (top)

table branch (above)

Table Supplier (above)

Supply Table (above)

Data Table (above)

This is my query

SELECT * FROM branch A
INNER JOIN asset B ON (A.Id = B.Branch_Id)
INNER JOIN supplier C ON ( A.Id = B.Branch_Id AND B.Branch_Id = C.Branch_Id)
INNER JOIN supply D ON (C.Id = D.Supplier_Id)
INNER JOIN data E ON (D.Id = E.Supply_Id AND B.Id = E.Asset_Id)
ORDER BY E.Intake

Asset data

Branch Information

Supplier Data

Supply Data

Data Data

Output of the query

Once again I thank you for your help.

mysql sql sql-server

asked by Ed Brennenburg 28.09.2018 в 20:17

source

2 answers

Problems when checking Local storage How to make a self-calculated query in SQLITE on the same table?

score 0 · Answer 1

0

The condition hidden by record 10 would say it is:

INNER JOIN supplier C ON ( A.Id = B.Branch_Id AND B.Branch_Id = C.Branch_Id)

In the "supplier" table you do not have Branch_Id with value "2" ...

answered by 28.09.2018 в 20:40

score 0 · Answer 2

Most likely, in one of your INNER JOIN there is a null data or it does not exist. What I would do is change each of my INNER JOIN by LEFT JOIN to your main table branch in the following way:

SELECT * FROM branch A
LEFT JOIN asset B ON (A.Id = B.Branch_Id) -- Primero este ponemos en LEFT JOIN
INNER JOIN supplier C ON ( A.Id = B.Branch_Id AND B.Branch_Id = C.Branch_Id)
INNER JOIN supply D ON (C.Id = D.Supplier_Id)
INNER JOIN data E ON (D.Id = E.Supply_Id AND B.Id = E.Asset_Id)
ORDER BY E.Intake

This way when you execute the query you can see if one or more columns return null values. Trying to guess I think it is very likely that your problem is where you do the INNER JOIN with conditions:

INNER JOIN supplier C ON ( A.Id = B.Branch_Id AND B.Branch_Id = C.Branch_Id)

So maybe those two you want to try with LEFT JOIN to verify.

This is going to serve you just to know if there is inconsistency of data in your table, and hence you can make a decision.