Alexis Heijmeijer Alexis Heijmeijer - 7 months ago 16
SQL Question

SQL join two tables and the elements that satisfies one condition

Good afternoon,

I'm having an issue with two tables that I'm trying to join.

What I am trying to do is, I have to print a table with all products that is registered in some agenda (codControl), so the person can put his price.

But first I have to look into lctocotacao to see if he had already given a price to some product. But when I do this, I just get the products that has some price, and the other ones I dont see.

Here is an example of my table cadprodutoscotacao

codProduct desc codControl
1 abc 197
2 cde 197
3 fgh 197
1 abc 198


And my table lctocotacao

codProduct price codControl codPerson
1 2.5000 197 19
2 3.0000 197 37
3 4.5000 198 37


I have this SQL statement at the moment:

SELECT cadc.cod, cadc.desc, lcto.codEnt, lcto.price
FROM cadprodutoscotacao cadc JOIN lctocotacao lcto
ON cadc.codControl = lcto.codControl
AND cadc.codProduct = lcto.codProduct
AND cadc.codControl = '197'
AND lcto.codPerson = '19'
ORDER BY cadc.codControl;


What I'm getting:

cod desc price codPerson codControl
1 abc 2.5000 19 197


And the table I expect

cod desc price codPerson codControl
1 abc 2.5000 19 197
2 cde 197
3 fgh 197


197 and 19 will be parameters to my query.

Any ideas on how to proceed?

E D I T

Basically, I have two queries:

SELECT *
FROM cadprodutoscotacao
WHERE cadc_codControl = '197'


This first, to return all products registered in the agenda '197'.

And the second one:

SELECT *
FROM lctocotacao
WHERE codPerson = 19
AND codControl = '197'


This second one to return products that already has some price added by the Person 19 in the agenda 197.

I have to return one table, including all records from the first query, and, if there is some price in the second one, I have to "concatenate" them.

Example

Thanks in advance.

Answer

You need a LEFT JOIN, but you also need to be careful about the filtering conditions:

SELECT cadc.cod, cadc.desc, lcto.codEnt, lcto.price
FROM cadprodutoscotacao cadc LEFT JOIN
     lctocotacao lcto
     ON cadc.codControl = lcto.codControl AND
        cadc.cod = lcto.cod AND
        lcto.codEnt = '19'
WHERE cadc.codControl = '197'
ORDER BY cadc_codigo;

A LEFT JOIN keeps all rows in the first table, regardless of whether a match is found in the ON conditions. This applies to conditions on the first table as well as the second. Hence, you don't want to put filters on the first table in the ON clause.

The rule is: When using LEFT JOIN put filters on the first table in the WHERE clause. Filters on the second table go in the ON clause (otherwise the outer join is generally turned into an inner join).

Comments