CODEWITHSUNDEEP
postgresql
Iren Patel
Iren Patel

Reputation: 757

how can one table name used more than once in query of postgresql

I create view in the postgresql that for write query but error message display table name used more than once. How to solve this problem?

Query

    SELECT 
        tbcitizen.firstname || '-' || tbcitizen.middlename || '-' || tbcitizen.familyname as firstname,
        tbcitizen.dateofbirth,
        tbcity.cityname, 
        tbcontact.contactdetails, 
        tbcitizen.citizenidp

    FROM
        public.tbcitizen,
        public.tbaddress, 
        public.tbcity, 
        public.tbcontact

    INNER JOIN  
        tbcontact ON tbcitizen.citizenidp = tbcontact.referenceidf AND tbcontact.referencetypeidf = 1 AND 
tbcontact.isprimery = 1 INNER JOIN

            tbaddress ON tbcitizen.citizenidf = tbaddress.referenceidf AND tbaddress.referencetypeidf = 1 AND 
tbaddress.isprimery = 1 INNER JOIN

            tbcity ON tbaddress.cityidf = tbcity.cityidp
    WHERE 

      tbaddress.referencetypeidf = tbcitizen.citizenidp AND
      tbaddress.referenceidf = tbcitizen.citizenidp AND
      tbaddress.cityidf = tbcity.cityidp




 Error = table name "tbcontact" specified more than once

Thanks

Upvotes: 0

Views: 115

Answers (1)

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

As the error states that - your table tbcontact used 2 times as the source table. So it creates ambiguity for the postgres database engine. So to resolve this issue you have to use table alias with different name.

Hope it'll help you.

Upvotes: 1

Related Questions