# SQL Select, Join, Where help **URL:** \ **Category:** Ignition\ **Created:** [October 10, 2020, 9:28pm UTC](https://forum.inductiveautomation.com/t/sql-select-join-where-help/39571 "2020-10-10T21:28:42Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\ **Post date:** [October 10, 2020, 9:28pm UTC](https://forum.inductiveautomation.com/t/sql-select-join-where-help/39571/1 "2020-10-10T21:28:42Z")
Hi all, I am pulling data from multiple tables to populate a Vision Table using a named query. This query worked until I started to add to it. When it failed, I put it back to the original and it did not work. SELECT binder\_notes.t\_stamp, usernames.username, binder\_notes.notes FROM binder\_notes JOIN usernames ON binder\_notes.user\_id = usernames.id WHERE binder\_notes.binder\_id = :binder\_id When I test it, I get this error message: GatewayException: org.postgresql.util.PSQLException: The column index is out of range: 2, number of columns: 1. caused by Exception: org.postgresql.util.PSQLException: The column index is out of range: 2, number of columns: 1. Ignition v8.0.16 (b2020082513) Java: Azul Systems, Inc. 11.0.7 Now I am confused as to why it no longer works event when testing in the named query. Also, I want to add to WHERE so that I get all the records with the selected binder\_id, topic\_id and page\_id. I think like this: WHERE binder\_notes.binder\_id = :binder\_id AND binder\_notes.topic\_id = :topic\_id AND binder\_notes.page\_id = :page\_id The parameters come from a selected value on a dropdown for each table. I would appreciate any help as basic as I am sure it is as I just don’t understand why it was working fine and then decided no to! Thanks, Steven ---
**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\ **Post date:** [October 10, 2020, 9:34pm UTC](https://forum.inductiveautomation.com/t/sql-select-join-where-help/39571/2 "2020-10-10T21:34:44Z")
Well, this is interesting… Just as i gave up I remembered that comments in a query a few days ago, made it not work. So I came back, deleted the commented lines from the query and it works again. Now I just have to figure out how to add my WHERE statement. Steven ---
**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\ **Post date:** [October 10, 2020, 9:44pm UTC](https://forum.inductiveautomation.com/t/sql-select-join-where-help/39571/3 "2020-10-10T21:44:39Z")
So this is the working query… SELECT binder\_notes.t\_stamp, usernames.username, binder\_notes.notes FROM binder\_notes JOIN usernames ON binder\_notes.user\_id = usernames.id WHERE binder\_notes.binder\_id = :binder\_id AND binder\_notes.topic\_id = :topic\_id AND binder\_notes.page\_id = :page\_id This is the second time I had a query not work with comments below the query. Could this be a bug? Anyway, thanks for reading!! 😀 😀 😀 Steven ---
**Author:** ![etlikmilos](https://avatars.discourse-cdn.com/v4/letter/e/fbc32d/32.png) [@etlikmilos](https://forum.inductiveautomation.com/u/etlikmilos)\ **Post date:** [October 10, 2020, 9:57pm UTC](https://forum.inductiveautomation.com/t/sql-select-join-where-help/39571/4 "2020-10-10T21:57:38Z")
> **[SQL Joins Using WHERE or ON | Intermediate SQL - Mode Analytics](https://mode.com/sql-tutorial/sql-joins-where-vs-on/)** > > This lesson of the SQL tutorial for data analysis covers the differences between filtering joined data using WHERE or ON. ON filters data before tables are joined; WHERE filters after. Maybe it will help you ---
**Author:** ![etlikmilos](https://avatars.discourse-cdn.com/v4/letter/e/fbc32d/32.png) [@etlikmilos](https://forum.inductiveautomation.com/u/etlikmilos)\ **Post date:** [October 10, 2020, 9:59pm UTC](https://forum.inductiveautomation.com/t/sql-select-join-where-help/39571/5 "2020-10-10T21:59:43Z")
I mean that : JOIN usernames ON binder\_notes.user\_id = usernames.id AND binder\_notes.binder\_id = :binder\_id AND binder\_notes.topic\_id = :topic\_id AND binder\_notes.page\_id = :page\_id ---
**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\ **Post date:** [October 10, 2020, 10:14pm UTC](https://forum.inductiveautomation.com/t/sql-select-join-where-help/39571/6 "2020-10-10T22:14:18Z")
@etlikmilos, Thanks for the information and the resource. I will take a look and learn some more. Thanks, Steven ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [October 11, 2020, 12:43am UTC](https://forum.inductiveautomation.com/t/sql-select-join-where-help/39571/7 "2020-10-11T00:43:10Z")
> [@steven.cox](#): > > This is the second time I had a query not work with comments below the query. Could this be a bug? No, it is a false expectation on your part. You are thinking JDBC is like a database's management studio, where query windows will also run that database's flavor of SQL _script_. Most JDBC drivers will run only one statement at a time, and only accept a valid _non-script_ statement. ---
**Author:** ![steven.cox](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steven.cox/32/18589_2.png) [@steven.cox](https://forum.inductiveautomation.com/u/steven.cox)\ **Post date:** [October 12, 2020, 2:01pm UTC](https://forum.inductiveautomation.com/t/sql-select-join-where-help/39571/8 "2020-10-12T14:01:00Z")
@pturmel I don’t know anything about that. I just got confused because the only difference between it working and not, was a valid comment. I have comments in other queries and they work fine. I thought maybe I was just overloading my system so I restarted and got the same results. I will have to be sure to clear out any comments if I have a query that does not work just to be sure. Thanks for all the help, Steven