On 2008-02-23 08:21, Kynn Jones wrote:
...
3. Why not write:
CREATE VIEW txt AS
SELECT a1.word AS word1, a1.type AS type1, a2.word AS word2, a2.type AS type2
FROM T a1 [LEFT] JOIN T a2 USING( zipk ); -- Use "LEFT" if appropriate
SELECT word1, word1
FROM S JOIN txt ON word = word1
WHERE type1 = <int1> AND type2 = <int2>;
This is would indeed produce the same results as Q1, but this approach would require defining about 10,000 views, one for each possible pair of int1 and int2
Why 10,000 views??? What's wrong with the ONE view above? You DON'T want to be defining VIEWs based on actual tables VALUES; leave that to the SELECT. For that matter, what's wrong with the final SELECT I listed (below)?
SELECT
a1.word AS word1, a2.word AS word2
FROM S JOIN T a1 USING( word )
LEFT JOIN T a2 USING( zipk ) WHERE a1.type = <int1> AND a2.type = <int2>; -- Dean
--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.