Good day. I'm trying to use ExecuteSQL to fetch descriptions from a parent table from two adjacent tables simultaneously and combine them into a single column. Not being exactly a SQL whiz, I'm having some issues. The setup isn't too complicated:
The "event" record has the IDs for the eventStatus and eventType records, as you can see. The eventStatus table has the description for the eventStatus. eventType has the description for the eventType. (Oddly enough.) What I'd like to do is have a concatenated "description" that consists of the description from eventStatus plus the description from eventType - but I need it for the event record, and I need to do it in a query. I'm able to do it for one or the other with a JOIN clause. Here's an example of what I'm using for the eventStatus query:
Let ( [
"SELECT es.description, '' || e.eventDate " &
"FROM event e JOIN eventStatus es ON e.eventStatusID = es.eventStatusID " &
"WHERE ( e.eventStatusID = 6347236899800001 OR ( e.eventStatusID = 6347236899800002 AND e.eventTypeID = 6347295476700007 ))"
ExecuteSQL ( sqlQuery ; ", " ; "¶" )
I'm sure there's a way to do it using subselects, but it's eluding me. Any help would be appreciated.