CODEWITHSUNDEEP
mysqlsql
TyKitsune
TyKitsune

Reputation: 73

Finding alternatives to SQL Queries

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

Upvotes: 1

Views: 56

Answers (1)

Alex
Alex

Reputation: 17289

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

Upvotes: 1

Related Questions