Combining three row/table in postgresql

Question

I must connect three table. My code works great with two tables but doesn't work with three table. I use PostgreSQL 9.4.9 in Debian.

Code with 2 tables (WORKS):

SELECT 
manufactures.manufacturename
,products.productname
FROM products
INNER JOIN manufactures
ON manufactures.manufactureid=products.productmanufacturer;

Code with 3 tables (Doesn't works)

SELECT 
manufactures.manufacturename
,products.productname
,languages.languagename
FROM products
INNER JOIN manufactures
ON manufactures.manufactureid=products.productmanufacturer
INNER JOIN languages
ON languages.languagename=products.productlanguage;

Error message

baza_testowa=# SELECT 
baza_testowa-# manufactures.manufacturename
baza_testowa-# ,products.productname
baza_testowa-# ,languages.languagename
baza_testowa-# FROM products
baza_testowa-# INNER JOIN manufactures
baza_testowa-# ON manufactures.manufactureid=products.productmanufacturer
baza_testowa-# INNER JOIN languages
baza_testowa-# ON languages.languagename=products.productlanguage;
ERROR:  operator does not exist: text = integer
LINE 9: ON languages.languagename=products.productlanguage;
                                 ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

     Table "public.products"
       Column        |  Type   |                          Modifiers                           
---------------------+---------+--------------------------------------------------------------
 productid           | integer | not null default nextval('products_productid_seq'::regclass)
 productmanufacturer | integer | not null
 productname         | text    | not null
 productlanguage     | integer | 
Indexes:
    "products_pkey" PRIMARY KEY, btree (productid)

                                 Table "public.manufactures"
     Column      |  Type   |                              Modifiers                               
-----------------+---------+----------------------------------------------------------------------
 manufactureid   | integer | not null default nextval('manufactures_manufactureid_seq'::regclass)
 manufacturename | text    | not null
Indexes:
"manufactures_pkey" PRIMARY KEY, btree (manufactureid)

      Table "public.languages"
    Column    |  Type   |                           Modifiers                            
--------------+---------+----------------------------------------------------------------
 languageid   | integer | not null default nextval('languages_languageid_seq'::regclass)
 languagename | text    | not null
Indexes:
    "languages_pkey" PRIMARY KEY, btree (languageid)

The error message says that products.productlanguage is an integer. Is it really what you want that it is the same as languages.languagename? — mm759
– mm759, Commented Oct 10, 2016 at 19:55

Roberto · Accepted Answer · 2016-10-10 19:53:43Z

1

languages.languagename is text but products.productlanguage is integer, you can't compare them

answered Oct 10, 2016 at 19:53

Roberto

2,18525 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Walerian Sobczak · Accepted Answer · 2016-10-11 08:42:11Z

0

You cannot make JOINs on columns that are different types. You can use explicit type casting, if you really want products.productlanguage to be an integer and languages.languagename to be text. So in your case it should look like this:

SELECT manufactures.manufacturename, products.productname, languages.languagename
FROM products
INNER JOIN manufactures ON manufactures.manufactureid = products.productmanufacturer
INNER JOIN languages ON languages.languagename = products.productlanguage::text;

answered Oct 11, 2016 at 8:42

Walerian Sobczak

8592 gold badges10 silver badges23 bronze badges

Collectives™ on Stack Overflow

Combining three row/table in postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related