SQL request 4

[Andrzejek]

Let's say I have data like that in MyTable:
[pre]
PIN PID
... ...
11-07-934-010 4086
11-07-934-010-01 [blue]4152[/blue]
11-07-934-010-02 4153
11-07-934-010-03 4154
11-07-934-010-04 4155
11-07-934-010-05 4156
... ...
[/pre]
If I only have one PID available (let's say 4152), how can I get the rest of the PIDs in one easy request?

I know I can do this:[tt]
SELECT PID
FROM MyTable
WHERE PIN LIKE '11-07-934-010%'[/tt]

but I don't have 11-07-934-010% available.

I can get 11-07-934-010 by:[tt][blue]
SELECT SUBSTR(PIN, 1, 13) AS MYPIN
FROM MyTable
WHERE PID = 4152[/blue][/tt]

But then I cannot combine the 2 requests:[tt]
SELECT PID
FROM MyTable
WHERE PIN LIKE '([blue]SELECT SUBSTR(PIN, 1, 13)
FROM MyTable
WHERE PID = 4152[/blue])%'[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[LKBrwnDBA]

Try this:

SELECT Pid
  FROM Mytable A
     , (SELECT SUBSTR ( Pin, 1, 13 ) Kk
          FROM Mytable
         WHERE Pid = 4152) B
 WHERE Pin LIKE B.Kk || '%' ;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Andrzejek]

Isn’t that interesting that once you state your problem, walk away, and after a few moments the answer just comes to you... ?

[pre]
SELECT PID
FROM MyTable
WHERE (SUBSTR(PIN, 1, 13) =
(SELECT SUBSTR(PIN, 1, 13)
FROM MyTable
WHERE (PID = 4152)))
[/pre]

Thank you LKBrwnDBA for the answer, but I will stick with mine.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.