CODEWITHSUNDEEP
mysqlsql
felipe.zkn
felipe.zkn

Reputation: 2060

What's wrong with subselect field from another subselect?

My tables are:

The user gives a district and I return a list of the activities that occur in that district. There can be more than one district by activity, so I will have to return it on a GROUP_CONCAT() later.

SELECT a.name, ap.name
FROM (
    SELECT apa_inner.activity_package_id AS activity_package_id
    FROM tbl_activity_package_address AS apa_inner
    WHERE district = 'Beja'
) AS apa
INNER JOIN (
    SELECT ap_inner.id, ap_inner.name, ap_inner.activity_id
    FROM tbl_activity_package_address AS apa_inner
    INNER JOIN tbl_activity_package AS ap_inner ON ap_inner.id = apa.activity_package_id
) AS ap ON apa.activity_package_id = ap.id
INNER JOIN tbl_activity AS a ON a.id = ap.activity_id

This gives me the error: "Unknown column 'apa.activity_package_id' in 'on' clause".

SELECT a.name, ap.name
FROM (
    SELECT id, activity_package_id
    FROM tbl_activity_package_address AS apa_inner
    WHERE district = 'Beja'
) AS apa
INNER JOIN (
    SELECT ap_inner.id AS id, ap_inner.name, ap_inner.activity_id
        FROM tbl_activity_package_address AS apa_inner
        INNER JOIN tbl_activity_package AS ap_inner ON ap_inner.id = apa_inner.activity_package_id
        WHERE apa_inner.id = apa.id
) AS ap ON apa.activity_package_id = ap.id
INNER JOIN tbl_activity AS a ON a.id = ap.activity_id

This gives me the error: "Unknown column 'apa.id' in where clause".

Upvotes: 1

Views: 62

Answers (2)

VMai
VMai

Reputation: 10336

Old answer

in your second subselect you use the alias names apa_inner and ap_inner so

SELECT a.name, ap.name
FROM (
    SELECT apa_inner.activity_package_id AS activity_package_id
    FROM tbl_activity_package_address AS apa_inner
    WHERE district = 'Beja'
) AS apa
INNER JOIN (
    SELECT ap_inner.id AS id
        FROM tbl_activity_package_address AS apa_inner
        INNER JOIN tbl_activity_package AS ap_inner ON ap_inner.id = apa_inner.activity_package_id
) AS ap ON apa.activity_package_id = ap.id
INNER JOIN tbl_activity AS a ON a.id = ap.activity_id

should fix the error. But do you really need those subselects. I'm sure you could simplify this query.

New answer

Well from

tbl_activity(id, name) tbl_activity_package(id, activity_id, name) tbl_activity_package_address(id, activity_package_id, district)

and the result should produce all activities that have any package with a package address in a certain district with a list of all districts that are covered by the activity.

This query will do it

SELECT
    a.name,
    GROUP_CONCAT(DISTINCT apa.district ORDER BY apa_district) AS distritos
FROM
    tbl_activity a
INNER JOIN
    tbl_activity_package ap
ON
    a.id = ap.activity_id
INNER JOIN
    tbl_activity_package_address apa
ON
    ap.id = apa.activity_package_id 
WHERE a.id IN (
    SELECT 
        ap_inner.activity_id
    FROM
        tbl_activity_package AS ap_inner
    INNER JOIN
        tbl_activity_package_id apa_inner
    ON
        ap_inner.id = apa_inner.activity_package_id 
    WHERE    
        apa_inner.district = 'Beja'
)       
GROUP BY 
    a.name

Upvotes: 1

echo_Me
echo_Me

Reputation: 37233

Try this

SELECT apa.activity_package_id AS activity_package_id,ap.id, ap.name, ap.activity_id
FROM tbl_activity AS a 
INNER JOIN tbl_activity_package AS ap ON a.id = ap.activity_id 
INNER JOIN tbl_activity_package_address AS apa ON apa.activity_package_id = ap.id
WHERE district = 'Beja'

Upvotes: 0

Related Questions