WHERE with BETWEEN

  • Resolved b…b

    (@bb-4)


    4 years ago

    Great Plugin … I am using Data Projects with the type parent/child. The parent table has a col. saleDay. The child table has 2 cols. startDay and endDay. All are INT(10).

    I want the child table to filter and show only rows if saleDay is between startDay and endDay.

    Can I use the BETWEEN operator? or is there a different way to do this?

    • This topic was modified 4 years ago by b...b.
    • This topic was modified 4 years ago by b...b.
Viewing 5 replies - 1 through 5 (of 5 total)
  • Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    4 years ago

    Hi,

    Welcome to the forum! 🙂

    You should be able to add such a where clause to your child table in your table options. Please add an option set (Data Projects >>> Manage Table Options) and add your default where clause to your option set. After that, make sure your Data Projects page uses that option set.

    Here are some instructions video on how to use option sets:
    https://wpdataaccess.com/docs/documentation/data-projects/manage-table-options/
    https://wpdataaccess.com/docs/documentation/data-projects/table-options-advanced/

    Please let me know if you need help.

    Good luck,
    Peter

    Thread Starter b…b

    (@bb-4)

    4 years ago

    Peter,

    I reviewed how to make option sets. Looks like I will need this after I get the WHERE statement working.

    I have tried a few different syntaxes with no success. I have (2) tables: salesparent and saleschild.

    I tried the following in the default WHERE of the saleschild table. All give “No data found”

    WHERE salesparent.saleDay BETWEEN saleschild.startDay AND saleschild.endDay
    WHERE salesparent.saleDay BETWEEN saleschild.startDay AND saleschild.endDay;
    WHERE ‘salesparent’.’saleDay’ BETWEEN ‘saleschild’.’startDay’ AND ‘saleschild’.’endDay’
    WHERE ‘salesparent’.’saleDay’ BETWEEN ‘saleschild’.’startDay’ AND ‘saleschild’.’endDay’;

    Any ideas?

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    4 years ago

    Hi,

    Bad news! You cannot use a parent column value in a child where clause… 😐

    But I see the relevance. Let me have a look it. I’ll see what I can do and let you know.

    Best regards,
    Peter

    Thread Starter b…b

    (@bb-4)

    4 years ago

    I’m making progress.

    I created a view combining the salesparent and saleschild tables (v_salesparent_saleschild). I then set a parent/child 1:n relationship on salesparent for a new field (I called it storeNo), this field is fake, but gives me the parent/child view.

    On the v_salesparent_saleschild I set the Default WHERE BETWEEN statement with a few more AND’s.

    Presto… All seems to be working. I lost the ability to make changes from the child table, since it is now only a view. But, that is OK, I only need to make changes in the parent table, and get information from the child.

    Probably not how you intended for using the plugin (but it seems to be working great for my project).

    Now onto option sets. 🙂

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    4 years ago

    Great! 🙂

    Let me know if you need help!

    Best regards,
    Peter

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘WHERE with BETWEEN’ is closed to new replies.