Problems with a query

Question

Problems with a query

Navigation

#1 by (1 votes)

1

I have some problems with a Query that I can not get out, the structure of the tables is as follows:

child

  child_id ----->CLAVE PRIMARIA
  child_name 
  birth_date 
  gender 
  address 
  city

letter

  letter_id --->Clave primaria
  child_id ---->Clave ajena que referencia a la tabla child,child_id
  arrival_date 
  number_toys

wished_toy

  letter_id -->Clave primaria a su vez clave ajena con letter_id de letter
  toy_id ---->Clave primaria a su ves clave ajena con toy_id de toy

toy

toy_id--->Clave primaria 
toy_name 
price 
toy_type
manufacturer

Well in the query I am asked to obtain the letters received where at least 3 toys with a price lower than 10 euros have been ordered, the id_letter, child_name, birth_date and number of toys ordered in the number_toys letter

Well, the first part of the query I have it clear, first I draw the letters of the children who have asked for at least 3 games and I do it in the following way

select l.letter_id,l.number_toys,h.child_name,h.birth_date
from letter l natural join child h
where l.number_toys >='3';

Well, I do not know how to continue with this query, how to relate with more joins to get to the toy table that is where the price of the toys is stored and tell them that the price must be less than 10.

sql postgresql

asked by Roman345 29.10.2017 в 21:22

source

1 answer

How to fix: 'Creating default object from empty value' in Laravel? [closed] How can I simplify these conditions, is there any way?

score 1 · Accepted Answer

What you need to do is a join to wished_toy and toy that limits only toys that cost less than 10 euros.

Then group by cards and use the having count(*) >= 3 clause to limit the result to those that include 3 or more toys that satisfy the filter described above.

select l.letter_id,
       c.child_name,
       c.birth_date,
       l.number_toys
  from letter l
  join child c
    on c.child_id = l.child_id
  join wished_toy wt
    on wt.letter_id = l.letter_id
  join toy t
    on t.toy_id = wt.toy_id
   and price < 10
 group by l.letter_id, 
          c.child_name,
          c.birth_date,
          l.number_toys
 having count(*) >= 3