L
L
LemanRass212018-12-12 16:45:14
SQL
LemanRass21, 2018-12-12 16:45:14

How to correctly formulate a SQL query, taking into account JOINs?

Hello.
At a basic level, I do well with JOINs, but this seems to be not a completely trivial task.
There is a contracts table, here are its fields:
id, vendor, purchaser - well, and a couple more pieces.
There is a tablttsa companies where there are only 2 fields
id, label
In the first table, 2 fields (vendor and purchaser) are the id-shniks of companies from the companies table.
I need to execute a query in such a way that I would get several records from the contracts table where vendor and purchaser will be replaced with the corresponding company names from the companies table (label field).
Here is my best but still not working attempt:

SELECT 
contracts.id, 
companies.label AS 'vendor',
companies.label AS 'purchaser', 
contracts.label, 
contracts.amount, 
contracts.status, 
contracts.created, 
contracts.delivered 
FROM 
contracts
INNER JOIN 
companies
ON
contracts.vendor = companies.id
AND
contracts.purchaser = companies.id
WHERE 
contracts.id=15

The request is executed, but both fields are replaced with the value from the first JOIN only.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
K
Konstantin Tsvetkov, 2018-12-12
@LemanRass21

This is one connection.
Two:

SELECT contracts.id, 
c1.label AS 'vendor',
c2.label AS 'purchaser', 
contracts.label, contracts.amount, contracts.status, contracts.created, contracts.delivered 
FROM contracts
INNER JOIN companies AS c1 ON contracts.vendor = c1.id
INNER JOIN companies AS c2 ON contracts.purchaser = c2.id
WHERE contracts.id=15

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question