Finding alternatives to SQL Queries

Question

Okay so I am trying, Quite unsuccessfully to find alternative methods to these queries to complete the same task, I know many of you will go, "why change them, they are fine".. well As a starting out SQL programmer I want to explore more avenues than I currently have ventured.

The First Query should Find a list of the A.names of all contained in table A, without duplication, which have the B.color 'Green'.

SELECT A.Name
FROM A
INNER JOIN B 
ON B.AID = A.AID 
WHERE B.Color = 'Green' 
GROUP BY A.AID

The Second Query should Find a list of the A.Names, B.Types and B.Colors for all elements in table B that have a B.Price less than 12.00.

SELECT A.Name, B.Types, B.Colors
FROM B 
INNER JOIN A 
ON A.AID = B.AID 
WHERE B.Price < '12.99' 
GROUP BY A.AID

I am using MySQL within PHPMyAdmin

~Thanks

-Edit - Table A contains, A.AID and A.Name, and Table B contains, B.ID, B.AID, B.Type, B.Color & B.Price

Sorry for any Confusion I may have caused

it seems unlikely that a.id = b.id is the correct join, or it could just be poorly named - could you post the table schema — pala_
– pala_, Commented Apr 23, 2015 at 23:41
MySQL forgives this abuse of the GROUP BY clause. Irritatingly, it even out-performs alternative (correct) solutions. But I would urge a beginner to forgo the use of GROUP BY except in aggregate queries. — Strawberry
– Strawberry, Commented Apr 23, 2015 at 23:44
That makes sense, So what kind of alternatives are we talking about here, as a beginner if you could show me some, or point me in the correct way? — TyKitsune
– TyKitsune, Commented Apr 23, 2015 at 23:52

Alex · Accepted Answer · 2015-04-23 23:56:54Z

1

FIRST QUERY

SELECT DISTINCT A.AID, A.Name
FROM A
INNER JOIN B 
ON B.AID = A.AID 
 AND B.Color = 'Green'

SECOND QUERY

SELECT A1.Name, B.Types, B.Colors
FROM B 
INNER JOIN (
  SELECT DISTINCT AID, Name
  FROM A 
 ) A1
ON A1.AID = B.AID 
WHERE B.Price < 12.99

answered Apr 23, 2015 at 23:56

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Finding alternatives to SQL Queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related