Thanks for your reply
The SQL I wish to produce is:
'SELECT question.* FROM question, keyword as k2, keyword as k1 WHERE
((k1.keyword="this") AND (k2.keyword="that") AND
(k1.question=k2.question) AND (k1.question=question.id))'
That is, find all questions that have both the keyword 'this' and the
keyword 'that' (for example).
(I checked this query using executesql and it seems to work as
intended).
The problem I am solving:
I have a table of questions called 'question' tagged with keywords
stored in a second table 'keyword':
db.define_table('question',
# Fields not important to the problem
)
db.define_table('keyword',
Field('question', db.question),
Field('keyword')
)
I dont have deep knowledge of SQL, so its quite possible I am missing
a better way.
Paul
On Apr 9, 1:40 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> Inner joins in DAL do not support AS. Can you show an SQL example of
> how you would use it?
>
> Massimo
>
> On Apr 8, 10:08 pm, Paul Wray <paul.w...@det.nsw.edu.au> wrote:
>
> > Hello
>
> > I'm attempting a simple self join and having trouble with the aliases.
> > The slightly simplified query is:
>
> > k1 = db.qkeyword.with_alias('k1')
> > k1 = db.qkeyword.with_alias('k2')
> > print db( (k1.keyword == 'this')
> > & (k2.keyword == 'that')
> > & (k1.question == k2.question)
> > & (k1.question == db.question.id))._select(db.question.ALL)
>
> > The generated query looks correct except that k1 and k2 are
> > interpreted as existing table names, not aliases (ie no AS clause is
> > present).
>
> > The only examples of aliases I have seen have used the left join, so I
> > suspect I need to somehow use it here too?
>
> > Also, I'd love to read a more detailed description of the DAL if one
> > exists. The book provides examples, but does not contain sufficient
> > detail to give you a mental model of what is going on, and so
> > generalise from the examples.
--
To unsubscribe, reply using "remove me" as the subject.
