Exclude parents query

[DouglasF]

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 child record(s) exist.

In other words:

If record id's are:

3100
3101
3102
3200
3300

Records 3101, 3102, 3200, 3300 are returned, but 3100 is not.

Any ideas?

Using MySQL 5

Thanks,
Douglas

 

[PHV]

You may try this:
SELECT R.*
FROM tblRecords AS R
WHERE (SUBSTRING(R.ID FROM 3 FOR 2)='00'
AND NOT EXISTS(SELECT * FROM tblRecords WHERE ID LIKE SUBSTRING(R.ID FROM 1 FOR 2) || '%' AND ID<>R.ID))
OR SUBSTRING(R.ID FROM 3 FOR 2)<>'00'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DouglasF]

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

 

[DouglasF]

<Bump>

Any ideas?

 

[PHV]

still returns the parents who have children
Could you please post the SQL code exhibiting this behaviour ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DouglasF]

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 codeID<>R.codeID)) OR SUBSTRING(R.codeID FROM 3 FOR 2)<>'00';

Doug

 

[r937]

doug, you said this was mysql 5

did you set the server mode for PIPES_AS_CONCAT?

because if you didn't, || is treated as a logical OR

try this instead --

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 
                 [b]CONCAT[/b](SUBSTRING(R.codeID FROM 1 FOR 2),'%') 
             AND codeID <> R.codeID )
       ) 
    OR SUBSTRING(R.codeID FROM 3 FOR 2) <> '00'

r937.com | rudy.ca

 

[DouglasF]

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() but now returns no parents rather than just those without children.

 

[PHV]

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 FOR 2)<>'00'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DouglasF]

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 FOR 2)<>'00'

Wow! without the CONCAT() and LIKE its about 15 times faster on 1000 records, but still no parents. Grrr.

Doug