Include a join query in sc_lookup macro

Scriptcase 8 Discussion
#1

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

#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

#4

Solved

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

Joe