CODEWITHSUNDEEP
mysqlsubquerytemp-tables
alex
alex

Reputation: 113

Mysql, combining and querying results with sub queries or temp tables

I am running into some trouble with the following circumstances: I have a query that creates two temp tables, and the following select to join them together--

    SELECT * FROM result 
    INNER JOIN result2 ON result2.packetDetailsId = result.packetDetailsId

I am then trying to create another column from concatenating a few of the resulting fields and then use that to reference/query against another table. Is there a way to accomplish this in one query? Should I get away from the temp tables?

Thank you again in advance.

update: If I try to alias the combination of the two temp tables I get an error message stating [Err] 1060 - Duplicate column name 'packetDetailsId'

    select * from (
    SELECT * FROM result 
    INNER JOIN result2 ON result2.packetDetailsId = result.packetDetailsId) as myalias

Another Update: I almost have it working as one query but I get the result "(BLOB)" in the column I concoctenated:

       select packet_details.packetDetailsId,products.productId,Credit,AccountNum,OrderStat,          CONCAT(products.productId,Credit,'_',OrderStat) as consol from (
    select packetDetailsId, GROUP_CONCAT(Credit) AS Credit, GROUP_CONCAT(AccountNum) AS AccountNum, GROUP_CONCAT(OrderStat) AS OrderStat FROM
 ( SELECT pd_extrafields.packetDetailsId,
CASE WHEN pd_extrafields.ex_title LIKE ('%Credit%') 
THEN pd_extrafields.ex_value ELSE NULL END as Credit,
CASE WHEN pd_extrafields.ex_title LIKE ('%Account%') 
THEN pd_extrafields.ex_value ELSE NULL END as AccountNum,
CASE WHEN pd_extrafields.ex_title LIKE ('%Existing%') 
THEN pd_extrafields.ex_value ELSE NULL END as OrderStat
FROM pd_extrafields  )AS TempTab GROUP BY packetDetailsId ) as alias2

    INNER JOIN packet_details ON alias2.packetDetailsId = packet_details.packetDetailsId

INNER JOIN sales ON packet_details.packetDetailsId = sales.packetDetailsId

INNER JOIN sold_products ON sales.saleId = sold_products.saleId INNER JOIN products ON sold_products.productId = products.productId

Upvotes: 1

Views: 2009

Answers (2)

Barranka
Barranka

Reputation: 21047

If I understand correctly, you already have the temporary tables created and you need to "concatenate" the results, using from ... inner join ...

The only possible restriction you may have is that you can only reference your temporary tables once in your from clause; besides that, there are no other restrictions (I frequently use temporary tables as intermediate steps in the creation of my final result).

Tips

Let's say your temp tables are temp_result1 and temp_result2. Both tables have a field packedDetailsId, on which the join will be performed. Remember to create the appropriate indexes on each table; at the very least you need to index packedDetailsId on both tables:

alter table temp_result1
    add index PDI(packedDetailsId);
alter table temp_result2
    add index PDI(packedDetailsId);

Now, just execute a query with the desired join and concatenation. If concat returns BLOB, then cast the result as char (of course, I'm assuming you need a text string):

select r1.*, r2.*, cast(concat(r1.field1, ',', r2.field2) as char) as data_concat
from temp_result1 as r1
    inner join temp_result2 as r2 on r1.packedDetailsId = r2.packedDetailsId;

I see your problem is that GROUP_CONCAT is returning BLOB values... It's normal (MySQL doesn't know a priori how to return the values, so it returns binary data); just use the cast function.

Hope this helps you

Upvotes: 2

camelbrush
camelbrush

Reputation: 236

so, if the result2 and result are both temp tables, you will have to include the # if local temp table and ## if global temp table

so your statements should be :

SELECT * FROM #result 
INNER JOIN #result2 ON #result2.packetDetailsId = #result.packetDetailsId

My Bad. This is only applicable for MS SQL

Upvotes: 0

Related Questions