1. We suggest you do NOT update to Joomla 3.8.10 until we can resolve an issue it causes with caching in Fabrik. If you do install it, you'll need to disable Joomla's "System Cache" in the global System settings.
  2. Apologies for the recent server outage, a planned migration by our host provider to a new location turned into a bit of a nightmare.

Database join

Discussion in 'Professional Support' started by nofc_fro, Jul 12, 2018.

  1. nofc_fro

    nofc_fro New Member

    Level: Professional
    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?
     
  2. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    Let's deal with them one by one ...

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

    Code (Text):

    {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
     
  3. nofc_fro

    nofc_fro New Member

    Level: Professional
    No, I have no error but I have a empty field :(
    [​IMG]
     
    Last edited: Jul 13, 2018
  4. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    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
     
  5. nofc_fro

    nofc_fro New Member

    Level: Professional
    OK! I got it! Now works! Thank you :)
     
  6. nofc_fro

    nofc_fro New Member

    Level: Professional
    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)
    [​IMG]

    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?
     
  7. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Professional
    What table are you joining to?

    -- hugh
     
  8. nofc_fro

    nofc_fro New Member

    Level: Professional
    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.

    [​IMG]
     
    Last edited: Jul 16, 2018 at 5:46 AM

Share This Page