Just new to the DMX stuff after using the WebAssist extensions for years - so I'm just getting my bearings.

Have managed to create a database connection and add a simple query. But where do I need to go to create joins in my query?

Also, is it possible to manually edit the query code anywhere? (I'm used to seeing it on the page.)

I would be interested in migrating to DMX, but I have lots of queries set up that ideally I thought maybe I could copy and paste into DMX equivalent pages, e.g. stuff like:

SELECT
lodges.LodgeID
, lodges.Lodge
, Activity
, lodges.subscription
, Category_ID
, product_profiles.LastUpdated
, subscription_end_date
,
(SELECT Count(DISTINCT UserIDPL, LodgeIDPL)
FROM product_likes
INNER JOIN trade_users
ON product_likes.UserIDPL = trade_users.UserID
WHERE LodgeIDPL = lodges.LodgeID AND trade_users.Visible = 'Yes' AND (User_Level = 3 OR User_Level = 7))
AS connections
FROM lodges
LEFT JOIN product_profiles
ON lodges.LodgeID = product_profiles.LodgeID
INNER JOIN lodge_activities_it
ON lodges.LodgeID = lodge_activities_it.LodgeID
INNER JOIN lodge_activities
ON lodge_activities_it.ActivityID = lodge_activities.ActivityID
WHERE Category_ID = 15
GROUP BY LodgeID

Is that possible?

Thanks.

Hello,
You can select a join and set it up, when you add a second table to the query builder.
Unfortunately we do not support pasting sql code in our extensions, please use the visual query builder.

Thanks Teodor - I see what its doing now.

Would I be able to have nested parts to queries, such as the

(SELECT Count(DISTINCT UserIDPL, LodgeIDPL)

or use things like GROUP and HAVING in my queries?

If not, that might be a deal breaker.

Being able to create custom queries using the actual SQL would be a definite bonus.