Announcement

Collapse
No announcement yet.

Include a join query in sc_lookup macro

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Include a join query in sc_lookup macro

    Hi,

    I have two tables: user (with columns user_id and user_region) and region (with columns region_id and regions)

    I know now how to use the sc_lookup macro to show fields from another table, but don't know how to include a join query in the to show from which region a user is.

    1. How should the query look like?
    2. Can I do the query in the grid lookup settings or do I have to define it in the events (onscriptinit)?

    Joe

  • #2
    1. SELECT u.user_id, r.regions FROM user u LEFT JOIN region r ON u.user_region = r.region_id WHERE ...

    2. You don't need the join in the (user) grid lookup, just a plain lookup: SELECT regions FROM region WHERE region_id = {user_region}

    However, if you i.e. want to sort your grid alphabetically on regions you need to join the tables in the SQL statement of the grid.

    jsb

    Comment


    • #3
      Thanks jsb,

      I can't get it work. The output in my grid field looks like

      region1 2
      region1 1
      region3 3
      region4 5
      .....

      The region field should show just one region depending on the user shown in the user field

      Joe

      Comment


      • #4
        Solved

        SELECT region FROM regions, user WHERE user_region = region_id and user_id = {user_id}

        Joe

        Comment

        Working...
        X