Given the following tables:

----------
create table person (
id int(11) primary auto_increment,
name varchar(50)
) type=InnoDB;

create table attribute (
person_id int(11) primary auto_increment,
name varchar(50),
FOREIGN KEY(person_id) references person.id
) type=InnoDB;
----------

(Each person may have n attributes.)
How would you do the following in MySQL?

----------
SELECT p.name, a.name
FROM person p
LEFT JOIN attribute a ON a.person_id=p.id
WHERE p.id IN (SELECT id from person LIMIT 2 OFFSET 1)
----------

(LIMIT in Subselects is not supported in MySQL.)

Any idea?

-Samuel

On Mon, 10 Dec 2007 08:42:17 +0100, Samuel <newsgroups@debain.org> wrote:

> Given the following tables:
>
> ----------
> create table person (
> id int(11) primary auto_increment,
> name varchar(50)
> ) type=InnoDB;
>
> create table attribute (
> person_id int(11) primary auto_increment,
> name varchar(50),
> FOREIGN KEY(person_id) references person.id
> ) type=InnoDB;
> ----------
>
> (Each person may have n attributes.)
> How would you do the following in MySQL?
>
> ----------
> SELECT p.name, a.name
> FROM person p
> LEFT JOIN attribute a ON a.person_id=p.id
> WHERE p.id IN (SELECT id from person LIMIT 2 OFFSET 1)
> ----------
>
> (LIMIT in Subselects is not supported in MySQL.)
>
> Any idea?

What would you like to order 'person' by exactly? This query if yours
is/can be highly unpredictable.
--
Rik Wasmus

On Mon, 10 Dec 2007 09:01:00 +0100, Rik Wasmus wrote:

> What would you like to order 'person' by exactly? This query if yours
> is/can be highly unpredictable.

Say, "order by p.name,a.name", but I any (reproducible) ordering will do;
I just want a maximum of n different persons including their attributes.

-Samuel

On Mon, 10 Dec 2007 07:42:17 +0000 (UTC), Samuel
<newsgroups@debain.org> wrote:

>Given the following tables:
>
>----------
>create table person (
> id int(11) primary auto_increment,
> name varchar(50)
>) type=InnoDB;
>
>create table attribute (
> person_id int(11) primary auto_increment,
> name varchar(50),
> FOREIGN KEY(person_id) references person.id
>) type=InnoDB;
>----------
>
>(Each person may have n attributes.)

With person_id being the primary key in attribute you
would only be able to insert one attribute row for every
person row. Also, person_id can't be auto_increment, it
would probably refer to the wrong person. It has to get
the value of person.id whenever an attribute row is
inserted. The application has to take care of that.

In order to store n attributes per person you have to
revise your schema:

-- no changes for person
create table attribute (
person_id int(11),
attrib_id int(11),
name varchar(50),
PRIMARY KEY (person_id,attrib_id),
INDEX idx_fk_pi (person_id),
FOREIGN KEY(person_id) references person.id
) ENGINE=InnoDB;

You could define attrib_id as auto_increment (in which
case you have to index it), depending on your requirements
there are other options.

>How would you do the following in MySQL?
>
>----------
>SELECT p.name, a.name
>FROM person p
>LEFT JOIN attribute a ON a.person_id=p.id
>WHERE p.id IN (SELECT id from person LIMIT 2 OFFSET 1)
>----------
>
>(LIMIT in Subselects is not supported in MySQL.)
>
>Any idea?

With the changed schema your will problem will be a
different one.

>-Samuel
--
( Kees
)
c[_] Build a fire for a man and he will be warm for
a day, but set fire to that man and he will be
warm forever! (Sun Tzu) (#443)

On Mon, 10 Dec 2007 12:07:44 +0100, Kees Nuyt wrote:

> With person_id being the primary key in attribute you would only be able
> to insert one attribute row for every person row.

That's the problem.

> Also, person_id can't
> be auto_increment, it would probably refer to the wrong person.

Ok, I just quickly hacked the schema together, think of it as pseudo SQL.

> In order to store n attributes per person you have to revise your
> schema:
>
> -- no changes for person
> create table attribute (
> person_id int(11),
> attrib_id int(11),
> name varchar(50),
> PRIMARY KEY (person_id,attrib_id),
> INDEX idx_fk_pi (person_id),
> FOREIGN KEY(person_id) references person.id
> ) ENGINE=InnoDB;
>
> You could define attrib_id as auto_increment (in which case you have to
> index it), depending on your requirements there are other options.

So except for the attribute table now having a primary key, what's
different?

> With the changed schema your will problem will be a different one.

I don't see how that changes the problem.

-Samuel

On Mon, 10 Dec 2007 11:13:44 +0000 (UTC), Samuel
<newsgroups@debain.org> wrote:

>On Mon, 10 Dec 2007 12:07:44 +0100, Kees Nuyt wrote:
>
>> With person_id being the primary key in attribute you would only be able
>> to insert one attribute row for every person row.
>
>That's the problem.
>
>> Also, person_id can't
>> be auto_increment, it would probably refer to the wrong person.
>
>Ok, I just quickly hacked the schema together, think of it as pseudo SQL.

Hm, so you just throw a random question into the group and
expect us to do the dirty work of asuming what you were
supposed to mean and correct your quick mistakes for you?

That's too easy, mister! We are willing to help you, but
you could show some respect. Our time is at least as
valuable as yours.


>> In order to store n attributes per person you have to revise your
>> schema:
>>
>> -- no changes for person
>> create table attribute (
>> person_id int(11),
>> attrib_id int(11),
>> name varchar(50),
>> PRIMARY KEY (person_id,attrib_id),
>> INDEX idx_fk_pi (person_id),
>> FOREIGN KEY(person_id) references person.id
>> ) ENGINE=InnoDB;
>>
>> You could define attrib_id as auto_increment (in which case you have to
>> index it), depending on your requirements there are other options.
>
>So except for the attribute table now having a primary key, what's
>different?

It already had a primary key, but the wrong one and I told
you why.
The other change is the removal of auto_increment columns
of the attribute table.

>> With the changed schema your will problem will be a different one.
>
>I don't see how that changes the problem.

Oh yes, it does, you can try to rewrite your query now.

>-Samuel

Good luck.
--
( Kees
)
c[_] In order to make an apple pie from scratch, you must first create
the universe. (Carl Sagan) (#10)