Agree with Jason,

add an user_id field into the status table, then

update status set user_id = id -(id/1000)*1000;

select s.*,u.* from status s
join users u on u.id=s.user_id
where s.status_id=4; -- 4=finish

anyway, if you have no chance to change db design:

select s.*,u.* from status s
join users u on u.id=s.id-(s.id/1000)*1000
where s.status_id=4; -- 4=finish

Regards.
Ali

--- In firebird-support@yahoogroups.com, Jason Dodson <jasond@...>
wrote:

>
> Not to be the jerk, but I recommend you simply get a book on

relational database. What you are trying to do is wrong on

> multitudes of levels and continuing forward with it will cause

someone to eventually slit their wrists.

>
> Jason
>
> majstoru wrote:
> > Hi,
> >
> > I have two tables with data like this sample:
> >
> > STATUS table:
> >
> > id status
> > 1010001 idle
> > 1010002 close
> > 1020003 statusx
> > 1020002 finish
> >
> > USERS table
> >
> > id name
> > 001 User_name1
> > 002 User_name2
> > 003 User_name3
> >
> > like you can see, firs 4 chars of id from status table are not
> > important for JOIN table users in SQL statement!
> >
> > My problem is how to write SELECT statement which will cut first

4

> > letters from id from STATUS table (Sample 1010001 -> 001) and

join

> > USERS table on STATUS table by this these 3 letters (Sample 001)
> >
> > Thaks...
> >
> >
> >
> >
> >
> >

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

> >
> > Visit http://firebird.sourceforge.net and click the Resources

item

> > on the main (top) menu. Try Knowledgebase and FAQ links !
> >
> > Also search the knowledgebases at http://www.ibphoenix.com
> >
> >

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

> >
> > Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>
> --
> The information transmitted herewith is sensitive information

intended only for use to the individual or entity to which it is
addressed. If the reader of this message is not the intended
recipient, you are hereby notified that any review, retransmission,
dissemination, distribution, copying or other use of, or taking of
any action in reliance upon, this information is strictly
prohibited. If you have received this communication in error,
please contact the sender and delete the material from your computer.

>