Comparing multiple rows and multiple columns

Question

faction_id   marker_id    cycle_id
14           1             1
14           2             1
12           2             2
12           3             2

I am trying to grab both entries with cycle_id 2, ignoring marker 2 in cycle 1, but still returning marker 1 in cycle 1. This is so I can display all the current data (most recent cycle) and only display markers that have not changed faction_id in previous cycles.

Right now, I am simply returning the whole table ordering it based on cycle_id and overwriting old cycle data in a PHP array when the data is returned, but this won't be an efficient way of handling it when the database get's larger.

My current select statement: (all of these columns are within tok_ownership)

SELECT tok_marker.id as id,
 tok_marker.name as name,
 tok_marker.lat as lat, 
 tok_marker.lng as lng, 
 tok_faction.colour as colour, 
 tok_ownership.war_conditions as war_conditions, 
 tok_ownership.improvements as improvements, 
 tok_ownership.poa as poa 
 FROM tok_ownership 
 JOIN tok_marker ON tok_marker.id=tok_ownership.marker_id 
 JOIN tok_faction ON tok_faction.term_id=tok_ownership.faction_id 
 ORDER BY tok_ownership.cycle_id ASC;

tok_ownership

1   marker_id   mediumint(8)
2   faction_id  mediumint(8)
3   cycle_id    mediumint(8)
4   victory_id  smallint(6)
5   war_conditions  varchar(64) 
6   improvements    varchar(64)
7   active_effects  text
8   poa tinyint(3)

tok_marker

1   id  mediumint(8)
2   name    varchar(64)
3   lat decimal(10,7)
4   lng decimal(10,7)

tok_faction

1   term_id smallint(6)
2   colour  char(6)

tok_cycle

1   id  int(10)
2   week    date

It is pretty hard to understand what you are trying to do. You should post your current code or at the very least an example of the output you expect with the conditions you are trying to match against. — Devon Bessemer, Nov 12 '14 at 05:11
@Devon Basically I am trying setup kind of a history function for this. So it displays everything from a specific cycle_id (in this case 2) and then only returns rows with marker_ids that are not listed as part of the specified cycle_id rows Updated with my current SELECT statement. — Scavello, Nov 12 '14 at 05:23
So cycle_id and marker_id are correlated somehow? I'm still not understanding 100% how they are related but I'll post an answer with some general advice on what I can pick up. — Devon Bessemer, Nov 12 '14 at 05:31
@Scavello do you mean to say, you want fetch the data for all marker id with higher cycle id ?? — Keshav jha, Nov 12 '14 at 05:32
@Devon Yea, it's for a custom google maps system I am building. So when the user adds a new cycle_id and makes changes to a specific marker on the map for that 'cycle', you'll be able to see all the current markers and their info, mean while the user could look back at previous cycles and see all the previous info up to that point (cycle_id). Is that starting to make more sense? — Scavello, Nov 12 '14 at 05:38
@Scavello. Yes, did adding the `WHERE` keyword I suggested after the joins do what you were expecting? — Devon Bessemer, Nov 12 '14 at 05:42
@Devon Not really, I need something more modular because this needs to be modular because it's going to returning data for what is essentially a time line. So I need to be able to change it easily. — Scavello, Nov 12 '14 at 06:31

Keshav jha · Answer 1 · 2014-11-12T06:15:48.777

1

i think this is the simple way to do this

select tok_marker.id,
 tok_marker.name as name,
 tok_marker.lat as lat, 
 tok_marker.lng as lng, 
 tok_faction.colour as colour, 
 tok_ownership.war_conditions as war_conditions, 
 tok_ownership.improvements as improvements, 
 tok_ownership.poa as poa,
 max(cycle_id)
 FROM tok_ownership 
 JOIN tok_marker ON tok_marker.id=tok_ownership.marker_id 
 JOIN tok_faction ON tok_faction.term_id=tok_ownership.faction_id 
 group tok_marker.id

edited Nov 12 '14 at 06:15

answered Nov 12 '14 at 05:47

