Solved

join inside derived table instead of outside

Posted on 2011-02-16
13
588 Views
Last Modified: 2012-05-11
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

Open in new window


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

Open in new window


thanks
0
Comment
Question by:gagaliya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
13 Comments
 
LVL 1

Author Comment

by:gagaliya
ID: 34910200
note:  i know you can just join all the tables together in 1 big join and forget about derived table, but the sql will be dynamically created with many tables and columns to join and select, i dont want to do a group by on all of those tables/columns.
0
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 34910297
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
0
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 34910310
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
0
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 34910341
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?
0
 
LVL 1

Author Comment

by:gagaliya
ID: 34910589
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
0
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 34910948
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...

0
 
LVL 41

Expert Comment

by:Sharath
ID: 34910987
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

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 34911007
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

Open in new window

0
 
LVL 1

Author Comment

by:gagaliya
ID: 34911516
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
0
 
LVL 41

Accepted Solution

by:
Sharath earned 450 total points
ID: 34912440
>> 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.

Not possible. The alternative is either JOIN as mentioned above or co-related sub-query. Do you have more records in table2 than table1?
0
 
LVL 55

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 50 total points
ID: 34912985
probably this is the best method:

select *
from
(select * from table1 where some_filters_here group by ... having ...) a
left|inner join
(select * from table1 where some_other_filters_here group by ... having ...) b
on a.id=b.id
where ... some_more_filters_after_join
order by .. some_columns_after_join
0
 
LVL 1

Author Closing Comment

by:gagaliya
ID: 34930016
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
0
 
LVL 1

Author Comment

by:gagaliya
ID: 34930036
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    
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft is moving in-place eDiscovery & hold from ECP to EOP console under Content Search in Search and Investigation Options.  In this post, I will be showing you how to export emails to a PST file using the Content Search Options.
Article by: Justin
In light of the WannaCry ransomware attack that affected millions of Windows machines, you might wonder if your Mac needs protecting. Yes, it does and here is how to do it.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question