Solved

SQL - table name specified more than once

Posted on 2014-09-23
8
428 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 66

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 49

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
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 

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 49

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

626 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