CODEWITHSUNDEEP
sqljdbcoracle10g
WithNeo
WithNeo

Reputation: 3

I want to select two different columns from two tables based on another column by SQL

I really get stuck with this question: I have two tables from an Oracle 10g XE database. I have been asked to give out the FName and LName of the fastest male and female athletes from an event. The eventID will be given out. It works like if someone is asking the event ID, the top male and female's FName and LName will be given out separately.

I should point out that each athlete will have a unique performance record. Thanks for reminding that!

Here are the two tables. I spent all the night on that.

ATHLETE 

╔═════════════════════════════════════════╦══╦══╗
║ athleteID* FName    LName    Sex   Club ║  ║  ║
╠═════════════════════════════════════════╬══╬══╣
║ 1034       Gabriel  Castillo M     5011 ║  ║  ║
║ 1094       Stewart  Mitchell M     5014 ║  ║  ║
║ 1161       Rickey   McDaniel M     5014 ║  ║  ║
║ 1285       Marilyn  Little   F          ║  ║  ║
║ 1328       Bernard  Lamb     M     5014 ║  ║  ║
║                                         ║  ║  ║
╚═════════════════════════════════════════╩══╩══╝

PARTICIPATION_IND

╔═════════════════════════════════════════════╦══╦══╗
║ athleteID* eventID* Performance_in _Minutes ║  ║  ║
╠═════════════════════════════════════════════╬══╬══╣
║ 1094       13       18                      ║  ║  ║
║ 1523       13       17                      ║  ║  ║
║ 1740       13                               ║  ║  ║
║ 1285       13       21                      ║  ║  ║
║ 1439       13       25                      ║  ║  ║
╚═════════════════════════════════════════════╩══╩══╝

Upvotes: 0

Views: 74

Answers (4)

Spaceman Spiff
Spaceman Spiff

Reputation: 934

SELECT * FROM (
    SELECT 
        a.FName
        , a.LName 
        FROM ATHLETE a
    JOIN PARTICIPATION_IND p 
    ON a.athleteID = p.athleteID
    WHERE a.Sex = 'M'
    AND p.eventID = 13
    ORDER BY p.Performance_in_Minutes
)
WHERE ROWNUM = 1
UNION
SELECT * FROM
    SELECT (
        a.FName
        , a.LName 
    FROM ATHLETE a
    JOIN PARTICIPATION_IND p 
    ON a.athleteID = p.athleteID
    WHERE a.Sex = 'F'
    AND p.eventID = 13
    ORDER BY p.Performance_in_Minutes
)
WHERE ROWNUM = 1

Upvotes: 1

Stephen
Stephen

Reputation: 1

SELECT a.FName, a.LName
FROM ATHLETE a
WHERE
a.athleteID IN (
  SELECT mp.athleteID
  FROM PARTICIPATION_IND mp
  INNER JOIN ATHLETE ma
    ON mp.athleteID = ma.athleteID
  WHERE 
    mp.eventID = 13 AND
    ma.Sex = 'M'
  HAVING MAX(mp.Performance_in_minutes)

  UNION

  SELECT fp.athleteID
  FROM PARTICIPATION_IND fp
  INNER JOIN ATHLETE fa
    ON fp.athleteID = fa.athleteID
  WHERE 
    fp.eventID = 13 AND
    fa.Sex = 'F'
  HAVING MAX(fp.Performance_in_minutes)
);

Upvotes: 0

spencer7593
spencer7593

Reputation: 108410

Here's one approach:

SELECT a.FName
     , a.LName
     , a.sex
     , p.eventID
     , p.performance_in_minutes
  FROM ( SELECT i.eventID, g.sex, MIN(i.performance_in_minutes) AS fastest_pim
           FROM athlete g
           JOIN participation_ind i
             ON i.athleteID = g.athleteID
          WHERE i.eventID IN (13)            -- supply eventID here
          GROUP BY i.eventID, g.sex
       ) f
  JOIN participation_ind p
    ON p.eventID = f.eventID
   AND p.performance_in_minutes = f.fastest_pim
  JOIN athlete a
    ON a.athleteID = p.athleteID
   AND a.sex = f.sex

The inline view aliased as f gets the fastest time for each eventID, at most one per "sex" from the athlete table.

We use a JOIN operation to extract the rows from participation_ind that have a matching eventID and performance_in_minutes, and we use another JOIN operation to get matching rows from athlete. (Note that we need to include join predicate on the "sex" column, so we don't inadvertently pull a row with a matching "minutes" value and a non-matching gender.)

NOTE: If there are two (or more) athletes of the same gender that have a matching "fastest time" for a particular event, the query will pull all those matching rows; the query doesn't distinguish a single "fastest" for each event, it distinguishes only the athletes that have the "fastest" time for a given event.

The "extra" columns in the SELECT list can be omitted/removed. Those aren't required; they are only included to aid in debugging.

Upvotes: 0

Adam
Adam

Reputation: 3914

here you go

Select A.FName, A.LName 
       from Athelete A
Join   Participation_IND P
       on A.AtheleteID= p.AtheleID 
And
P.Performance_in_Minutes >=(Select max (performace_in_minutes) from Participation_IND)

Thanks

Upvotes: 0

Related Questions