On Sun, Oct 17, 2004 at 03:30:32 -0400,
Mike Mascari [EMAIL PROTECTED] wrote:
I'm thinking that the WHERE clauses condition should read:
WHERE l1.p_pkey is not null OR l2.p_key is not null;
That seems to make more sense. I was puzzling about that condition myself.
If both keys where not
I would use 2 left joins and use the where condition to make sure one
of them is true, such as:
select big_table.* from
big_table left join little_table as l1 on big_table.y1=l1.y and
l1.x=10
left join little_table as l2 on big_table.y2=l2.y and l1.x=10
where l1.p_key is not null and l2.p_key is
Sim Zacks wrote:
I would use 2 left joins and use the where condition to make sure one
of them is true, such as:
select big_table.* from
big_table left join little_table as l1 on big_table.y1=l1.y and
l1.x=10
left join little_table as l2 on big_table.y2=l2.y and l1.x=10
where l1.p_key is not null
Mike Mascari wrote:
Sim Zacks wrote:
I would use 2 left joins and use the where condition to make sure one
of them is true, such as:
select big_table.* from
big_table left join little_table as l1 on big_table.y1=l1.y and
l1.x=10
left join little_table as l2 on big_table.y2=l2.y and l1.x=10
where
Mike,
You are probably correct, I was thinking in English, not SQL. That's
what happens when I bang code too early in the morning.
Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax
Am Samstag, 16. Oktober 2004 07:23 schrieb Mike Mascari:
Hello. I have a query like:
SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y IN (big_table.y1, big_table.y2);
I have indexes on both big_table.y1 and big_table.y2 and on
little_table.x and
If the problem is the sort, use UNION ALL.
As for the query restructuring, I don't know if there is a way of
restructuring the query to do it in a single query. You would be able
to contruct a query plan that would do it, something like:
- Nested Loop
- Append
- Index Scan on big_table.y1
Mike Mascari [EMAIL PROTECTED] writes:
SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y IN (big_table.y1, big_table.y2);
Is there any way to write the first query such that indexes will be used?
I'm afraid you're stuck with the UNION workaround.
Hello. I have a query like:
SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y IN (big_table.y1, big_table.y2);
I have indexes on both big_table.y1 and big_table.y2 and on
little_table.x and little_table.y. The result is a sequential scan of
big_table.