• Sign In
  1. Messages

SQl WHERE statement trouble

LabKey Support Forum

View Message

view list print
SQl WHERE statement trouble katy wiseman  2022-06-14 09:57
Status: Closed
 

Hi,

I'm trying to use a vey simple WHERE statement in my SQL query but I keep getting an error saying could not resolve column. You documentation seems to suggest what I'm trying to do is possible so I'm a bit lost.

I'm trying to do:
SELECT
*
FROM miseq_results

WHERE well = "A01"

and I get this error:
Query 'lists_temp_5991' has errors Edit Query
Error on line 5: Could not resolve column: A01 edit lists_temp_5991

and your documentation says that it should be possible to use where to filter on a value, rather than column=column:
Filter the results for certain values. Example:

SELECT *
FROM "Physical Exam"
WHERE YEAR(Date) = 2010

Could someone help me with where I'm going wrong please?

Katy
 
 
Matthew Bellew responded:  2022-06-14 09:59

In SQL you use double-quotes around column names not strings (like most other languages). To create a string value you need to use single-quotes. E.g.

SELECT *
FROM miseq_results
WHERE well = 'A01'
 
katy wiseman responded:  2022-06-15 01:23

Ahhh thank you! That was a very easy fix :).
It also turns out I was having some problems as the well column is a look up so I need to put in some left joins as well for my WHERE statement to work on the text value rather than the primary key.
 
Matthew Bellew responded:  2022-06-15 09:44

Hi Katy,

Just so you know. If a lookup is defined LabKey SQL provides a simple syntax to refer to the joined table. For instance if "well" has a foreign key (lookup) defined, and the target table has a column called "name" you can simply use this syntax.

WHERE well.name = 'A01'

The "." in the "well.name" does the left outer join for you. Hope that's helpful.
 
katy wiseman responded:  2022-06-16 00:56

Thank you! I didn't know that, that's very helpful!