Finding lookup fields in MySQL

[Rob Allen]

I've got a lookup table for a lookup table and can't work out the SQL to
do a search. Can anyone help?

As an example, consider the following:

Table 1 - books:
fields: book_id, price, title, author
data:
1, 2.99, 'Life', 'A. Slug'
2, 4.99, 'The Universe', 'A. Planet'

Table 2 - book_fields:
fields: field_id, field_name
data:
1, 'cover_colour'
2, 'cover_type'

Table 3 - book_field_look up:
data: book_id, field_id, field_value
1, 1, 'blue'
1, 2, 'hardback'
2, 1, 'blue'
2, 2, 'softback'
How do I do all hardback books that are blue? I'm sure that the answer
is simple, but have a block today :(

--
Rob Allen

[Rob Allen]

In message <Mz************ **@rob.mustardh ouse.com>, Rob Allen
<ro***@the-allens.net> writes

How do I do all hardback books that are blue? I'm sure that the answer
is simple, but have a block today :(

Umm.. That should be "How do I find all the hardback books that are
blue?"

--
Rob Allen

[s a n j a y]

so do you need the sql?

something like

select * from table1, table3 where table1.bood_id = table3.book_id and
table3.field_va lue like "%hardback% ';
"Rob Allen" <ro***@the-allens.net> wrote in message
news:Mz******** ******@rob.must ardhouse.com...

I've got a lookup table for a lookup table and can't work out the SQL to
do a search. Can anyone help?

As an example, consider the following:

Table 1 - books:
fields: book_id, price, title, author
data:
1, 2.99, 'Life', 'A. Slug'
2, 4.99, 'The Universe', 'A. Planet'

Table 2 - book_fields:
fields: field_id, field_name
data:
1, 'cover_colour'
2, 'cover_type'

Table 3 - book_field_look up:
data: book_id, field_id, field_value
1, 1, 'blue'
1, 2, 'hardback'
2, 1, 'blue'
2, 2, 'softback'
How do I do all hardback books that are blue? I'm sure that the answer
is simple, but have a block today :(

--
Rob Allen

[Andy Hassall]

On Mon, 11 Aug 2003 12:35:55 +0000 (UTC), Rob Allen <ro***@the-allens.net>
wrote:

I've got a lookup table for a lookup table and can't work out the SQL to
do a search. Can anyone help?

As an example, consider the following:

Table 1 - books:
fields: book_id, price, title, author
data:
1, 2.99, 'Life', 'A. Slug'
2, 4.99, 'The Universe', 'A. Planet'

Table 2 - book_fields:
fields: field_id, field_name
data:
1, 'cover_colour'
2, 'cover_type'

Table 3 - book_field_look up:
data: book_id, field_id, field_value
1, 1, 'blue'
1, 2, 'hardback'
2, 1, 'blue'
2, 2, 'softback'
How do I do all hardback books that are blue? I'm sure that the answer
is simple, but have a block today :(

Join to your books table with your field tables twice, once for type, once for
colour, something like:

SELECT b.book_id
FROM books b,
book_fields bf_colour, book_field_look up bfl_colour,
book_fields bf_type, book_field_look up bfl_type
WHERE bf_colour.field _name = 'cover_colour'
AND bf_colour.field _id = bfl_colour.fiel d_id
AND bfl_colour.fiel d_value = 'blue'
AND bfl_colour.book _id = b.book_id
AND bf_type.field_n ame = 'cover_type'
AND bf_type.field_i d = bfl_type.field_ id
AND bfl_type.field_ value = 'hardback'
AND bfl_type.book_i d = b.book_id

--
Andy Hassall (an**@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Rob Allen]

In message <28************ *************** *****@4ax.com>, Andy Hassall
<an**@andyh.co. uk> writes

Join to your books table with your field tables twice, once for type,
once for colour, something like:

Ah... You can multiply join the same table! So for N search criteria, I
need to join N times...

Thanks !

--
Rob Allen