Keshav jha

1,356
8
22

This is close to what I want, except it's pulling the row with marker_id 2 and cycle_id 1, instead it should be the row with marker_id 2 and cycle_id 2. marker_id 1 and 3 are being pulled as expected though. Am I making sense? My brain isn't functioning right after working on this most of the day :P – Scavello Nov 12 '14 at 06:12
same thing as the previous. – Scavello Nov 12 '14 at 06:20
then there must be some problem with join, can you provide the all three table stucture. – Keshav jha Nov 12 '14 at 06:24
cause if you create a test table with faction_id, marker_id, cycle_id and run this query select faction_id, marker_id, max(cycle_id ) from test group by marker_id it gives the desired output. – Keshav jha Nov 12 '14 at 06:27
Updated question with structure for all tables involved. – Scavello Nov 12 '14 at 06:37
1

can you please take a look at this link http://stackoverflow.com/questions/15402025/inner-join-table-with-respect-to-a-maximum-value – Keshav jha Nov 12 '14 at 06:48

score 0 · Answer 2 · answered Nov 12 '14 at 05:33

You are missing your conditions in your query. Conditions are generally provided using the WHERE keyword.

For instance:

WHERE tok_ownership.cycle_id = 2 AND tok_ownership.marker_id != 2

This would select the rows that have cycle_id equal to 2 and marker_id not equal to 2.

Scavello · Answer 3 · 2014-11-12T22:02:11.147

Coming at it today with a fresh brain I managed to put it together myself.

SELECT * FROM
(SELECT tok_ownership.marker_id as marker,
term_data.name as owner,
tok_marker.name as name, 
tok_marker.lat as lat,
tok_marker.lng as lng,
tok_faction.colour as colour,
tok_ownership.war_conditions as war_conditions,
tok_ownership.improvements as improvements,
tok_ownership.poa as poa,
tok_ownership.cycle_id as cycle 
FROM tok_ownership 
JOIN term_data ON term_data.tid=tok_ownership.faction_id 
JOIN tok_marker ON tok_marker.id=tok_ownership.marker_id 
JOIN tok_faction ON tok_faction.term_id=tok_ownership.faction_id 
WHERE cycle_id <= 2 ORDER BY tok_ownership.cycle_id DESC, tok_marker.id ASC) cycles 
GROUP BY cycles.marker;

This returns marker 1 with cycle_id 1, marker 2 and 3 with cycle_id 2, but does not return marker 2 with cycle_id 1. And this is easily adjustable with variables from PHP. Thank you all for your help and attempts to understand my frazzled overworked brain yesterday!

UPDATE: I also worked out the following statement as well which does the same thing. Would this be better optimized than the first one?

SELECT term_data.name as owner,
tok_marker.id as marker, 
tok_marker.name as name, 
tok_marker.lat as lat, 
tok_marker.lng as lng,  
tok_faction.colour as colour, 
tok_ownership.war_conditions as war_conditions,  
tok_ownership.improvements as improvements, 
tok_ownership.poa as poa,  
tok_ownership.cycle_id as cycle 
FROM tok_ownership 
JOIN term_data ON tok_ownership.faction_id=term_data.tid 
JOIN tok_marker ON tok_marker.id=tok_ownership.marker_id 
JOIN tok_faction ON tok_faction.term_id=tok_ownership.faction_id 
JOIN (SELECT id, cycle_id FROM 
 (SELECT tok_marker.id,
  tok_ownership.cycle_id 
  FROM tok_marker 
  JOIN tok_ownership ON tok_ownership.marker_id=tok_marker.id 
  WHERE tok_ownership.cycle_id <= 3 
  ORDER BY tok_ownership.cycle_id DESC, tok_marker.id DESC) ids 
 GROUP BY ids.id 
 ORDER BY ids.id DESC) marker_list ON tok_marker.id=marker_list.id 
 AND tok_ownership.cycle_id=marker_list.cycle_id;

Comparing multiple rows and multiple columns

3 Answers3