final part of a QUERY

Question

final part of a QUERY

Navigation

#1 by (1 votes)

2

I am finishing a query that I have been asked but I can not finish it, the structure of the tables is as follows:

child

CREATE TABLE CHILD(
child_id SMALLINT,
child_name VARCHAR(255) NOT NULL,
birth_date DATE NOT NULL,
gender VARCHAR(255) NOT NULL,
address VARCHAR(255),
city VARCHAR(255),
CONSTRAINT PK_CHILD PRIMARY KEY(child_id)

Sibling

CREATE TABLE SIBLING(
child_id1 SMALLINT,
child_id2 SMALLINT,
CONSTRAINT PK_SIBLING PRIMARY KEY(child_id1, child_id2),
CONSTRAINT CHILD1_FK FOREIGN KEY (child_id1) REFERENCES CHILD(child_id),
CONSTRAINT CHILD2_FK FOREIGN KEY (child_id2) REFERENCES CHILD(child_id)
);

letter

CREATE TABLE LETTER(
letter_id SMALLINT,
arrival_date DATE NOT NULL,
number_toys INTEGER NOT NULL,
delivery_mode VARCHAR(255) NOT NULL,
child_id SMALLINT,
CONSTRAINT PK_LETTER PRIMARY KEY(letter_id),
CONSTRAINT CHILD_FK FOREIGN KEY (child_id) REFERENCES CHILD(child_id)
);

wished_toy

 CREATE TABLE WISHED_TOY(
 letter_id SMALLINT,
 toy_id SMALLINT,
 CONSTRAINT PK_WISHED_TOY PRIMARY KEY(letter_id, toy_id),
 CONSTRAINT LETTER_FK FOREIGN KEY (letter_id) REFERENCES LETTER(letter_id)
 CONSTRAINT LETTER_FK FOREIGN KEY (toy_id) REFERENCES TOY(toy_id)
 );

toy

CREATE TABLE TOY(
toy_id SMALLINT,
toy_name VARCHAR (255) NOT NULL,
price DECIMAL NOT NULL,
toy_type VARCHAR(255) NOT NULL,
manufacturer VARCHAR(255) NOT NULL,
CONSTRAINT PK_TOY PRIMARY KEY(toy_id),
);

They ask me to make a query that contains the list of children (Child) who have requested a toy ordered also by one of their siblings (Sibling). Specifically, the identifier of the child child_id, the name child_name and its date is wanted. of birth birth_date.

I already have this part of the query, but something fails me, I do not know how to compare the toys that each child has requested ...

SELECT ch.child_id,ch.child_name,ch.birth_date
FROM child ch 
right outer join sibling si ON ch.child_id= SI.child_id1
left outer join letter l 
on ch.child_id=l.child_id
left outer join wished_toy wt on l.letter_id=wt.letter_id

sql postgresql postgresql-9.3

asked by Roman345 30.10.2017 в 20:08

source

1 answer

Print a column of a csv Error in MYSQL and PHP query

score 1 · Accepted Answer

I would need a test and data schema, but I think that you should be able to solve it (with the apologies of the case, I'm working on the air, and more apologies may be that the syntax in postgress is something different)

TABLA JUGUETES_PEDIDOS:
select toy_id, count(*) as cuenta from WISHED_TOY group by toy_id  (devuelve la cuenta de juguetes) 

TABLA CARTAS_JUGUETES:
SELECT letter_id, toy_id from WISHED_TOY where toy_id IN (SELECT * FROM JUGUETES_PEDIDOS WHERE cuenta > 1) (o se podia usar un having en la consulta anterior, devuelve las cartas que en las cuales se repiten juguetes)

TABLA NIÑOS_JUGUETES:
SELECT CHILD_ID, toy_id  
FROM 
   LETTER l inner join CARTAS_JUGUETES c on l.letter_id = c.letter_id 
(Encuentro todos los niños que pidieron juguetes, y que juguete)

TABLA CRUZADA_DE_JUGUETES:
SELECT N1.toy_id, N1.CHILD_ID child_id1, N2.CHILD_ID child_id2 FROM NIÑOS_JUGUETES N1 INNER JOIN NIÑOS_JUGUETES N2 ON N1.toy_id = N2.toy_id  
(CREA UNA TABLA PARECIDA A SIBLING.. OJO VA A REPETIR REGISTROS PARA EL NIÑO CONSIGO MISMO)

TABLA FINAL:
SELECT * FROM CRUZADA_DE_JUGUETES c INNER JOIN Sibling s ON c.child_id1 = s.child_id1 and c.child_id2 = s.child_id2

This should result in a table where children have asked for the same gift .. but without data is a nice theoretical solution ... that can be written in a single query, very carefully.