CODEWITHSUNDEEP
sqlpostgresql
Verh
Verh

Reputation: 71

How to merge two tables for a query?

I have table animal:

CREATE TABLE animal
(
    id_animal serial primary key,
    id_parent INT,
    lft INT NOT NULL,
    rgt INT NOT NULL

);

And table animal_lang:

CREATE TABLE animal_lang
(
        id_animal_lang serial  primary key,
        id_animal int  constraint animal_lang_ibfk_1 references animal on delete cascade,
        code varchar(5) not null,
        name varchar(100) not null,
        descri varchar(255) not null
);

From table animal i get data using query:

SELECT animal.*, (COUNT(parent.id_animal) - 1) AS depth 
FROM animal AS animal, animal AS parent 
WHERE (animal.lft BETWEEN parent.lft AND parent.rgt) 
GROUP BY animal.id_animal;

How to get this data additionally with 'name' and 'descri' from table animal_lang for specifed 'code'?

with

WHERE  animal_lang.code = 'eng'

result should looks:

https://i.sstatic.net/puoLK.png

I am using Nested Tree Model

sample data for animals:

INSERT INTO animal VALUES (43, 1, 2, 5);
INSERT INTO animal VALUES (46, 44, 7, 8);
INSERT INTO animal VALUES (47, 43, 3, 4);
INSERT INTO animal VALUES (1, NULL, 1, 14);
INSERT INTO animal VALUES (44, 1, 6, 11);
INSERT INTO animal VALUES (45, 1, 12, 13);
INSERT INTO animal VALUES (48, 44, 9, 10);

sample data for animal_lang

INSERT INTO animal_lang VALUES (1, 43, 'eng', 'EnglishTitleXXX', 'EnglishDXXX');
INSERT INTO animal_lang VALUES (2, 45, 'eng', 'EnglishTitleYYY', 'EnglishDYYY');
INSERT INTO animal_lang VALUES (3, 44, 'eng', 'EnglishTitleZZZ', 'EnglishDZZZ');
INSERT INTO animal_lang VALUES (4, 1, 'eng', 'EnglishTitleUUU', 'EnglishDUUU');
INSERT INTO animal_lang VALUES (5, 46, 'eng', 'EnglishTitleQQQ', 'EnglishDQQQ');
INSERT INTO animal_lang VALUES (6, 47, 'eng', 'EnglishTitleDDD', 'EnglishDDDD');
INSERT INTO animal_lang VALUES (7, 48, 'eng', 'EnglishTitleHHH', 'EnglishDHHH');
INSERT INTO animal_lang VALUES (8, 43, 'ger', 'GermanTitleXXX', 'GermanDXXX');
INSERT INTO animal_lang VALUES (9, 45, 'ger', 'GermanTitleYYY', 'GermanDYYY');
INSERT INTO animal_lang VALUES (10, 44, 'ger', 'GermanTitleZZZ', 'GermanDZZZ');
INSERT INTO animal_lang VALUES (11, 1, 'ger', 'GermanTitleUUU', 'GermanDUUU');
INSERT INTO animal_lang VALUES (12, 46, 'ger', 'GermanTitleQQQ', 'GermanDQQQ');
INSERT INTO animal_lang VALUES (13, 47, 'ger', 'GermanTitleDDD', 'GermanDDDD');
INSERT INTO animal_lang VALUES (14, 48, 'ger', 'GermanTitleHHH', 'GermanDHHH');

I tried to do this like that:

SELECT animal.*, trans.*, (COUNT(parent.id_animal) - 1) AS depth
FROM animal AS animal, animal AS parent
LEFT JOIN animal_trans as trans on animal.id_animal = trans.id_animal
WHERE (animal.lft BETWEEN parent.lft AND parent.rgt)
GROUP BY animal.id_animal;

but this error occurs:

ERROR:  invalid reference to FROM-clause entry for table "animal"
LINE 3: LEFT JOIN animal_trans as trans on animal.id_animal = ...
                                             ^
HINT:  There is an entry for table "animal", but it cannot be referenced from this part of the query.

Upvotes: 0

Views: 90

Answers (2)

Juan Ruiz de Castilla
Juan Ruiz de Castilla

Reputation: 974

Try this:

SELECT 
A.id_animal,A.id_parent,A.lft,A.rgt,
(SELECT count(*) 
FROM animal A2 
WHERE A.lft BETWEEN A2.lft AND A2.rgt)-1
as depth, 
B.name, 
B.descri
FROM animal A, animal_lang B
WHERE A.id_animal = B.id_animal
AND B.code = 'eng'

It look like your "result should look" example.

Upvotes: 1

j_random_hacker
j_random_hacker

Reputation: 51226

WHERE (animal.lft BETWEEN animal.lft AND animal.rgt)

Assuming that animal.rgt >= animal.lft, this clause will always be true. You almost certainly want something else before the BETWEEN.

Upvotes: 0

Related Questions