Joining of 2 SQL queries

[arachno]

I have managed to construct 2 separate SQL queries that basically gives me what I want. However; having them as a single query would be a great advantage as I could do an "ORDER BY" which would sort both results. As my SQL skills are getting a little rusty (to be moderate) I have been failing to combine them to 1 query.

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

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 '%'

This one will give me all the requests where the requesttitle contains 'test'; which in my case is requestid's: 1, 4, 24, 25, 27 and 31.

My second query

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 '%'

This one will give me all the requests which has an entry that contains 'test'; which in my case is requestid's: 1, 4, 5, 24, 25, 26, 27 and 30.

Trying to combine them I ended up with the following query:

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

This one actually sort of gives me what I want, except for some of the fields containing "null" for the requestid's 5, 26, 30 (missing from my first query) and 31 (missing from my second query). I suppose I have to do this with inner joins somehow, and loose the full outer one.

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

[r035198x]

If the queries return similar columns you could use UNION to combine their results.

[arachno]

Hey thanks!

UNION did the trick indeed!

I'll paste my final query in case someone else should be having a similar problem:

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 '%'
UNION
SELECT hprequests.requestid, hprequests.workstatus, hprequests.username, hprequests.requesttitle, hprequests.reqstatus, maxresults.regdate
FROM hprequests 
INNER JOIN (SELECT requestid, MAX(regdate) as regdate, MIN(entry) as entry FROM hpentries WHERE LOWER(entry) LIKE LOWER ('%test%') GROUP BY requestid) maxresults
ON hprequests.requestid = maxresults.requestid 
WHERE hprequests.reqstatus LIKE '%'
ORDER BY requestid

It gives me some duplicates, but I'll sort that out in my code.
Thanks again for the input!