Author Topic: Target Queries with Left Join  (Read 4113 times)

Offline dsagiorato

  • Newbie
  • *
  • Posts: 3
  • Karma: +0/-0
    • View Profile
Target Queries with Left Join
« on: May 12, 2006, 06:44:07 AM »
Hi,

Is possible use Left Join´s in target queries? Does anyone has alredy did target query with left join ?


Thank´s

Daniel

Offline dsagiorato

  • Newbie
  • *
  • Posts: 3
  • Karma: +0/-0
    • View Profile
Re: Target Queries with Left Join
« Reply #1 on: May 12, 2006, 11:24:11 AM »
I think it's not possible. I suggest using Custom Query option.


Thank´s, but I tried to use Custom Query Option using *= or left join and does not work.

Than

Offline to_mac

  • Full Member
  • ***
  • Posts: 17
  • Karma: +0/-0
    • View Profile
Re: Target Queries with Left Join
« Reply #2 on: May 12, 2006, 01:02:53 PM »
Pivotal QUERY always base on only one table.
In SQL language, Pivotal QUERY that's area between "SELECT ... FROM .... WHERE " and "ORDER BY ..."


Join is possible only when you prepare LIST with foreign key.

Offline kwiecin

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 84
  • Karma: +0/-0
  • Gender: Male
    • View Profile
    • Pivotal CRM Blog
Re: Target Queries with Left Join
« Reply #3 on: May 12, 2006, 04:12:54 PM »
In the list there's always inner join.

Daniel - what for do you need left join?

Pivotal Query always returns results from one table, but you can build conditions on joined tables.

In Custom SQL you can write WHERE condition only.

Regards,
MK
Marcin Kwiecinski
CRM || ITIL Consultant
Microsoft Certified Professional
-------------------------------------------------------------------------
Pivotal CRM Blog - Your everyday Pivotal Web Toolkit
www.pivotalcrm.info

Offline RLD_developer

  • Newbie
  • *
  • Posts: 3
  • Karma: +0/-0
    • View Profile
Re: Target Queries with Left Join
« Reply #4 on: May 26, 2006, 08:32:58 AM »
Link the query to a / any report and when you select the query name for the data source - save it.  from the list of reports, right click the report you've just created and select properties.  There is an option "All inclusive - this option toggles between left and inner join).  You can also use 2 nested queries and use the not in or in (insert table name / query for the right side of the query).

Hope this helps

Offline jaybee

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 125
  • Karma: +12/-0
  • Gender: Male
  • Up the irons !
    • View Profile
Re: Target Queries with Left Join
« Reply #5 on: May 29, 2006, 01:08:37 AM »
Hi,

If your join problem is really about queries, post a screen capture or the SQL part of it so we can made it a custom one with left join

Kindest Regards,
Jérôme BERGES
Carmis
France

Offline DanClarke

  • Newbie
  • *
  • Posts: 5
  • Karma: +0/-0
    • View Profile
Re: Target Queries with Left Join
« Reply #6 on: June 01, 2006, 06:16:14 AM »
I totally agree with the previous comments that you may not need a left outer join, and if you post your SQL someone may be able to advise a better solution; however if you use custom SQL you _can_ still use a left outer join, even though you're limited to defining the WHERE clause.

For example if you wanted to implement this SQL:

  select *
  from company c
   left outer join contact ct on ct.company_id = c.company_id
  where c.company_name like '%IBM%'

You can enter this in custom SQL:

  Company.Company_Id  in (select distinct c.company_id
         from company c
          left outer join contact ct on ct.company_id = c.company_id
         where c.company_name like '%IBM%')

(Having first created a new target query on the Company table of course).

Granted this isn't very elegant (and might run quite slowly), but it will give you the results you want. The same approach will work for other cases where you really want to change the SELECT clause - start the custom SQL with 'xxx_id IN' then write the SQL you wanted.

Hope this helps,


Dan.