MySQL Forums
Forum List  »  Newbie

SELECT, DISTINCT, and JOIN
Posted by: Peter Pham
Date: August 28, 2013 02:37PM

Table "myInfo" has columns:
1. id (PK)
2. key
3. value
4. metaId

Table "meta" has columns:
1. id (PK)
2. metaName
3. metaValue
4. instanceNum

metaId in the "myInfo" table correlates to a instanceNum in the "meta" table. The value of the "value" column changes sometimes over different rows with the metaId. Think of the metaId as a link to a timestamp value in the "meta" table("timestamp" and its value would go into the metaName and metaValue columns respectively).

I want to select the distinct values of the 'value' column in "myInfo". So far I have:
SELECT DISTINCT mi.key, mi.value FROM myInfo as mi JOIN metadata as meta
WHERE mi.metaId=meta.instanceNum AND meta.key = 'timestamp' AND mi.value='maxWeight';

But I ALSO want the timestamps associated with those values. So I want the output to look something like:

key value timestamp
maxWeight 10 tons 15:00:05 2011-01-01
maxWeight 5 tons 08:00:07 2011-10-12
maxWeight 25 tons 13:05:09 2013-08-01

I can't place timestamp as one of the columns in my SELECT because then it will return duplicate mi.attrValue values too since the timestamp makes every row unique. I tried putting the DISTINCT keyword behind only mi.attrValue but I got a MySQL error.



Edited 2 time(s). Last edit at 08/28/2013 03:45PM by Peter Pham.

Options: ReplyQuote


Subject
Written By
Posted
SELECT, DISTINCT, and JOIN
August 28, 2013 02:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.