Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

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

0
szadroga
Asked:
szadroga
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
Phillip BurtonCommented:
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
 
PortletPaulCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 BurtonCommented:
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 BurtonCommented:
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
 
PortletPaulCommented:
>>"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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now