Plz find the following set of operations. Can anybody tell me how does LAST_INSERT_ID() works in this scenario?Im confused with the output im getting.thanx in advance.

CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);

INSERT INTO person VALUES (NULL, 'Antonio Paz');

mysql> select * from person;
+----+-------------+
| id | name |
+----+-------------+
| 1 | Antonio Paz |
+----+-------------+
1 row in set (0.00 sec)


mysql> select * from shirt;
+----+---------+-------+-------+
| id | style | color | owner |
+----+---------+-------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 2 |
+----+---------+-------+-------+
3 rows in set (0.00 sec)


INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());

INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', LAST_INSERT_ID()),
(NULL, 'polo', 'red', LAST_INSERT_ID()),
(NULL, 'dress', 'blue', LAST_INSERT_ID()),
(NULL, 't-shirt', 'white', LAST_INSERT_ID());



mysql> select * from person;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+----+---------------------+
2 rows in set (0.00 sec)


mysql> SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 2 |
| 4 | dress | orange | 2 |
| 5 | polo | red | 4 |
| 6 | dress | blue | 5 |
| 7 | t-shirt | white | 6 |
+----+---------+--------+-------+
7 rows in set (0.00 sec)

I tested what you have here, the shirts are listed as id of 1 for the first three and id of 2 for the final four. Try truncating your table and doing those inserts into the shirt table again.

thanx
I did as u said.
But still my output is same.I tried with droping that tables and doing the insertion again.No change to the output.

Im using mySQL4.0..

LAST_INSERT_ID() returns to you the id of the last AUTO_INCREMENT column you updated - link. It looks like this variable is updated even when you insert more than one record in the INSERT statement.

As both of your tables have AUTO_INCREMENTS on them - you are creating a new LAST_INSERT_ID() for each row you inserted. For example:

You have added a new person - with the id being 1. LAST_INSERT_ID() will now return 1.
When you add the first column - LAST_INSERT_ID() is 1 - and it will set this as your owner. But as you've just added a shirt row - the LAST_INSERT_ID() will now return 1 (the ID of the shirt you just added).
The second shirt you add is also getting 1 from LAST_INSERT_ID() - but as this new record has a key of 2 - LAST_INSERT_ID() will now return 2 for the next insert.
Therefore - when you are adding the third shirt - it owner is listed as 2.

This pattern is followed for the next set of inserts you do (the first owner is 2 - as you've just added a new person with ID 2. Then they increment for each shirt added - 4 to 6)

You need to store the id of the person you've added - then use it as the owner for each shirt you then insert. I know how to do this with PHP - but I haven't used MySQL variables (if they actually exist).

Good luck - I hope I've explained it well enough.

ThankU very much Mr.Dixon for ur explanation.

And as u said i used variables and resolved the problem.
Here is vat i did and my output.

CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);

INSERT INTO person VALUES (NULL, 'Antonio Paz');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);

INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

SELECT @last := LAST_INSERT_ID();

INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', @last),
(NULL, 'polo', 'red', @last),
(NULL, 'dress', 'blue', @last),
(NULL, 't-shirt', 'white', @last);

SELECT * FROM person;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+----+---------------------+

SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
| 7 | t-shirt | white | 2 |
+----+---------+--------+-------+

SELECT s.* FROM person p, shirt s
WHERE p.name LIKE 'Lilliana%'
AND s.owner = p.id
AND s.color <> 'white';

+----+-------+--------+-------+
| id | style | color | owner |
+----+-------+--------+-------+
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
+----+-------+--------+-------+

Thanx once again.