My tables:
1. hprequests(requestid{PK}, username{FK}, flagtype, reqstatus, workstatus, requesttitle, statuschangedby)
2. hpentries(entryid{PK}, username{FK}, requestid{FK}, entry, regdate)
3. hpusers(username{PK}, firstname, lastname, etc)
My first query
Expand|Select|Wrap|Line Numbers
- SELECT hprequests.requestid, hprequests.workstatus, hprequests.username, hprequests.requesttitle, hprequests.reqstatus, entries.regdate
- FROM hprequests
- INNER JOIN (SELECT MAX(regdate) AS latest, requestid FROM hpentries GROUP BY requestid) maxresults
- ON hprequests.requestid = maxresults.requestid
- INNER JOIN hpentries entries
- ON maxresults.latest = entries.regdate
- WHERE LOWER(hprequests.requesttitle) LIKE LOWER('%test%') AND hprequests.reqstatus LIKE '%'
My second query
Expand|Select|Wrap|Line Numbers
- SELECT hprequests.requestid, hprequests.workstatus, hprequests.username, hprequests.requesttitle, hprequests.reqstatus, hprequests.statuschangedby, singleentry.entry, singleentry.latest
- FROM hprequests
- INNER JOIN (SELECT requestid, MAX(regdate) as latest, MIN(entry) as entry FROM hpentries WHERE LOWER(entry) LIKE LOWER('%test%') GROUP BY requestid) singleentry
- ON hprequests.requestid = singleentry.requestid
- WHERE hprequests.reqstatus LIKE '%'
Trying to combine them I ended up with the following query:
Expand|Select|Wrap|Line Numbers
- SELECT hprequests.requestid, hprequests.username, hprequests.requesttitle, hprequests.reqstatus, hprequests.workstatus, singleentry.latest, singleentry.entry
- FROM hprequests
- INNER JOIN (SELECT requestid, requesttitle FROM hprequests WHERE LOWER(requesttitle) LIKE LOWER('%test%')) extratitles
- ON hprequests.requestid = extratitles.requestid
- FULL OUTER JOIN (SELECT requestid, MAX(regdate) as latest, MIN(entry) as entry
- FROM hpentries WHERE LOWER(entry) LIKE LOWER('%test%') GROUP BY requestid) singleentry
- ON hprequests.requestid = singleentry.requestid
- ORDER BY requestid
So, to sum it up: What I basically want to find is all requests where either the title or one of the entries contains a string ('test' in this case). It would be nice if it doesn't select any duplicates, but I suppose I could sort out duplicates code wise.
Hope this was somewhat understandable :)
-Johnb