I'm creating a view, but I'm having a problem. What you see when you
"SELECT *" the view is different from what you would see if you just ran
the SQL that is used to create the view. Here's an example of what I
mean:
So first of all here's the SQL that gets me the data I want:
SELECT DISTINCT(applicanthistory.contact_id) as cid, (
SELECT statustype.statustypelabel
FROM statustype, applicanthistory
WHERE statustype.statustype_id = applicanthistory.statustype_id
AND applicanthistory.contact_id = cid
ORDER BY applicanthistory.statusdate DESC,
applicanthistory.statustype_id LIMIT 1
) as currentstatus
FROM applicanthistory
It returns this:
+-----+---------------+
| cid | currentstatus |
+-----+---------------+
| 1 | N |
| 2 | N |
| 3 | N |
| 4 | N |
| 5 | N |
| 6 | P |
| 7 | N |
| 8 | N |
| 9 | N |
| 10 | N |
+-----+---------------+
Here's the SQL used to create the view:
CREATE VIEW contactextension_view AS
SELECT DISTINCT(applicanthistory.contact_id) as cid, (
SELECT statustype.statustypelabel
FROM statustype, applicanthistory
WHERE statustype.statustype_id = applicanthistory.statustype_id
AND applicanthistory.contact_id = cid
ORDER BY applicanthistory.statusdate DESC,
applicanthistory.statustype_id LIMIT 1
) as currentstatus
FROM applicanthistory
When I do this: SELECT * FROM contactextension_view;
I see:
+-----+---------------+
| cid | currentstatus |
+-----+---------------+
| 1 | N |
| 2 | N |
| 3 | N |
| 4 | N |
| 5 | N |
| 6 | N |
| 7 | N |
| 8 | N |
| 9 | N |
| 10 | N |
+-----+---------------+
Notice cid = 6. The results from the first query (not the view) are
correct.
Why oh why would the view show something different from the sql that is
used to create it? I thought a view was more or less just an alias for a
query.
Thanks in advance for any help.
-Rob
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
