I do not understand why my cascading drop down doesn't work with some of the following SQL statements:
Works with this, but doesn't put "English" with my key columns to make selection readable:
SELECT contactid FROM workordercontactlist WHERE cid = session.companyid AND active = 'TRUE' ORDER BY contactid
Does not work with this, even though tests correctly in SQL builder:
SELECT CONCAT(contacts.firstname, ' ', contacts.middlename, ' ', contacts.lastname, ' - ', contacts.company) AS Name,workordercontactlist.contactid
FROM workordercontactlist workordercontactlist INNER JOIN contacts contacts ON workordercontactlist.cid = contacts.cid AND
workordercontactlist.contactid = contacts.contactid WHERE workordercontactlist.cid = session.companyid AND
workordercontactlist.active = 'TRUE' ORDER BY workordercontactlist.contactid
And would really like it to work with this, which does not test correctly in SQL builder:
SELECT CONCAT( If(contacts.firstname IS NULL , '', contacts.firstname) , If(contacts.middlename IS NULL , '', CONCAT(' ', contacts.middlename) ) ,
If(contacts.lastname IS NULL , '', CONCAT(' ', contacts.lastname) ) , If(contacts.lastname IS NULL AND contacts.company IS NOT NULL ,
contacts.company, If(contacts.company IS NULL , '', CONCAT(' - ', contacts.company) ) ) ) AS Name, workordercontactlist.contactid
FROM workordercontactlist workordercontactlist
INNER JOIN contacts contacts
ON workordercontactlist.cid = contacts.cid AND workordercontactlist.contactid = contacts.contactid
WHERE workordercontactlist.cid = session.companyid AND workordercontactlist.active = 'TRUE'
ORDER BY workordercontactlist.contactid
I had read on the board that naming the columns exactly was important and tried naming the CONCAT column as contactid with the same result.
Thank you for the help.
Works with this, but doesn't put "English" with my key columns to make selection readable:
SELECT contactid FROM workordercontactlist WHERE cid = session.companyid AND active = 'TRUE' ORDER BY contactid
Does not work with this, even though tests correctly in SQL builder:
SELECT CONCAT(contacts.firstname, ' ', contacts.middlename, ' ', contacts.lastname, ' - ', contacts.company) AS Name,workordercontactlist.contactid
FROM workordercontactlist workordercontactlist INNER JOIN contacts contacts ON workordercontactlist.cid = contacts.cid AND
workordercontactlist.contactid = contacts.contactid WHERE workordercontactlist.cid = session.companyid AND
workordercontactlist.active = 'TRUE' ORDER BY workordercontactlist.contactid
And would really like it to work with this, which does not test correctly in SQL builder:
SELECT CONCAT( If(contacts.firstname IS NULL , '', contacts.firstname) , If(contacts.middlename IS NULL , '', CONCAT(' ', contacts.middlename) ) ,
If(contacts.lastname IS NULL , '', CONCAT(' ', contacts.lastname) ) , If(contacts.lastname IS NULL AND contacts.company IS NOT NULL ,
contacts.company, If(contacts.company IS NULL , '', CONCAT(' - ', contacts.company) ) ) ) AS Name, workordercontactlist.contactid
FROM workordercontactlist workordercontactlist
INNER JOIN contacts contacts
ON workordercontactlist.cid = contacts.cid AND workordercontactlist.contactid = contacts.contactid
WHERE workordercontactlist.cid = session.companyid AND workordercontactlist.active = 'TRUE'
ORDER BY workordercontactlist.contactid
I had read on the board that naming the columns exactly was important and tried naming the CONCAT column as contactid with the same result.
Thank you for the help.
Comment