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:
select pal.palid, inv.palid
from pal right outer join inv on pal.palid=inv.palid
This should be done in the presentation layer. However, if you really need it to be done in the database side, you can use
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