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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
"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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.