Thread Details
  • State Suggested Answer
  • Replies 32 replies
  • Answers 1 answer
  • Subscribers 376 subscribers
  • Views 8272 views
  • Users 0 members are here
Forum Actions
Related

Join 2 tables

Rana Awais

Is there any way to join two tables like we join in MySQL like inner join , outer join etc
I want data from 2 tables on foreign key basis

Parents
  • 0

    EDIT: This answer is not relevant anymore since the OP's question was something totally different which I misunderstood!


    Hi ,

    Can you provide more details on what you're trying to achieve? Which two tables are you trying to join? Are they sugar modules? 

    Yes, we can actually join two tables, though there are no foreign keys.

    For plain relate fields, you can just join directly by using the IDs. Eg:

    select * from cases c
        join accounts a on c.account_id = a.id and a.deleted = 0
    where c.deleted = 0;

    For relationships, there is usually a join table, eg:

    select * from contacts c
        left join accounts_contacts ac on ac.contact_id = c.id and ac.deleted = 0
        left join accounts a on ac.account_id = a.id and a.deleted = 0
    where c.deleted = 0;

Reply
  • 0

    EDIT: This answer is not relevant anymore since the OP's question was something totally different which I misunderstood!


    Hi ,

    Can you provide more details on what you're trying to achieve? Which two tables are you trying to join? Are they sugar modules? 

    Yes, we can actually join two tables, though there are no foreign keys.

    For plain relate fields, you can just join directly by using the IDs. Eg:

    select * from cases c
        join accounts a on c.account_id = a.id and a.deleted = 0
    where c.deleted = 0;

    For relationships, there is usually a join table, eg:

    select * from contacts c
        left join accounts_contacts ac on ac.contact_id = c.id and ac.deleted = 0
        left join accounts a on ac.account_id = a.id and a.deleted = 0
    where c.deleted = 0;

Children