Use of 'Distinct' and 'Inner join' ...

Trying to query a postgress SQL DB of another application, having trouble with the distinct keyword in the select.
I know the field 'type' does exist in the table.  Why does it only let me query on 'id'

SELECT *  -- this select works.
-- Select distinct t3.type  -- this select does not work, produces an error, "t3.type does not exist."
-- Select distinct type   -- this select does not work, produces an error, "t3.type does not exist."
-- Select distinct t3.id  --  this select statement does work, what's the difference.
FROM table1 t1
inner join table2 t2 on t1.id = t2.id 
inner join table3 t3 on t2.id = t3.id
where
   t2.r_date > '2017-12-01' and
   t2.r_date < '2018-02-01'

limit 100

Open in new window

sidwelleAsked:
Who is Participating?
 
Ryan ChongConnect With a Mentor Commented:
"type" is a SQL Key Word in postgresql

https://www.postgresql.org/docs/7.3/static/sql-keywords-appendix.html

hence, you got to use:

Select distinct t3."type"

Open in new window


and try to avoid to use SQL Key Words for db objects definition
1
 
PortletPaulfreelancerCommented:
Without knowing your tables we can only guess.
I would try these to determine which of the tables does have the column "type" that I want

Select distinct t1.type FROM table1 t1;
Select distinct t2.type FROM table1 t2;
Select distinct t3.type FROM table1 t3;

Once you know that then use the correct reference in the full query:

Select distinct ??.type
FROM table1 t1
inner join table2 t2 on t1.id = t2.id
inner join table3 t3 on t2.id = t3.id
where
   t2.r_date > '2017-12-01' and
   t2.r_date < '2018-02-01'

---
by the way, do you need: > or equals '2017-12-01'

where
   t2.r_date >= '2017-12-01' and
   t2.r_date < '2018-02-01'
0
 
PortletPaulfreelancerCommented:
Ah..... good one Ryan
0
 
sidwelleAuthor Commented:
Ryan, you were right.  the name of the field needs be quoted if its also a reserved word.
Works now !

Thanks for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.