PH wrote:
And what about this ?
SELECT R.codeID, R.codeName
FROM jobCode AS R
WHERE (SUBSTRING(R.codeID FROM 3 FOR 2)='00'
AND NOT EXISTS(SELECT codeID FROM jobCode WHERE SUBSTRING(codeID FROM 1 FOR 2)=SUBSTRING(R.codeID FROM 1 FOR 2)
AND codeID<>R.codeID))
OR SUBSTRING(R.codeID FROM 3...
Thanks Rudy.
Yes MySQL5.
did you set the server mode for PIPES_AS_CONCAT? because if you didn't, || is treated as a logical OR
Using default server mode so this explains why I had difficulty understanding how PH's example worked... assumed that the pipes were an OR.
Changed to use CONCAT()...
Could you please post the SQL code exhibiting this behaviour
Thanks PH. Here is what I tested.
SELECT R.codeID, R.codeName
FROM jobCode AS R
WHERE (SUBSTRING(R.codeID FROM 3 FOR 2)='00'
AND NOT EXISTS(SELECT codeID FROM jobCode WHERE codeID LIKE SUBSTRING(R.codeID FROM 1 FOR 2) || '%'
AND...
Thanks PH for the good example of using SUBSTRING.
Your suggestion is close, but still returns the parents who have children. Need to filter out parent IDs who have the same two first digits as children IDs.
Doug
Appreciate some suggestions on creating a select query.
-Retrieving records on a four digit id number.
-Parent record id numbers end in '00' (i.e. 3100), child records end with '01' to '99' (i.e. 3102).
-Need to exclude parent record if child record(s) exist.
-Include parent records where no...
Hi lupines46,
My example query1 returns a non-distinct list of user groups instead of four different user groups with the 'checked' field set to true or false such as:
UserGroup_id | UserGroup | Checked
1 | Staff | 0
2 | Manager | 0
2 |...
Thanks PHV,
Your suggestion works perfectly! A little sleep has helped me see a little clearer...
I needed the subquery to set the 'checked' field for each userGroup. The LEFT JOIN allows all userGroups to be returned with each having a 'checked' field set to true or false depending on the...
Thanks Lupins46
Your correct in that I want to show ALL the userGroups but I do NOT want to limit the records to those which just match the entered user_id. The record set needs to include ALL userGroups and also create and set the 'checked' field for each userGroup to '1' for userGroups that...
I am trying to get a recordset containing a distinct list of all User groups in the userGroups table. If the user_id making the query belongs to any of the groups, flag the 'checked' field as '1', if not flag the 'checked' field as '0'. User_id may belong to none or multiple userGroups.
The...
<CFoutput> Usage:
Is it good practice to use one <CFoutput> </CFoutput> surrounding a whole template or use multiple uses where the CF code needs it throughout the page? Would the template process faster if multiple CFoutput's are used? Does having HTML between the tag slow the page processing?
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.