• Monday , 24 September 2018

MySQL Database Tutorial – 21 – Another Subquery Example

Code Canyon



Facebook – https://www.facebook.com/TheNewBoston-464114846956315/ GitHub – https://github.com/buckyroberts Google+ …

Original source

3d Ocean

Related Posts

25 Comments

  1. freestyle ice
    September 14, 2018 at 19:16

    2 ways I can think of to go about this w/out using subqueries:
    SELECT name, cost FROM items WHERE name REGEXP ('frogs') ORDER BY cost LIMIT 1
    SELECT name, MIN(cost) FROM items WHERE name REGEXP ('frogs')

  2. Md. Nasir Uddin Protul
    September 14, 2018 at 19:16

    You can do it like I did below and I think it's much easier to understand:

    SELECT seller_id,name,cost FROM items WHERE cost = (SELECT MIN(cost) FROM items WHERE name LIKE'%frogs%')

  3. Aimopotis 80
    September 14, 2018 at 19:16

    I can see Leo and I am chasing him down the rabbit hole

  4. Nero Adam
    September 14, 2018 at 19:16

    What bucky did was a lucky shot… cuz there is no connection between name and cost, so in another table it would give you a name and a cost which are not connected together in the same raw, here is how you do it…
    SELECT name , cost FROM items WHERE name LIKE '%baby%' AND cost = (select min(cost) from items)
    you're welcome

  5. Evgeni Kuzmov
    September 14, 2018 at 19:16

    is there a difference btw tables and lists in SQL at all?

  6. 24DeepSky
    September 14, 2018 at 19:16

    bad example. the subquery is reduntant. plus subqueries are not useless. they are usefull because the query will still be working even after the tables/databse are updated (e.g. new users sell this)

  7. David Park
    September 14, 2018 at 19:16

    Quite the same example as the first one in video 20. But thanks for the effort.

  8. Science is Awesome!!
    September 14, 2018 at 19:16

    his example seems redundant to me so i can understand all the comments below that this is a bad example. of using sub-queries. you already know that you want boxes of frogs and so using a sub-query is redundant here.

  9. Bhav Gill
    September 14, 2018 at 19:16

    a very bad example, sub-query isn't even required in this example. NOT HELPFUL

  10. Flame2057
    September 14, 2018 at 19:16

    Why does this give me 'shampoing'?

    SELECT name, MIN(cost) FROM items WHERE seller_id IN(

    SELECT seller_id FROM items WHERE name LIKE '%boxes of frogs'

    )

  11. Ammar Basil
    September 14, 2018 at 19:16

    Cant we just say
    SELECT seller_id, cost FROM items WHERE name LIKE (%boxes of frogs) AND cost=MIN (COST);

  12. Ishaan Arora
    September 14, 2018 at 19:16

    Here is a simple version of the complex query shown in the video 🙂

    SELECT min(cost),seller_id,name from (SELECT * from items where name regexp("frog")) AS mincost

  13. bacho kamkamidze
    September 14, 2018 at 19:16

    someone help )) i wrote exactly same code but for name 'women'

    SELECT name, MIN(cost)
    FROM items
    WHERE name LIKE 'women%'
    AND seller_id IN
    (SELECT seller_id FROM items WHERE name LIKE 'women%')

    and the answer was

    name MIN(cost)
    women perfum 17.549999237060547

    but actually the price of women perfum is 110.9
    please can u explane why i received this answer?

  14. Matthew Ruddock
    September 14, 2018 at 19:16

    When I replace MIN with MAX it gives me the right MAX cost however it still gives me the name "3 boxes of frogs" which should be 7 boxes of frogs. How can this be corrected ?

  15. Clark Liu
    September 14, 2018 at 19:16

    OK, it is not just me who is thinking the subquery is not needed.

  16. Zeta
    September 14, 2018 at 19:16

    Gonna watch Inception tonight

  17. BrUSomania
    September 14, 2018 at 19:16

    Totally not an ad for Inception, haha

  18. George Qiao
    September 14, 2018 at 19:16

    why am I getting 10.75 as MIN(cost) when i use code 
    SELECT name, MIN(cost) FROM items WHERE seller_id IN(68,6,18)

  19. Fostiné Samuel
    September 14, 2018 at 19:16

    Do we really have to use the subquery for this? This query will give the same result

    SELECT name,MIN(cost)
    FROM items
    WHERE name LIKE '% boxes of frogs%'

    When we type WHERE name LIKE '% boxes of frogs%', it retrieves (68, 8, 18),  then the MIN will retrieve the min cost among the list. Having a subquery to retrieve the list is kind of a repetitive step, Am I wrong?

  20. Jan Koci
    September 14, 2018 at 19:16

    WHAT IF they are chocolate frogs? You know, there is even Harry Potter among the customers 😉

  21. Proper Sag
    September 14, 2018 at 19:16

    will this one work fine?

    Select cust_name
    From Customers
    Where cust_id =

    Select Distinct cust_id
    From Items
    Where item_desc like ‘%frogs%’ And item_Price =

    Select Min(item_price)
    From Items
    Where item_desc like ‘%frogs%’

  22. ruidargul
    September 14, 2018 at 19:16

    select name , min(cost) from items where name like "%boxes%";
    with this query u will get the same result no need to nest the query..
     

  23. Cherry Pauper
    September 14, 2018 at 19:16

    My query and subqueries work on their own, but when I combine them, I get an error ("#1241 – Operand should contain 1 column(s)"). What does this mean?

  24. David Woodard
    September 14, 2018 at 19:16

    I have a database with numbers like 00-00-00-00-00(00) about 104 of them
    I would like to find the number picked the most only in the main part of the number
    not in (00)
    then I would like to get a count of how many times all the other numbers or picked with that number like
    if the number 25 in the most picked and 15 was picked with it how many time was 15 picked with the number 25 like

    10-15-25-48-49(28) 15 would be (1)
    15-22-25-40-50(14) 15 would be (2)
    01-06-10-15-25(11) 15 would be (3)

    in result I need the most picked number and
    and each number picked with it total times
    like:
    Most_Picked: 25
    15=3
    08=2
    12=15
    06=1

  25. nur yati
    September 14, 2018 at 19:16

    Great explanation but does this subquery only work for the same table? if i want to subquery another different table, is it possible?

Leave A Comment

You must be logged in to post a comment.