New to SQL need help

[rkass068]

Hi all!

 

I am trying to perform an SQL calculation in filemaker 13. I have two related tables of importance to the calculation

 

1. Main

2. Entries

 

__pk_Main = __fk_Main (in entries table)

 

In the entries table I have the fields Date, EndDate, Format

 

I want to make an SQL statement that lists the Date,EndDate only with format = "chemotherapy"

 

Here is my statement that is giving me a "?"

 

ExecuteSQL (  "SELECT date, EndDate, format FROM Entries WHERE format = chemotherapy ARRANGE BY Date"; "" ; "")

 

Thanks for the help!

[Wim Decorte]

"date" is a reseved SQL keyword.  Avoid naming your fields with names that are reserved keywords, data types, ...

 

Put quotes around "date" or change the field name

 

And I think you meant "ORDER BY" and not "ARRANGE BY"

[rkass068]

"date" is a reseved SQL keyword.  Avoid naming your fields with names that are reserved keywords, data types, ...

 

Put quotes around "date" or change the field name

 

And I think you meant "ORDER BY" and not "ARRANGE BY"

Thank you for the help but it seems to still be doing the same thing. I'm getting a "?" for the solution after I changed the field name and put ORDER BY. Don't know why I put "Arrange by" in my question when i did type Order by hahaha

[Wim Decorte]

 

 

ExecuteSQL (  "SELECT date, EndDate, format FROM Entries WHERE format = chemotherapy ARRANGE BY Date"; "" ; "")

 

 

you also need to put "chemotherapy" in single quotes(')

[rkass068]

you also need to put "chemotherapy" in single quotes(')

Everything seems to be working except the WHERE clause for some reason. Even with the single quotes it just generates a blank list. I was also wondering how I would only allow the query to come from the related record being browsed?

 

EDIT: Its ok works now! Thanks so much for the help!

[comment]

I was also wondering how I would only allow the query to come from the related record being browsed?

 

Keep in mind that SQL has no access to the relationships defined in Filemaker. You need to either define the join in the SQL query, or  (preferably, IMHO) use Filemaker's native tools. In this example, a filtered portal should work very nicely.

[Wim Decorte]

Keep in mind that SQL has no access to the relationships defined in Filemaker. You need to either define the join in the SQL query

 

You don't need to declare a JOIN to pick up related records, you can do a simple select against the child table as long as you specify the right WHERE clause to isolate the proper records in the child table.

 

You only need JOINs when you want to return data from more than one table at once in the result set.  As long as all the data you are asking for comes from one table, there is no need for JOINs.

[comment]

You don't need to declare a JOIN to pick up related records, you can do a simple select against the child table as long as you specify the right WHERE clause to isolate the proper records in the child table.

 

I was hoping to avoid this technicality by writing "define the join" in lower-case...