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 4 6373
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
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
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)
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Eric Linders |
last post by:
Hi everyone. :-)
Our site gets a ton of traffic on our contact forms, which collect the
standard information (name, address, city, state, zip, home phone,
etc.) The form validation is done with...
|
by: Aaron W. West |
last post by:
Timings... sometimes there are almost too many ways to do the same thing.
The only significant findings I see from all the below timings is:
1) Integer math is generally fastest, naturally....
|
by: my-wings |
last post by:
I've been reading about how evil Lookup fields in tables are, but I've got
to be missing something really basic. I know this subject has been covered
before, because I've just spent an hour or two...
|
by: Zachary Turner |
last post by:
I want to make a Lookup Field based on another Lookup field. In other
words, I have this table A with two fields: ID and Name, where ID is
an Autonumber and Name is a friendly name. Then I have a...
|
by: google |
last post by:
I have a database with four table. In one of the tables, I use about
five lookup fields to get populate their dropdown list. I have read
that lookup fields are really bad and may cause problems...
|
by: jon f kaminsky |
last post by:
Hi-
I've seen this problem discussed a jillion times but I cannot seem to
implement any advice that makes it work. I am porting a large project from
VB6 to .NET. The issue is using the combo box...
|
by: Lance Williams |
last post by:
How can you programatically tell if a field in a table is a lookup field? I am looping through the fields collection and I want to see if any fields are lookup fields.
Is there a property, or...
|
by: Markw |
last post by:
I think this was recently asked but I was a little lost on the example that was used so I'm reasking it with an example I can understand.
Also forgive me for such a basic question but I really am...
|
by: samdev |
last post by:
I have set up a table with a few fields that are set to
required....all work fine except the fields that are Lookup from
another table.
What am I forgetting to set?
Thanks!!
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |