databasejoin from many repeated tables

[elle]

Hi there,

I have another problem in the same project like desribed here:
http://fabrikar.com/forums/index.php?threads/where-statement-in-databasejoin.40008/

I want to have a databasejoin element, render as checkbox.
It should show me the user names, stored in the joomla users table.
But only those users, who's id is stored in the table "schulungen_repeat_promoter"
See here:

WHERE it's field 'parent_id' is the id in the table "schulungen"

WHERE it's field 'firma_schulung' in turn has the same number as the element 'terminplaner___firma_planer'
in my form, where the databasejoin should display.

The setup for this databasejoin element looks so:


Can you help me to write the right WHERE statement into the Data - where field?

I have tried many time but without any success.

Many thanks for youre help.
Nice regards

Elle

 

[elle]

By the way:

I've tried to use a cascadingdropdown, but there is no chance to pickup the joomla-user table.

 

[cheesegrits]

I just don't uderstand your explanation of the relationships.

So, in table schulungen_repeat_promoter, the 'promoter' field is the userid, correct?

But you want to somehow then restrict it to those propomoters from schulungen_repeat_promoter, where the parent_id refers to the primary key on a row in schulungen, where he field 'firma_schulung' matches the PK of the row in the table you are display a row from?

-- hugh

 

[elle]

Hi Hugh,

hope you are well.
Thank you for youre reply.

So, in table schulungen_repeat_promoter, the 'promoter' field is the userid, correct?

Correct!

But you want to somehow then restrict it to those propomoters from schulungen_repeat_promoter, where the parent_id refers to the primary key on a row in schulungen, where he field 'firma_schulung' matches the PK of the row in the table you are display a row from?

Not exactly.

What I have for now is the following WHERE-statement:

WHERE {thistable}.`id`
  IN (SELECT `schulungen_repeat_promoter`.`promoter` FROM `schulungen_repeat_promoter`, `schulungen`
  WHERE `schulungen_repeat_promoter`.`parent_id` = `schulungen`.`id`
  AND `schulungen`.`firma_schulung` = 5)

That works!

But I need to change the "5" at the end to the element "terminplaner___firma_planer" in this form.
Don't know, how to refer to this element...

 

[elle]

friendly bump