Excluding multiple results in specific column (SQL JOIN)

Question

I'm taking my first steps in terms of practical SQL use in real life.

I have a few tables with contractual and financial information and the query works exactly as I need - to a certain point. It looks more or less like that:

SELECT /some columns/ from CONTRACTS

Linked 3 extra tables with INNER JOIN to add things like department names, product information etc. This all works but they all have simplish one-to-one relationship (one contract related to single department in Department table, one product information entry in the corresponding table etc).

Now this is my challenge:

I also need to add contract invoicing information doing something like:

inner join INVOICES on CONTRACTS.contnoC = INVOICES.contnoI

(and selecting also the Invoice number linked to the Contract number, although that's partly optional)

The problem I'm facing is that unlike with other tables where there's always one-to-one relationship when joining tables, INVOICES table can have multiple (or none at all) entries that correspond to a single contract no. The result is that I will get multiple query results for a single contract no (with different invoice numbers presented), needlessly crowding the query results.

Essentially I'm looking to add INVOICES table to a query to just identify if the contract no is present in the INVOICES table (contract has been invoiced or not). Invoice number itself could be presented (it is with INNER JOIN), however it's not critical as long it's somehow marked. Invoice number fields remains blank in the result with the INNER JOIN function, which is also necessary (i.e. to have the row presented even if the match is not found in INVOICES table).

SELECT DISTINCT would look to do what I need, but I seemed to face the problem that I need to levy DISTINCT criteria only for column representing contract numbers, NOT any other column (there can be same values presented, but all those should be presented).

Unfortunately I'm not totally aware of what database system I am using.

Answer 1

I would do it this way:

with mainquery as(
<<here goes you main query>>
),

invoices_rn as( 
select *, 
ROW_NUMBER() OVER (PARTITION BY contnoI order by 
<<some column to decide which invoice you want to take eg. date>>) as rn
)

invoices as (
select * from invoices_rn where rn = 1
)

select * from mainquery
left join invoices i on contnoC = i.contnoI

This gives you an ability to get all of the invoice details to your query, also it gives you full control of which invoice you want see in your main query. Please read more about CTEs; they are pretty handy and much easier to understand / read than nested selects.

I still don't know what database you are using. If ROW_NUMBER is not available, I will figure out something else :)

Also with a left join you should use COALESCE function for example:

COALESCE(i.invoice_number,'0')

Of course this gives you some more possibilities, you could for example in your main select do:

CASE WHEN i.invoicenumber is null then 'NOT INVOICED'
else 'INVOICED'
END as isInvoiced

Answer 2

Seems like the question is still getting some attention and in an effort to provide some explanation here are a few techniques.

If you just want any contract with details from the 1 to 1 tables you can do it similarily to what you have described. the key being NOT to include any column from Invoices table in the column list.

SELECT
   DISTINCT Contract, Department, ProductId .....(nothing from Invoices Table!!!)
FROM
    Contracts c
    INNER JOIN Departments D
    ON c.departmentId = d.Department
    INNER JOIN Product p
    ON c.ProductId = p.ProductId
    INNER JOIN Invoices i
    ON c.contnoC  = i.contnoI

Perhaps a Little cleaner would be to use IN or EXISTS like so:

SELECT
   Contract, Department, ProductId .....(nothing from Invoices Table!!!)
FROM
    Contracts c
    INNER JOIN Departments D
    ON c.departmentId = d.Department
    INNER JOIN Product p
    ON c.ProductId = p.ProductId
WHERE
    EXISTS (SELECT 1 FROM Invoices i WHERE i.contnoI = c.contnoC )

SELECT
   Contract, Department, ProductId .....(nothing from Invoices Table!!!)
FROM
    Contracts c
    INNER JOIN Departments D
    ON c.departmentId = d.Department
    INNER JOIN Product p
    ON c.ProductId = p.ProductId
WHERE
    contnoC IN (SELECT contnoI FROM Invoices)

Don't use IN if the SELECT ... list can return a NULL!!!

If you Actually want all of the contracts and just know if a contract has been invoiced you can use aggregation and a case expression:

SELECT
   Contract, Department, ProductId, CASE WHEN COUNT(i.contnoI) = 0 THEN 0 ELSE 1 END as Invoiced
FROM
    Contracts c
    INNER JOIN Departments D
    ON c.departmentId = d.Department
    INNER JOIN Product p
    ON c.ProductId = p.ProductId
    LEFT JOIN Invoices i
    ON c.contnoC  = i.contnoI
GROUP BY
    Contract, Department, ProductId

Then if you actually want to return details about a particular invoice you can use a technique similar to that of cybercentic87 if your RDBMS supports or you could use a calculated column with TOP or LIMIT depending on your system.

SELECT
   Contract, Department, ProductId, (SELECT TOP 1 InvoiceNo FROM invoices i WHERE c.contnoC  = i.contnoI ORDER BY CreateDate DESC) as LastestInvoiceNo
FROM
    Contracts c
    INNER JOIN Departments D
    ON c.departmentId = d.Department
    INNER JOIN Product p
    ON c.ProductId = p.ProductId
GROUP BY
    Contract, Department, ProductId

Answer 3

You can use SELECT ..., invoiced = 'YES' ... where exists ... union SELECT ..., invoiced = 'NO' ... where not exists ...

or you can use a column like "invoiced" with a subquery into invoices to set it's value depending on whether you get a hit or not