Accessing the value of one row relative to the value of another

[jonparker83]

I'm not really sure how to explain this which is probably why i'm having trouble finding the answer myslf...

Basically I have queried my mySQL table in the following way:

SELECT date, status FROM table WHERE id=12345678 (not a unique id but a foreign key)

..and have the following results:

---------------------
DATE ¦ STATUS ¦
---------------------
20050101 ¦ N ¦
20050102 ¦ N ¦
20050103 ¦ A ¦
20050104 ¦ A ¦
---------------------

What i need to do is access the value in the status column by specifiying the date.

I hope this makes sense and im not sure how to do this currently without a WHOLE load of queries.

Multi dimensional arrays possibly?

I really don't know where to start so if someone could point me in the right direction, I would really appreciate it!

Thanks :)

[emsaw]

You'll want to 'JOIN' those two tables together. I think this is what you are asking:

Table 'Assoc'
---------------------
ID ¦ DESC ¦
---------------------
12345678 ¦ Blah1 ¦
23456789 ¦ Blah2 ¦
34567890 ¦ Blah3 ¦
45678901 ¦ Blah4 ¦
---------------------

Table 'Stat'
---------------------
DATE ¦ STATUS ¦ FKAssocID
---------------------
20050101 ¦ N ¦ 12345678
20050102 ¦ N ¦ 23456789
20050103 ¦ A ¦ 34567890
20050104 ¦ A ¦ 45678901
---------------------

Your select would look something like this:
SELECT S.status
FROM Stat 'S'
JOIN Assoc 'A'
ON S.FKAssocID = A.ID
[edit]--WHERE A.ID=12345678[/edit]
WHERE S.Date = '20050101'

[edit]I changed the query based on the date.[/edit]

I just realized that based upon what you are asking, you don't even need a join. I'm a spaz.
Why can't you just select based upon the 'Date' field in the status table?
-Mark

[R e b r a n d t]

What i need to do is access the value in the status column by specifiying the date.

Why can't you just select based upon the 'Date' field in the status table?

Yea.. SELECT status FROM table WHERE date = '20050101';? (-;