gagaliya
asked on
join inside derived table instead of outside
select table1.col1, a.CODE1, a.CODE2
from table1,
(
select table2.id
,CODE1=min( case when table2.code='CODE1' then table2.value end )
,CODE2 =min(case when tabel2.code='CODE2' then table2.value end)
from table2
group by table2.id
) a
where table1.id = 12345 and table1.id = a.id
This works except performance is extremely slow as it needs to create derived table by selecting the entire table2, only then does it join with table1 and filter.
How can i maintain this sql structure but do the filter inside the derived table. Something like below, but it's giving me syntax error saying table1.id not found.
select table1.col1, a.CODE1, a.CODE2
from table1,
(
select table2.id
,CODE1=min( case when table2.code='CODE1' then table2.value end )
,CODE2 =min(case when tabel2.code='CODE2' then table2.value end)
from table2
where table2.id = table1.id <--- doesnt work
group by table2.id
) a
where table1.id = 12345 and table1.id = a.id
thanks
what about this:
select b.col1, b.CODE1, b.CODE2
from (select * from table1 where id = 12345) a,
(
select id,
CODE1 =min(case when code='CODE1' then value end),
CODE2 =min(case when code='CODE2' then value end)
from table2 where id=12345
group by table2.id
) b
where a.id = b.id
select b.col1, b.CODE1, b.CODE2
from (select * from table1 where id = 12345) a,
(
select id,
CODE1 =min(case when code='CODE1' then value end),
CODE2 =min(case when code='CODE2' then value end)
from table2 where id=12345
group by table2.id
) b
where a.id = b.id
oops, a typo on first column alias
select a.col1, b.CODE1, b.CODE2
from (select * from table1 where id = 12345) a,
(
select id,
CODE1 =min(case when code='CODE1' then value end),
CODE2 =min(case when code='CODE2' then value end)
from table2 where id=12345
group by table2.id
) b
where a.id = b.id
select a.col1, b.CODE1, b.CODE2
from (select * from table1 where id = 12345) a,
(
select id,
CODE1 =min(case when code='CODE1' then value end),
CODE2 =min(case when code='CODE2' then value end)
from table2 where id=12345
group by table2.id
) b
where a.id = b.id
above sample does not look meaningful :) if we use "from table2 where id=12345" then we dont need to group by id
select a.col1, b.CODE1, b.CODE2
from (select * from table1 where id = 12345) a,
(
select id,
CODE1 =min(case when code='CODE1' then value end),
CODE2 =min(case when code='CODE2' then value end)
from table2 where id=12345
) b
where a.id = b.id
and I guess this gives you one row... no need for "where a.id = b.id"
is this a real query?
select a.col1, b.CODE1, b.CODE2
from (select * from table1 where id = 12345) a,
(
select id,
CODE1 =min(case when code='CODE1' then value end),
CODE2 =min(case when code='CODE2' then value end)
from table2 where id=12345
) b
where a.id = b.id
and I guess this gives you one row... no need for "where a.id = b.id"
is this a real query?
ASKER
hi unfortunately id=12345 was just an example filter. You could have 10000 id from some other filter criteria.
My question really is how to do table1.id = table2.id inside the derived table
My question really is how to do table1.id = table2.id inside the derived table
create a query
create view v_table2 as
select id,
CODE1 =min(case when code='CODE1' then value end),
CODE2 =min(case when code='CODE2' then value end)
from table2
then use
select a.col1, b.CODE1, b.CODE2
from table1 a left join v_table2 b on a.id=b.id
where a.id in (..some other filters..)
maybe this works better...
create view v_table2 as
select id,
CODE1 =min(case when code='CODE1' then value end),
CODE2 =min(case when code='CODE2' then value end)
from table2
then use
select a.col1, b.CODE1, b.CODE2
from table1 a left join v_table2 b on a.id=b.id
where a.id in (..some other filters..)
maybe this works better...
try adding an additional filter on code as you are interested in only two codes. Do you have any other codes?
select table1.col1, a.CODE1, a.CODE2
from table1,
(
select table2.id
,CODE1=min( case when table2.code='CODE1' then table2.value end )
,CODE2 =min(case when tabel2.code='CODE2' then table2.value end)
from table2
where table2.code in ('CODE1','CODE2')
group by table2.id
) a
where table1.id = 12345 and table1.id = a.id
or check this...
select t1.col1,
CODE1 = (select min(t2.value) from table2 as t2 where t2.code = 'CODE1' and t2.id = t1.id),
CODE2 = (select min(t2.value) from table2 as t2 where t2.code = 'CODE2' and t2.id = t1.id)
from table1 as t1
ASKER
Hi guys, this is not what i am asking. My question is how to join table1.id = table2.id when tabel2 is inside a derived table.
Assume table2 has 50 million unique table2.id, i need to filter down FIRST by joining table1.id = table2.id instead of creating a 50 million row derived table then filter. Filtering by table2.code or any other column is irrelevant for this discussion.
Thank you
Assume table2 has 50 million unique table2.id, i need to filter down FIRST by joining table1.id = table2.id instead of creating a 50 million row derived table then filter. Filtering by table2.code or any other column is irrelevant for this discussion.
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
FYI, doesnt look like it's possible. The most perforamnce efficient way is still below:
select table1.col1, table1.CODE1, table1.CODE2,
table2.id
,CODE1=min( case when table2.code='CODE1' then table2.value end )
,CODE2 =min(case when tabel2.code='CODE2' then table2.value end)
from table1, table2
where table1.id = 12345 and table1.id = table2.id
group by table1.col1, table1.CODE1, table1.CODE2,
table2.id
select table1.col1, table1.CODE1, table1.CODE2,
table2.id
,CODE1=min( case when table2.code='CODE1' then table2.value end )
,CODE2 =min(case when tabel2.code='CODE2' then table2.value end)
from table1, table2
where table1.id = 12345 and table1.id = table2.id
group by table1.col1, table1.CODE1, table1.CODE2,
table2.id
ASKER
CORRECTION:
select table1.col1, table2.id
,CODE1=min( case when table2.code='CODE1' then table2.value end )
,CODE2 =min(case when tabel2.code='CODE2' then table2.value end)
from table1, table2
where table1.id = 12345 and table1.id = table2.id
group by table1.col1, table2.id
select table1.col1, table2.id
,CODE1=min( case when table2.code='CODE1' then table2.value end )
,CODE2 =min(case when tabel2.code='CODE2' then table2.value end)
from table1, table2
where table1.id = 12345 and table1.id = table2.id
group by table1.col1, table2.id
ASKER