There was a problem creating the mysql query.

There are 2 tables: 1st - the name of the series, 2nd - the series. In the first table of the field "id", "title", in the second - "id", "serial_id", "title", "price".

Table 1:

id | title ----------- 1 | title1 2 | title2 3 | title3 4 | title4

Table 2:

 id | serial_id | title | price ---------------------------------- 1 | 1 | demo text | 1100 2 | 1 | demo text | 200 3 | 2 | demo text | 450 4 | 2 | demo text | 375

The site is sorted by price. Tell me, please, how to make a request so that when choosing a price, only those series are shown, the price of which series satisfies the price limit!

    3 answers 3

    If you want to leave the series, where at least 1 episode satisfies the requested price:

     select distinct t1.title, t2.serial_id from table1 t1 inner join table2 t2 on t1.id = t2.serial_id where t2.price < 400 /* тут задаём цену */ order by t1.popularity, t2.price /* desc по желанию */

    If you want to leave the series, where, although all the series satisfy the requested price:

     select distinct t1.title, t2.serial_id, t2.id from table1 t1 inner join (select serial_id, max(price) as max_price from table2 group by serial_id having max(price) < 1000 /* тут задаём цену */ ) t2 on t1.id = t2.serial_id
    • Why use Left Outer Join? By definition, there can be no records in the series table with a non-existent secondary key from the series table - IvanZakirov
    • @IvanZakirov working habit, corrected - Denis
    • @Denis Just the first option is required, thanks! Then another question, for example, sorting costs the price and popularity of the series. Popularity in our table "Series", the price in the table with the series. How to make a general sample? - Vyacheslav Fedorov
    • Add ORDER BY t2.price, t1.popularity to the end of the query. - Arnial
    • @ VyacheslavFyodorov corrected, Arnial correctly noticed - Denis
     $mysqli = new mysqli(/*данные для коннекта*/); $cost = 100; //максимальная цена $stmt = $mysqli->prepare("SELECT t1.title, t2.title, t2.price FROM table1 t1, table2 t2 WHERE t1.id=t2.serial_id AND t2.price < ?"); $stmt->bind_params("i", $cost); $stmt->execute();

    Well, and so on

       SELECT t1.id, t1.title FROM table1 t1, table2 t2 WHERE t1.id = t2.serial_id and t2.price between [min_price] and [max_price] GROUP by t1.id, t1.title

      Instead of BETWEEN, other conditions like <> etc. can be made.