This has perplexed me all morning. It looks to me that there is a bug in
the search action query. I'm using Witango 5.062 on Redhat 9 with
unixODBC-2.2.3-6. Dev Studio Mac 5.09.
I'm trying to build a set of rows that includes nulls for non-matching
records.
The search action produces this SQL:
[Search Action] [65] content
[Query] [66] SELECT
t1.id,t1.description,t1.template_id,t1.placement,t1.token,s2.id,s2.token_id
,s2.document_id,s2.org_id,s2.media_id FROM {oj vcs.tokens t1 LEFT OUTER
JOIN vcs.snippets s2 ON (t1.id=s2.token_id)} WHERE (s2.document_id=27)
ORDER BY 5 ASC
The Direct DBMS Query is:
[Direct DBMS Action] [67] DirectDBMS
[Query] [67] SELECT tokens.id, tokens.description, tokens.template_id,
tokens.placement, tokens.token, snippets.id, snippets.token_id,
snippets.document_id, snippets.org_id, snippets.media_id FROM tokens LEFT
JOIN snippets ON tokens.id=snippets.token_id AND snippets.document_id=27
ORDER BY tokens.token;
Here are the two tables (all columns in order) being joined
tokens
2 Sidebar copy 4 inline 1
3 Main copy 4 inline 2
4 Main image in right column 4 download 3
snippets
27 4 4 301 79
Here is the result produced by the search action, which specifes a left
outer join from tokens.id to snippets.token_id:
content
4 Main image in right column 4 download 3 4 4
27 301 79
Here is the content produced by the Direct DBMS action
2 Sidebar copy 4 inline 1
3 Main copy 4 inline 2
4 Main image in right column 4 download 3 4 4
27 301 79
Note that the latter properly produces a row for each token, with NULLS
in those rows for which there are no snippets.
Bill Conlon
To the Point
345 California Avenue Suite 2
Palo Alto, CA 94306
office: 650.327.2175
fax: 650.329.8335
mobile: 650.906.9929
e-mail: mailto:[EMAIL PROTECTED]
web: http://www.tothept.com
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf
