James Sheridan
<[EMAIL PROTECTED]> wrote:
> The query should have been:
>
> SELECT Q.*
> FROM Query Q,
> UserQuery UQ
> WHERE Q.type = 'a' OR
> (Q.id = UQ.queryID AND
> UQ.userID = '1');
Well, it's the same thing: for each row in Q with Q.type = 'a', you will
get as many copies of this row as there are rows in UQ, since you don't
stipulate to any relationsips between such rows and UQ table, and so the
engine builds a cross-join.
> Basically, I want all queries of type "a" and all the ones that have
> a record in UserQuery with userID = 1 (queries shared to the user).
So why don't you say that in your query?
SELECT *
FROM Query Q
WHERE Q.type = 'a' OR
EXISTS (
select 1 from UserQuery UQ
where Q.id = UQ.queryID AND UQ.userID = '1'
);
-- or
SELECT *
FROM Query Q
WHERE Q.type = 'a' OR
Q.id IN (
select UQ.queryID from UserQuery UQ
where UQ.userID = '1'
);
Or else, use left join.
> Related addendum:
>
> In reading it appears that MySQL treats "," as a CROSS JOIN and
> implements it effectively as an INNER JOIN.
This makes no sense whatsoever. Cross join and inner join are two
entirely different things. How can you treat something as apples and
implement as oranges?
In all SQL engines I know of, comma is equivalent to cross join. It's
been this way since before "JOIN" was a keyword in SQL.
Besides, if you are right (which you are not), how exactly does MySQL
figure out which fields to do an inner join on? Say, in your example,
how would it know to do Q.id = UQ.queryID, and not Q.id = UQ.userID or
Q.creatorID = UQ.queryID ?
> a) Is this correct?
Absolutely not.
> and b) Is Sqlite acting the same or treating it as a true CROSS JOIN?
SQLite is acting the same _by_ treating comma as a CROSS JOIN.
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
