I'm taking this conversation public, where maybe someone will have mercy on us...
SELECT PEOPLE."name" FROM PEOPLE INNER JOIN CAREER ON (CAREER."pid" = PEOPLE."pid")
WHERE ("ctype" = Y) AND ("mid" = X)
I can add CAREER."role" to get the role as well, but I don't see how to number the rows (i.e., so the end result can be something like 1. [actor] - [role]). We need to do this because MC can't handle ordered lists.
Select
PEOPLE."name",
CAREER."role"
From
PEOPLE Inner Join
CAREER On (CAREER."pid" = PEOPLE."pid")
Where
CAREER."ctype" = 0 And
CAREER."mid" = 111
There is no easy way of numbering result rows in Firebird.
I do not really understand:
-first one column only needed to be retrieved, now 3 columns are needed???
-normally there is no need of numbering rows as this task is usually accomplished by the database client library and/or programmatically while analyzing results
Sorry—I'd didn't mean to confuse the matter with my very first attempt at a SQL statement. And this is for raldo's plugin, so I don't know for sure exactly what is required. But to accommodate credits in regular alphabetically-sorted list fields in MC's flat database, I believe we need to import two fields. Using Actors as an example, that would be [name] in
Actors and "1. [actor] - [role]" in
Actor Credits. Then Actors can be used to search and filter movies by actor, and
Actor Credits can be used to properly display credits in a movie information context. I realized my query would produce three columns when one is required. I assumed Raldo would know how to concatenate them.
Judging from a
screenshot raldo provided, it appears he is using SQL statements directly in a field mapping. That implies to me there is no opportunity to do anything programmatically. In researching this, I see there is a
technique for
ranking results within a query, but we don't seem to have anything to rank. Can the ordering of the credits somehow be included in the query, or not—because it's just an index and not a field?
Is there a way to concatenate fields in a query, or is that also something normally done programmatically?In Firebird SQL, concatenation is denoted by a "||", so now we have...
Select
PEOPLE."name" || ' - ' || CAREER."role"
From
PEOPLE Inner Join
CAREER On (CAREER."pid" = PEOPLE."pid")
Where
CAREER."ctype" = 0 And
CAREER."mid" = X
