Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL - table name specified more than once

Posted on 2014-09-23
8
Medium Priority
?
465 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 1000 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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 1000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

718 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