Database join

[nofc_fro]

Hi,
I have a few cases to ask, forgive me

CASE 1. Database join in "ccc" list
I have this situation:
Two tables called "aaa" and "bbb".
The "aaa" table has two elements: id, name (e.g. 100, frank)
The "bbb" table has three elements: id, aaa_id, surname (e.g. 201.100, smith)

I create a new "ccc" list that has two elements: id,namesurname(database join)
I would like to have this result: 999,frank smith

If I create an Element with Plug-in "Databasejoin" I go to the tabs "Data", I select the table "aaa", Value "id" and in "Concat label" what should I do?

CASE 2. Database join in "bbb" list
I have a similar situation:
Two tables called "aaa" and "bbb".
The "aaa" table has two elements: id, league,num (e.g. 100, nhl, 326)
The "bbb" table has three elements: id, month_id, name_month (e.g. 200, 326, april)
I would like to create a new "database join" element in the "aaa" table that gives me the name of the month
How can I do?

CASE 3. One to many
This is the most difficult case.
I have this situation:
Two tables called "aaa" and "bbb".
The "aaa" table has two elements: id, company (e.g. 100, corporationxxx)
The "bbb" table has three elements: id, company_id, attribute_id, value. BUT I have more records with aaa_id. E.g.->
"aaa" table
100,corporationxxx
200,corporationyyy

"bbb" table
1,100,10,sun
2,100,11,happy
3,100,12,sing
4,200,10,snow
5,200,11,sad
6,200,12,silence
I would like to create a new "database join" elements in the "aaa" table that gives me the list of values:
NEW "aaa" table 100,corporationxxx,sun,happy,sing
How can I do?

 

[cheesegrits]

Let's deal with them one by one ...

CASE 1. Database join in "ccc" list
I have this situation:
Two tables called "aaa" and "bbb".
The "aaa" table has two elements: id, name (e.g. 100, frank)
The "bbb" table has three elements: id, aaa_id, surname (e.g. 201.100, smith)

I create a new "ccc" list that has two elements: id,namesurname(database join)
I would like to have this result: 999,frank smith

If I create an Element with Plug-in "Databasejoin" I go to the tabs "Data", I select the table "aaa", Value "id" and in "Concat label" what should I do?

You can use dependent subqueries in your concat, which you'll have to do to get the 'surname' from bbb

{thistable}.name, ' ', (SELECT surname FROM bbb WHERE aaa_id = {thistable}.id)

Let me know if that works, and we'll move on to the second one.

-- hugh

 

[nofc_fro]

No, I have no error but I have a empty field

 

[cheesegrits]

What are you expecting to see in the list view of CCC?

If you add a new record in CCC, you should then have the "Join" element, which allows you to select from AAA, with the concatenated label as the dropdown labels. So on form CCC you are choosing a record from AAA, with the name from BBB concatenated to the label.

It sounds like you may be confusing the database join element with list joins. Are you trying to create a table that shows all the records of AAA and BBB joined, so you see first and last names?

If so, then edit List AAA, go to the Data tab, Joins, and add a join ...

From table: aaa
To table: bbb
From element: aaa.id
To element: bbb.aaa_id

Repeated: No

... and save.

That will then add a new group to AAA for the records from BBB, and list AAA will show the joined groups as individual rows.

-- hugh

 

[nofc_fro]

OK! I got it! Now works! Thank you

 

[nofc_fro]

I always have two tables:
"aaa" -> id, name (eg 100, frank; 101, peter)
"ddd" -> id; aaa_id; topic_id; topic (1,100,10, metal; 2,100,20, yellow; 3,101,10, pop; 4,101,20, armony)

in the table "aaa" I have to add two new "database join" elements with the "where" condition.
The first element will be called "Music" and will have the condition that id_topic = 10;
The second element will be called "Genre" and will have the condition that id_topic = 20;
How can I do?

 

[cheesegrits]

What table are you joining to?

-- hugh

 

[nofc_fro]

Hi,
I want to merge the "aaa" table with the "ddd" table, in particular I want to create an element in "aaa" that applies a condition WHERE on the "ddd" table.