Michaela Ervin Michaela Ervin - 2 months ago 7
SQL Question

SQL query with multiple joins a subqueries

I understand that a sql query cannot dynamically add and remove columns.
Every site will have the same extra data.
So what I need to do is get all the data from 'z' and tack on certain fields from linked tables.

Table tt_transaction and Table tt_extendeddatatype are independent.
tt_extendeddatadetail has 2 foreign keys.
1) fk-> tt_transaction.id
2) fk -> tt_extendeddatatype

I need to pull each transaction with its extended data. Each site will have the same extended data names.

I think the following broken code demonstrates what I am trying to do much better than I can explain.

SELECT z.*, aaa.name, aaa.value, bbb.name, bbb.value FROM tt_transaction
LEFT JOIN
(
SELECT a.name, a.description, b.value, b.transaction_id AS text FROM tt_extendeddatatype AS a
INNER JOIN tt_extendeddatadetail AS b ON a.id = b.type_id
WHERE a.name = 'Number'
) AS aaa
ON aaa.transaction_id = z.id
LEFT JOIN
(
SELECT a.name, a.description, b.value, b.transaction_id AS text FROM tt_extendeddatatype AS a
INNER JOIN tt_extendeddatadetail AS b ON a.id = b.type_id
WHERE a.name = 'STRING'
) AS bbb
ON bbb.transaction_id = z.id


My current error is column aaa.transaction_id does not exist

gh9 gh9
Answer
SELECT z.*, aaa.name, aaa.value, bbb.name, bbb.value FROM tt_transaction
LEFT JOIN
(
    SELECT a.name, a.description, b.value, b.transaction_id AS transaction_id FROM tt_extendeddatatype AS a
    INNER JOIN tt_extendeddatadetail AS b ON a.id = b.type_id
    WHERE  a.name = 'Number'
) AS aaa 
ON aaa.transaction_id = z.id
LEFT JOIN
(
    SELECT a.name, a.description, b.value, b.transaction_id AS text FROM tt_extendeddatatype AS a
    INNER JOIN tt_extendeddatadetail AS b ON a.id = b.type_id
    WHERE  a.name = 'STRING'
) AS bbb
ON bbb.transaction_id = z.id

You named the column TEXT instead of transaction_id in your subquery.

Comments