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 ;

Open in new window

szadrogaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
In lines 54 and 55 you are inner joining with public.project_instance_people
In lines 65 and 66 you are doing the same thing again.

There's the problem.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If you're going to use a table more than once in a query, you need to alias both of them, like..

<total air code>

SELECT pipb.id as parent_id, pipa.id as child_id
FROM public.project_instance_people pipa
   JOIN public.project_instance_people pipb ON pipa.ID = pipb.ParentID
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

szadrogaAuthor Commented:
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 ;

Open in new window

0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Line 58 says:

public.assets.id = public.pip1.asset_id)

Open in new window


Shouldn't that read

public.assets.id = pip1.asset_id)

Open in new window


(i.e. substitute "pip1" instead of "public.pip1")

Similarly, shouldn't line 69 read

closeout.package_instances.project_id = pip2.project_instance_id)

Open in new window

0
szadrogaAuthor Commented:
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?
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Because in line 55 you said:

public.project_instance_people pip1

Open in new window


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.
0
PortletPaulfreelancerCommented:
>>"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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.