CODEWITHSUNDEEP
mysqlsqljoinselect
Eric
Eric

Reputation: 175

Selecting multiple entries for the same attribute being equal to another

I want to modify my existing SQL query, but can't get it to work how I want. (This is in no way homework / exam related)

I want a query that gets all map ids, map names and reward points just like it does now, but gets the maps for all different run ids rather than only once whatever the map id. Now, if there is a mapid for a user in inf_times, it will no longer be displayed in the final list. I want a map to be displayed once for every runid IF the mapid is not in inf_times for that user.

Table Structure:

inf_maps: mapid, mapname
inf_simpleranks_maps: mapid, runid, rewardpoints
inf_times: uid, mapid, runid

select a.mapid, b.mapname, a.rewardpoints 
from (select r.mapid, rewardpoints 
      from inf_maps r, inf_simpleranks_maps srm 
      where r.mapid 
      not in (select mapid 
              from inf_times 
              where uid = %d 
              group by uid, mapid) 
      and r.mapid = srm.mapid 
      order by rewardpoints desc) a, 
inf_maps b 
where a.mapid = b.mapid;

EXAMPLE

inf_times
uid mapid runid   
1    4     1

inf_simpleranks_maps
mapid runid rewardpoints
4       1       10
4       2       12 

inf_maps
mapid mapname
4      mapmap

The query should return 

mapid mapname rewardpoints
4      mapmap     12

Instead, it now returns nothing.

Upvotes: 0

Views: 42

Answers (2)

GMB
GMB

Reputation: 222492

Your explanation is hard to follow. Based on your sample data though, the following query would produce the results that you expect:

select  m.*, r.rewardpoints
from inf_maps m
inner join inf_simpleranks_maps r on r.mapid = m.mapid
where not exists (
    select 1 from inf_times t where t.mapid = m.mapid and t.runid = r.runid
)

Upvotes: 1

zealous
zealous

Reputation: 7503

Try the following with not exists. here is the demo.

select
  im.mapid,
  im.mapname,
  ism.rewardpoints
from inf_maps im
join inf_simpleranks_maps ism
on im.mapid = ism.mapid
where not exists
(
  select
    mapid
  from inf_times it
  where ism.mapid = it.mapid
  and ism.runid = it.runid
)

Output:

| mapid | mapname | rewardpoints |
| ----- | ------- | ------------ |
| 4     | mapmap  | 12           |

Upvotes: 1

Related Questions