Many To Many

[robertwh]

Trying the following:

statement: 'SELECT st.SiteID, st.SiteName, st.ExpandedSiteName FROM SiteTable st JOIN ActiveTips t ON st.SiteID = t.SiteID WHERE ((st.Active = 1 AND st.SiteDump = 0) OR st.CopywriterID IS NOT NULL) AND t.DeletedDate IS NULL GROUP BY st.SiteID, st.SiteName, st.ExpandedSiteName HAVING COUNT(*) > 10 ORDER BY st.SiteName', idFieldName: 'TipID', name: 'exploreTipSites'

Which I believe is a many to many join. Doesn't seem to work, is it possible to allow it?

[malcolm-kee]

Thanks for raising the issue. Is TipID returned by the query?

Field	Required?	Description
idFieldName	Required	column that is unique for each record. This column must be returned by the statement.

[robertwh]

Yes. Unique to the ActiveTips table

Sent from my iPhone

On Jun 7, 2019, at 8:11 PM, Malcolm Kee notifications@github.com wrote:

Thanks for raising the issue. Is TipID returned by the query?

Field Required? Description idFieldName Required column that is unique for each record. This column must be returned by the statement. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or mute the thread.

[malcolm-kee]

Hi @robertwh, try putting SiteID as idFieldName because the idFieldName actually means unique identifier for your query result, not id for your table.

[robertwh]

I'll give that a try, but it's not unique to the rows returned, TipID is.

[robertwh]

Just reran everything with TipID as the idFieldName as well as adding it in to the select statement. Looks like it's working now. Thought for sure this was how I originally tried it and it didn't work. Here is the full code:

      {
        statement: 'SELECT t.TipID, st.SiteID, st.SiteName, st.ExpandedSiteName FROM SiteTable st JOIN ActiveTips t ON st.SiteID = t.SiteID WHERE ((st.Active = 1 AND st.SiteDump = 0) OR st.CopywriterID IS NOT NULL) AND t.DeletedDate IS NULL GROUP BY st.SiteID, st.SiteName, st.ExpandedSiteName HAVING COUNT(*) > 10 ORDER BY st.SiteName',
        idFieldName: 'TipID',
        name: 'exploreTipSites'
      }

Thanks for the help/quick responses!

[malcolm-kee]

@robertwh great! I'll close the issue.