--- [EMAIL PROTECTED] wrote:
> I am trying to write my first application using sqlite in perl.
> The perl part is no problem, but I have not used sql before and
> am having some difficulty.
>
> I have three tables:
>
> CREATE TABLE i2u(i UNIQUE, u);
> INSERT INTO i2u VALUES('i1','u1');
> INSERT INTO i2u VALUES('i2','u2');
> INSERT INTO i2u VALUES('i3','u3');
>
> CREATE TABLE m2u(m UNIQUE, u);
> INSERT INTO m2u VALUES('m1','u1');
> INSERT INTO m2u VALUES('m2','u2');
>
> CREATE TABLE mu(m UNIQUE, u);
>
>
> Given some I and M, I wish to generate U from the first success
> when attempting to map I->U in i2u or M->U in m2u.
> So, for example, ("i1","m2") should map to ("u1").
>
> If the first stage found a U, I want to insert a new row into mu.
> So, continuing the example, we should insert ("m2", "u1").
>
>
> I feel sure there must be a way to implement this as a single
> sql query, without having to resort to messy perl wrapping.
>
> The sqlite sql documentation gives me some ideas, but none yet
> that works properly. So far, the closet to functional code I have
> come up with is:
>
> INSERT INTO mu
> SELECT ?,u FROM (
> SELECT u FROM i2u WHERE i = ?
> UNION
> SELECT u FROM m2u WHERE m = ?
> )
> ORDER BY ROWID
> LIMIT 1
> ;
>
> If I bind "M","I","M" to this as a test, then I do not get
> the result I hoped for (ie. nothing inserted).
>
> This seems to be because, for example, this:
>
> SELECT u FROM i2u WHERE i = "I";
>
> matches all three of the existing rows.
The expression "I" means the column "i". For the literal "I", use 'I'.
The query above will probably work if you bind (say) 'm1', 'i1', 'm1'.
Naturally don't include the quotes in the value passed to sqlite3_bind_text().
__________________________________
Do you Yahoo!?
Make Yahoo! your home page
http://www.yahoo.com/r/hs
