By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,830 Members | 2,276 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,830 IT Pros & Developers. It's quick & easy.

Finding lookup fields in MySQL

P: n/a
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_lookup:
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
Jul 16 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
In message <Mz**************@rob.mustardhouse.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
Jul 16 '05 #2

P: n/a
so do you need the sql?

something like

select * from table1, table3 where table1.bood_id = table3.book_id and
table3.field_value like "%hardback%';
"Rob Allen" <ro***@the-allens.net> wrote in message
news:Mz**************@rob.mustardhouse.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_lookup:
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

Jul 16 '05 #3

P: n/a
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_lookup:
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_lookup bfl_colour,
book_fields bf_type, book_field_lookup bfl_type
WHERE bf_colour.field_name = 'cover_colour'
AND bf_colour.field_id = bfl_colour.field_id
AND bfl_colour.field_value = 'blue'
AND bfl_colour.book_id = b.book_id
AND bf_type.field_name = 'cover_type'
AND bf_type.field_id = bfl_type.field_id
AND bfl_type.field_value = 'hardback'
AND bfl_type.book_id = 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)
Jul 16 '05 #4

P: n/a
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
Jul 16 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.