Facebook – https://www.facebook.com/TheNewBoston-464114846956315/ GitHub – https://github.com/buckyroberts Google+ …
Original source
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”
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')
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%')
I can see Leo and I am chasing him down the rabbit hole
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
is there a difference btw tables and lists in SQL at all?
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)
Quite the same example as the first one in video 20. But thanks for the effort.
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.
a very bad example, sub-query isn't even required in this example. NOT HELPFUL
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'
)
Cant we just say
SELECT seller_id, cost FROM items WHERE name LIKE (%boxes of frogs) AND cost=MIN (COST);
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
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?
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 ?
OK, it is not just me who is thinking the subquery is not needed.
Gonna watch Inception tonight
Totally not an ad for Inception, haha
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)
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?
WHAT IF they are chocolate frogs? You know, there is even Harry Potter among the customers 😉
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%’
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..
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?
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
Great explanation but does this subquery only work for the same table? if i want to subquery another different table, is it possible?