szadroga
asked on
SQL - table name specified more than once
i have the following query and when I try to run the full SELECT statement, I get an error message that reads table name "porject_instance_people specificed more than once. can someone help with the syntax or understanding
SELECT
public.assets.first_name || ' ' || public.assets.last_name AS team_member,
CASE
WHEN public.project_markets.market IS NULL
THEN 'Corporate'
ELSE public.project_markets.market
END,
events.event.name AS event_name,
CASE
WHEN events.event_log.event_id = 3
THEN MIN(events.event_log.timestamp)
END AS package_assignment,
CASE
WHEN events.event_log.event_id = 1
THEN MIN(events.event_log.timestamp)
END AS form_download,
CASE
WHEN events.event_log.event_id = 2
THEN MIN(events.event_log.timestamp)
END AS form_upload,
closeout.packages.name
FROM
events.event
INNER JOIN
events.event_log
ON
(
events.event.id = events.event_log.event_id)
INNER JOIN
public.assets_user_xref
ON
(
events.event_log.user_id = public.assets_user_xref.user_id)
INNER JOIN
public.assets
ON
(
public.assets_user_xref.assets_id = public.assets.id)
FULL OUTER JOIN
public.unitek_pcs
ON
(
public.assets.pc6 = public.unitek_pcs.pc6)
FULL OUTER JOIN
public.project_pc_market_xref
ON
(
public.unitek_pcs.id = public.project_pc_market_xref.pc_id)
FULL OUTER JOIN
public.project_markets
ON
(
public.project_pc_market_xref.market_id = public.project_markets.id)
INNER JOIN
public.project_instance_people
ON
(
public.assets.id = public.project_instance_people.asset_id),
closeout.packages
INNER JOIN
closeout.package_instances
ON
(
closeout.packages.id = closeout.package_instances.package_id)
INNER JOIN
public.project_instance_people
ON
(
closeout.package_instances.project_id = public.project_instance_people.project_instance_id)
WHERE
events.event_log.event_id IN (3,
2,
1)
GROUP BY public.assets.first_name, public.assets.last_name, public.project_markets.market, events.event.name, events.event_log.event_id, packages.name, closeout.packages.name ;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
LIne 59 is a problem also, here you are including the table closeout.packages
but without ANSI join syntax and it could actually be a Cartesian product that is being formed
but without ANSI join syntax and it could actually be a Cartesian product that is being formed
ASKER
I tried applying an alias to both tables, but still getting an error message. "invalid reference to FROM-clause entry for table pip1.
SELECT
public.assets.first_name || ' ' || public.assets.last_name AS team_member,
CASE
WHEN public.project_markets.market IS NULL
THEN 'Corporate'
ELSE public.project_markets.market
END,
events.event.name AS event_name,
CASE
WHEN events.event_log.event_id = 3
THEN MIN(events.event_log.timestamp)
END AS package_assignment,
CASE
WHEN events.event_log.event_id = 1
THEN MIN(events.event_log.timestamp)
END AS form_download,
CASE
WHEN events.event_log.event_id = 2
THEN MIN(events.event_log.timestamp)
END AS form_upload,
closeout.packages.name
FROM
events.event
INNER JOIN
events.event_log
ON
(
events.event.id = events.event_log.event_id)
INNER JOIN
public.assets_user_xref
ON
(
events.event_log.user_id = public.assets_user_xref.user_id)
INNER JOIN
public.assets
ON
(
public.assets_user_xref.assets_id = public.assets.id)
FULL OUTER JOIN
public.unitek_pcs
ON
(
public.assets.pc6 = public.unitek_pcs.pc6)
FULL OUTER JOIN
public.project_pc_market_xref
ON
(
public.unitek_pcs.id = public.project_pc_market_xref.pc_id)
FULL OUTER JOIN
public.project_markets
ON
(
public.project_pc_market_xref.market_id = public.project_markets.id)
INNER JOIN
public.project_instance_people pip1
ON
(
public.assets.id = public.pip1.asset_id),
closeout.packages
INNER JOIN
closeout.package_instances
ON
(
closeout.packages.id = closeout.package_instances.package_id)
INNER JOIN
public.project_instance_people pip2
ON
(
closeout.package_instances.project_id = public.pip2.project_instance_id)
WHERE
events.event_log.event_id IN (3,
2,
1)
GROUP BY public.assets.first_name, public.assets.last_name, public.project_markets.market, events.event.name, events.event_log.event_id, packages.name, closeout.packages.name ;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
they may have done it. Why does it matter if i have the schema name in there? public is the schema name, but i assume since i included the schema in the alias then repeating it will cause issues?
Also, when using a table twice in a query how come you have to alias both of them. seems like you should be able to use the "original" and then alias going forward on connections?
Also, when using a table twice in a query how come you have to alias both of them. seems like you should be able to use the "original" and then alias going forward on connections?
Because in line 55 you said:
So you say, "I no longer what it to be called public.something - I want it to be called pip1".
It's not aliased as public.pip1; it's aliased as pip1.
public.project_instance_people pip1
So you say, "I no longer what it to be called public.something - I want it to be called pip1".
It's not aliased as public.pip1; it's aliased as pip1.
>>"when using a table twice in a query how come you have to alias both of them. "
you do not have to alias both
you do have to alias at least one
it is conventional to alias both
you do not have to alias both
you do have to alias at least one
it is conventional to alias both
In lines 65 and 66 you are doing the same thing again.
There's the problem.