Xavier Tapia Gonzalez Xavier Tapia Gonzalez - 5 months ago 23
SQL Question

Left Outer Join with just one Match

First of all, a little explanation of my database. I am working in a warehouse and I've got a lot of palets, every palet contains some objects. The table of palets is called "pal", and the palet of objects is called "inv". Every "pal" has its own name, called "palid", and it is a primary key of the table "pal". Every "inv" has its own name called "invid" and the name of the palet where it is. They have more fields but there are not important for my question.

What I want is a SQL query that shows me every "pal" matched with all its "inv"'s but no repetition of "pal". Let me show you with an example:

pal_1 contains inv_1 and inv_2,

pal_2 contains inv_3,

pal_3 does not contain nothing.

I want this:

pal_1 inv_1
NULL inv_2
pal_2 inv_3
pal_3 NULL


So I create this query:

select pal.palid, inv.palid
from pal right outer join inv on pal.palid=inv.palid


But I get this:

pal_1 inv_1
pal_1 inv_2
pal_2 inv_3
pal_3 NULL


And I don't want this repetition of pal_1 because I have like 1000 registers, and it is very difficult to know the state of my warehouse (if I have a lot of objects in my palets or not) without having this blanks on my query.

Do you know how to solve it?

Answer

This should be done in the presentation layer. However, if you really need it to be done in the database side, you can use ROW_NUMBER:

SELECT
    CASE 
        WHEN ROW_NUMBER() OVER(PARTITION BY p.palid ORDER BY(i.invid)) = 1
            THEN p.palid
        ELSE ''
    END AS palid,
    i.invid 
FROM pal p
LEFT JOIN inv i
    ON i.palid = p.palid
ORDER BY p.palid, i.invid
Comments