SQL Join – Join More Than 3 Tables in One Query with Many Comparisons

firebirdjoin;liketable

I use an overlycomplex and poorly designed software at my work, and now I need to produce a report of invoices issued between a period of time.

Invoices are held at table ENTR2012, which uses the following structure:

CREATE TABLE ENTR2012 (
ABREV VARCHAR(8) NOT NULL,
NOTA VARCHAR(9) NOT NULL,
NUMEROEMIT VARCHAR(18) NOT NULL,
DATAENTRADA TIMESTAMP,
DATAEMISSAO TIMESTAMP,
VALCONTABIL DOUBLE PRECISION,
PRIMARY KEY (ABREV,NOTA,NUMEROEMIT)

From this table I want only ABREV, NOTA, NUMEROEMIT, DATAENTRADA, DATAEMISSAO, VALCONTABIL.

This should be my table:

| ABREV  | NOTA | NUMEROEMIT | DATAENTRADA | DATAEMISSAO | VALCONTABIL |
| dep1   | 001  | 1234567    | 01.01.2012  | 31.01.2012  | 1,000.00    |
| dep2   | 001  | 898909201  | 02.01.2012  | 16.01.2012  | 998.00      |

As you can see, ABREV holds the departament ID that issued the invoice, NOTA is the number of the invoice (and each depart. have their own invoice count), NUMEROEMIT is the client ID, DATAENTRADA is the date the invoice was issued, DATAEMISSA is the day the invoice was payed, and VALCONTABIL the value of the invoice.

Now I need to find the Invoice description, that is, what exactly we are charging for.

A second table, ITST2012 holds the descriptions:

CREATE TABLE ITST2012 (
ABREV VARCHAR(8) NOT NULL,
NOTA VARCHAR(9) NOT NULL,
NUMEROEMIT VARCHAR(18) NOT NULL,
DATAENTRADA TIMESTAMP,
CODPRODUTO VARCHAR(13),
ALIQISS DOUBLE PRECISION,
VALTOTAL DOUBLE PRECISION,
PRIMARY KEY (ABREV,NOTA,NUMEROEMIT)

ITST has ABREV (dep. ID), NOTA (invoice number), DATAEMISSAO (issue date), and the info I want CODPRODUTO (the product code) and ALIQISS (an useful number my boss requires for this report).

As each department (ABREV) has its own numbering order (NOTA), we have to compare the dep. ID (ABREV), the number (NOTA) and the value (VALTOTAL) at the same time to confirm this is the right invoice. The product should be like this:

| ABREV  | NOTA | NUMEROEMIT | DATAENTRADA | DATAEMISSAO | VALCONTABIL | ITST2012.CODPRODUTO | ITST2012.ALIQISS |
| dep1   | 001  | 1234567    | 01.01.2012  | 31.01.2012  | 1,000.00    | 2                   | 5.00             |
| dep2   | 001  | 898909201  | 02.01.2012  | 16.01.2012  | 998.00      | 3                   | 15.00            |

Next, I need to JOIN the ITST2012.CODPRODUTO with the real product description. Once again, each department uses its own numeration system, so we need to match ABREV (dep. ID) and CODPRODUTO (the product ID) with the table PRODUTOS.

CREATE TABLE PRODUTOS (
ABREV VARCHAR(8) NOT NULL,
CODIGO VARCHAR(13) NOT NULL,
DESCRICAO VARCHAR(50),
PRIMARY KEY (ABREV,CODIGO)

We just need to be sure ABREV = PRODUTOS.ABREV and ITST2012.CODPRODUTO = PRODUTOS.CODIGO. The table should be like this:

| ABREV  | NOTA | NUMEROEMIT | DATAENTRADA | DATAEMISSAO | VALCONTABIL | ITST2012.CODPRODUTO | PRODUTOS.DESCRICAO | ITST2012.ALIQISS |
| dep1   | 001  | 1234567    | 01.01.2012  | 31.01.2012  | 1,000.00    | 2                   | my_product         | 5.00             |
| dep2   | 001  | 898909201  | 02.01.2012  | 16.01.2012  | 998.00      | 3                   | my_product2        | 15.00            |

Finally, we just have to join the client ID (NUMEROEMIT at the first table) with the table FORNEC, which holds all our clients (this time, there there is no need to check department, we use the same table for everything)

CREATE TABLE FORNEC (
RAZSOC VARCHAR(55),
CNPJCPF VARCHAR(18),

We just have to match NUMEROEMIT = FORNEC.CNPJCPF, and get the RAZSOC info.

The final table for the report is like this:

| ABREV  | NOTA | NUMEROEMIT | FORNEC.RAZSOC | DATAENTRADA | DATAEMISSAO | VALCONTABIL | ITST2012.CODPRODUTO | PRODUTOS.DESCRICAO | ITST2012.ALIQISS |
| dep1   | 001  | 1234567    | my_client     | 01.01.2012  | 31.01.2012  | 1,000.00    | 2                   | my_product         | 5.00             |
| dep2   | 001  | 898909201  | my_client2    | 02.01.2012  | 16.01.2012  | 998.00      | 3                   | my_product2        | 15.00            |

My problem is: HOW? I have no idea how to join all those tables at the same time, using SQL. I can INNER JOIN the client easily, but I can't write an query which compares many fields at the same time, and still joins the right way. I'm quite desperate as I've only worked with databases of my own design, not this macaroni database with too much info inside to few tables.

Thanks a lot for any SQL Wizard that can string this together with me.

PS. The database is Firebird, with no row ids.

The question have been answered, so I thank you very much!

The final code is the following:

SELECT notas.ABREV, notas.NOTA, notas.NUMEROEMIT, prestador.RAZSOC, notas.DATAENTRADA, notas.DATAEMISSAO, notas.VALCONTABIL, itens.CODPRODUTO, prod.DESCRICAO, itens.ALIQISS FROM ENTR2012 notas
INNER JOIN ITST2012 itens
ON notas.ABREV=itens.ABREV AND notas.NOTA=itens.NOTA AND notas.VALCONTABIL=itens.VALTOTAL
INNER JOIN PRODUTOS prod
ON itens.ABREV=prod.ABREV AND itens.CODPRODUTO=prod.CODIGO
INNER JOIN FORNEC prestador
ON itens.NUMEROEMIT=prestador.NUMERO;

Best Answer

I'm really not sure if this is going to work in this Database of yours, but I made tests with much simpler values and tables in my Oracle XE database and it worked just fine, I would give it a go.

SELECT * FROM ENTR2012
INNER JOIN ITST2012
ON ENTR2012.ABREV=ITST2012.ABREV AND ENTR2012.NOTA=ITST2012.NOTA AND ENTR2012.VALTOTAL=ITST2012.VALTOTAL
INNER JOIN PRODUTOS
ON ITST2012.ABREV=PRODUTOS.ABREV AND ITST2012.CODPRODUTO=PRODUTOS.CODIGO
INNER JOIN FORNEC
ON ITST2012.NUMEROEMIT=FORNEC.CNPJCPF;

Please, review values, columns and such because maybe I got lost during the writing.

Related Solutions

Sql-server – SQL join query to show rows with non-existent rows in one table

Thank you for SQLfiddle and sample data! I wish more questions started this way.

If you want all members regardless of whether they have an entry for that date, you want a LEFT OUTER JOIN. You were very close with this version however a little trick with outer joins is that if you add a filter to the outer table in the WHERE clause, you turn an outer join to an inner join, because it will exclude any rows that are NULL on that side (because it doesn't know if NULL would match the filter or not).

I modified the first query to get a row for every member:

SELECT Members.Member_ID
      ,Time_Entry.Date_Start
      ,Time_Entry.Hours_Actual
      ,Time_Entry.Hours_Bill
FROM dbo.Members
  LEFT OUTER JOIN dbo.Time_Entry
--^^^^ changed from FULL to LEFT
  ON Members.Member_ID = Time_Entry.Member_ID
  AND Time_Entry.Date_Start = '20131110';
--^^^ changed from WHERE to AND

I'll leave it as an exercise for the reader to take it from there and add the other columns, formatting, COALESCE etc.

Some other notes:

please always use the schema prefix when creating and referencing objects
please always use a length when converting to varchar etc.
stay away from ambiguous, regional date formats like mm-dd-yyyy

consider using aliases to make your queries easier to read. E.g. the above could be re-written as:

SELECT m.Member_ID
  ,t.Date_Start
  ,t.Hours_Actual
  ,t.Hours_Bill
FROM dbo.Members AS m
LEFT OUTER JOIN dbo.Time_Entry AS t
ON m.Member_ID = t.Member_ID
AND t.Date_Start = '20131110';

... a lot tidier, IMHO, as long as you use sensible aliases.

SQL Server – How to JOIN More Than Two Tables

try this

SELECT [Subject] , [Type], MarksObtd 
  FROM [Marks_Table] 
  INNER JOIN [Subjects_Table] 
  ON [Marks_Table].SubjectCode = [Subjects_Table].SubjectCode 
  INNER JOIN [Types_Table] 
  ON [Marks_Table].TypeID = [Types_Table].TypeID

As james added comment, I think it is a simple join and you need to change [TypeID] in select clause to [Type]

Best Answer

Related Solutions

Sql-server – SQL join query to show rows with non-existent rows in one table

SQL Server – How to JOIN More Than Two Tables

Related Question