Display values in one table but not in the other

Question

Good morning. I’m new to Database and SQL, and I have a question in a college exercise. The objective of the exercise is:

I was able to do the part of presenting the number, description of the account, name of the supplier and locality, but he presents me with all the values and I only want those where the balance sheets do not appear in any invoice but I am not able to do that part. Together sending code

select NúmeroConta, Descrição, Nome, Localidade
from  ContasBalanço join Fornecedores
on ContasBalanço.NúmeroConta = Fornecedores.ContaBalançoPredefinida join Facturas
on Fornecedores.IDFornecedor = Facturas.Fornecedor
where 

In my opinion, I think the condition that remains to be added is in the Where yet I do not know how to do that part. Together I also leave picture of the tables.

Balance Sheet:

Suppliers:

Invoices:

Itensinvoice:

I’d really appreciate it if someone could help me. As I said, I’m new at this and I’m enjoying learning about databases and I find this interesting, yet this little problem has arisen and I don’t know how to solve it. Regards

Answer 1

To understand more about Joins, I recommend reading this article or this other.

Basically, what you need is all the data from one table (Balance Accounts) while you just need to check the other (Invoice) to select the requested data, in which case the ideal would be to make a left Join, so the script would look like this:

select NúmeroConta, Descrição, Nome, Localidade
from  ContasBalanço join Fornecedores
on ContasBalanço.NúmeroConta = Fornecedores.ContaBalançoPredefinida 
left join Facturas
on Fornecedores.IDFornecedor = Facturas.Fornecedor
where 
Facturas.IDFactura is null
ORDER BY ContasBalanço.NúmeroConta;

Another solution to a similar problem can be found here.

Answer 2

I’ll give you some tips, first you should organize your code leave well structured so that you can read the information more easily:

SELECT cbal.NúmeroConta, cbal.Descrição, forn.Nome, forn.Localidade
FROM ContasBalanço cbal
  INNER JOIN facturasacturas fac
    ON fac.ContaBalançoPredefinida = cbal.NúmeroConta
  INNER JOIN Fornecedores forn
    ON forn.IDFornecedor = fac.Fornecedor
ORDER BY cbal.NúmeroConta ASC

See how I separated the lines, SELECT, FROM, INNER, ON, ORDER BY.

When one thing belongs to another as the ON in the INNER I use an additional indentation.

Let’s go to your exercise, first see all the tables that relate to each other and that you will use and make the relationships, INNER JOIN, LEFT JOIN, etc....

I don’t know if any table is missing in your code but I couldn’t find the table Itensfactura. But every time you perform the INNER JOIN you will only bring the data if it exists in both tables, so I see no need to use the WHERE.

In case to use the WHERE you should use the LEFT JOIN that it brings all the data from the table of FROM and only brings the data from the table of LEFT if data exists, otherwise the columns will come blank. Test.

If the WHERE with LEFT JOIN You should do the following:

SELECT cbal.NúmeroConta, cbal.Descrição, forn.Nome, forn.Localidade
FROM ContasBalanço cbal
  LEFT JOIN facturasacturas fac
    ON fac.ContaBalançoPredefinida = cbal.NúmeroConta
  INNER JOIN Fornecedores forn
    ON forn.IDFornecedor = fac.Fornecedor
WHERE fac.IDFactura IS NOT NULL
ORDER BY cbal.NúmeroConta ASC

Note the WHERE, IS NOT NULL means that it will not bring anything that is void, in case the invoices that have not been issued.

And last I ordered the dice with the ORDER BY ASC (Ascendant) as requested by the exercise. o ORDER BY DESC orders downwards, take the test.

Another tip I give when starting on SQL is to understand everything you are doing, understand each command and think about what you will do before you do, so you learn very quickly. I hope I have helped you!