MySQL Database Tutorial – 21 – Another Subquery Example




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

Original source


25 responses to “MySQL Database Tutorial – 21 – Another Subquery Example”

  1. 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

  2. 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)

  3. 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?

  4. 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?

  5. 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%’

  6. 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

Leave a Reply