INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL request

SQL request

(OP)

Let's say I have data like that in MyTable:

PIN               PID
...               ...
11-07-934-010     4086
11-07-934-010-01  4152
11-07-934-010-02  4153
11-07-934-010-03  4154
11-07-934-010-04  4155
11-07-934-010-05  4156
...               ...
 
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:
SELECT PID
FROM MyTable
WHERE PIN LIKE '11-07-934-010%'


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

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


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

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.

RE: SQL request

3


Try this:

CODE

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

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

RE: SQL request

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

SELECT PID
FROM MyTable
WHERE (SUBSTR(PIN, 1, 13) =
        (SELECT SUBSTR(PIN, 1, 13)
      FROM MyTable
      WHERE (PID = 4152)))
 

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close