Hello-
Recently my host downgraded mySQL to version 3.23. Which messed up all my UNION statements, since 3.23 doesn't support the UNION statement.
Anywho, I've rewritten everything, but I am having trouble with one last bit. Here is my new select statement:
$select = "SELECT DISTINCT IFNULL(events.title, events_sec.title) AS title, IFNULL(events.eid, events_sec.eid) AS eid, IFNULL(events.entryDate, events_sec.entryDate) AS entryDate, IFNULL(events.year, events_sec.year) AS year
FROM _dummy AS D
LEFT JOIN events ON (D.num = 0 AND events.keywords LIKE $reg_ex OR events.title LIKE $reg_ex2)
LEFT JOIN events_sec ON (D.num = 1 AND events_sec.keywords LIKE $reg_ex3 OR events_sec.title LIKE $reg_ex4)
WHERE D.num < 2 AND IFNULL(events.eid, events_sec.eid) IS NOT NULL
ORDER BY entryDate DESC";
Here, two tables are joined (events and events_sec) into a dummy table, _dummy, and then sorted.
The events table fields are joined to dummy with num = 0
The events_sec table fields are joined to dummy with num = 1
This works fine, and I can access the results. But, is there a way to return the num in the _dummy field with the results so I can tell which table a particular result came from? I tried adding D.num in the select statement in the first line, but then I get double results of everything, one for num = 0 and one for num = 1 - no matter which table it came from.
Any ideas?
-Sledge
