Solved

SQL - table name specified more than once

Posted on 2014-09-23
8
408 Views
Last Modified: 2014-09-23
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
Comment
Question by:szadroga
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40339286
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
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 40339293
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40339334
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:szadroga
ID: 40339384
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
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 250 total points
ID: 40339393
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
 

Author Comment

by:szadroga
ID: 40339407
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40339411
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40339441
>>"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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question