browsesql browse form issue
Posted: Sun Apr 01, 2018 10:12 am
I have two tables that look like this:
Table Notes:
Table Positions:
This is the sql for the Browse Form:
The output will look like this:
Issue: The browse form returns no rows.
nuCurrentProperties().browse_sql shows a completly messed up sql. How to make this work?
Table Notes:
Code: Select all
╔══════════╦═════════════════╗
║ nid ║ forDepts ║
╠══════════╬═════════════════╣
║ 1 ║ 1,2,4 ║
║ 2 ║ 4,5 ║
╚══════════╩═════════════════╝Code: Select all
╔══════════╦═════════════════╗
║ id ║ name ║
╠══════════╬═════════════════╣
║ 1 ║ Executive ║
║ 2 ║ Corp Admin ║
║ 3 ║ Sales ║
║ 4 ║ Art ║
║ 5 ║ Marketing ║
╚══════════╩═════════════════╝Code: Select all
SELECT a.nid, GROUP_CONCAT(b.name ORDER BY b.id) DepartmentName
FROM Notes a
INNER JOIN Positions b
ON FIND_IN_SET(b.id, a.forDepts) > 0
GROUP BY a.nid
Code: Select all
╠══════════╬════════════════════════════╣
║ 1 ║ Executive, Corp Admin, Art ║
║ 2 ║ Art, Marketing ║
╚══════════╩════════════════════════════╝
nuCurrentProperties().browse_sql shows a completly messed up sql. How to make this work?
Code: Select all
SELECT a.nid, a.field1, a.field2 -- and all other fields (a.*)
FROM Notes a
INNER JOIN Positions b ON FIND_IN_SET(b.id, a.forDepts) > 0
GROUP BY a.nid
WHERE 1
ORDER BY b.id) DepartmentName
FROM Notes a
INNER JOIN Positions b ON FIND_IN_SET(b.id, a.forDepts) > 0
GROUP BY a.nid