CODEWITHSUNDEEP
sqlpostgresql
Nikk
Nikk

Reputation: 7891

PostgreSQL can't make Self Join

I have a table:

| acctg_cath_id | parent | description |
| 1             | 20     | Bills       |
| 9             | 20     | Invoices    |
| 20            |        | Expenses    |
| 88            | 30     |
| 89            | 30     |
| 30            |        |

And I want to create a self join in order to group my items under a parent.

Have tried this, but it doesn't work:

SELECT
    accounting.categories.acctg_cath_id,
    accounting.categories.parent

FROM accounting.categories a1, accounting.categories a2

WHERE a1.acctg_cath_id=a2.parent

I get error: invalid reference to FROM-clause entry for table "categories"

When I try:

a.accounting.categories.acctg_cath_id
b.accounting.categories.acctg_cath_id

I get error: cross-database references are not implemented: a.accounting.categories.acctg_cath_id

Desired output:

What am I doing wrong here?

Upvotes: 0

Views: 497

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 94913

It seems you merely want to sort the rows:

select *
from accounting.categorie
order by coalesce(parent, acctg_cath_id), parent nulls first, acctg_cath_id;

Result:

+---------------+--------+-------------+
| acctg_cath_id | parent | description |
+---------------+--------+-------------+
| 20            |        | Expenses    |
| 1             | 20     | Bills       |
| 9             | 20     | Invoices    |
| 30            |        |             |
| 88            | 30     |             |
| 89            | 30     |             |
+---------------+--------+-------------+

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You don't need a self-join. You don't need aggregation. You just need a group by clause:

SELECT ac.*
FROM accounting.categories ac
ORDER BY COALESCE(ac.parent, ac.acctg_cath_id),
         (CASE WHEN ac.parent IS NULL THEN 1 ELSE 2 END),
         ac.acctg_cath_id;

Upvotes: 0

forpas
forpas

Reputation: 164099

You don't need grouping, only self join:

select 
  c.acctg_cath_id parentid, c.description parent,
  cc.acctg_cath_id childid, cc.description child
from (
  select distinct parent
  from categories
) p inner join categories c
on p.parent = c.acctg_cath_id
inner join categories cc on cc.parent = p.parent
where p.parent = 20

You can remove the WHERE clause if you want all the parents with all their children.
See the demo.
Results:

> parentid | parent   | childid | child   
> -------: | :------- | ------: | :-------
>       20 | Expences |       1 | Bills   
>       20 | Expences |       9 | Invoices

Upvotes: 0

J Spratt
J Spratt

Reputation: 2012

Your syntax is performing a cross join: FROM accounting.categories a1, accounting.categories a2

Try the following:

SELECT
    a2.acctg_cath_id,
    a2.parent
FROM accounting.categories a1
    JOIN accounting.categories a2 ON (a1.acctg_cath_id = a2.parent)
;

Examine the DBFiddle.

Upvotes: 0

Related Questions