Extremely simple SQL, Access, .ASP help.
January 11, 2008 9:13 AM Subscribe
How can a fool make a table in an Access database reference another table, using SQL and ASP?
I am building a new bloggy type thing. I am somewhat of a simpleton when it comes to database stuff, but usually when I muck around for a while I can get things to go my way. I am self-taught, and poorly so, so basic concepts are sometimes not present.
This thing is written in classic .asp (thanks in advance for making fun of me for that), and pulling from an access database. Let's say it's about pizza. The table I am using, "pizza", has the fields "pizzapicture", "pizzatitle" and "pizzatoppings".
pizza pizzapicture pizzatitle pizzatoppings pizza1.jpg pepperoni pie pepperoni pizza2.jpg with fish anchovy pizza3.jpg 'shrooms mushroom
It occurred to me that the toppings will pretty much always be one of a few options, so I made another table in the database called "toppings", changed the datatype in pizza.pizzatoppings to point to this new table using the "lookup wizard" in access. Toppings has three entries: "pepperoni", "anchovy", "mushroom".
toppings ID toppings 1 pepperoni 2 anchovy 3 mushrooms
So, well, how do I use this thing? When it was just one table it was easy enough for me to grab an entry, but when I use my old queries I end up with the ID# of the toppings, instead of the word "mushroom", or whatever. Hope?
Two bonus questions:
1) to accommodate the eventuality of an entry being a pepperoni-mushroom-anchovy pizza, should I add "pizzatoppings2" and "pizzatoppings3" to "pizza", or is there a way to have "pizzatoppings" contain more than one number?
2) What should I have used to search for the answer to my question? I've tried many versions and pretty much everything ends up being too broad or too deep. Surely there is a simple name for the concept that I am (reasonably unwittingly) trying to implement?
I am building a new bloggy type thing. I am somewhat of a simpleton when it comes to database stuff, but usually when I muck around for a while I can get things to go my way. I am self-taught, and poorly so, so basic concepts are sometimes not present.
This thing is written in classic .asp (thanks in advance for making fun of me for that), and pulling from an access database. Let's say it's about pizza. The table I am using, "pizza", has the fields "pizzapicture", "pizzatitle" and "pizzatoppings".
It occurred to me that the toppings will pretty much always be one of a few options, so I made another table in the database called "toppings", changed the datatype in pizza.pizzatoppings to point to this new table using the "lookup wizard" in access. Toppings has three entries: "pepperoni", "anchovy", "mushroom".
So, well, how do I use this thing? When it was just one table it was easy enough for me to grab an entry, but when I use my old queries I end up with the ID# of the toppings, instead of the word "mushroom", or whatever. Hope?
Two bonus questions:
1) to accommodate the eventuality of an entry being a pepperoni-mushroom-anchovy pizza, should I add "pizzatoppings2" and "pizzatoppings3" to "pizza", or is there a way to have "pizzatoppings" contain more than one number?
2) What should I have used to search for the answer to my question? I've tried many versions and pretty much everything ends up being too broad or too deep. Surely there is a simple name for the concept that I am (reasonably unwittingly) trying to implement?
Response by poster: You can see that tabular data rendered here
posted by dirtdirt at 9:15 AM on January 11, 2008
posted by dirtdirt at 9:15 AM on January 11, 2008
Response by poster: As it is now, yes, but I think itt would be best to plan for more than one topping.
posted by dirtdirt at 9:21 AM on January 11, 2008
posted by dirtdirt at 9:21 AM on January 11, 2008
Best answer: Well, it seems overly complicated for your purposes, but you could have a lookup table that just has pizza IDs and topping IDs, so you'd just do a "SELECT topping FROM pizza_lookup WHERE ID = (yourpizzaidhere)"
Table: Pizza
id | picture | title
Table: PizzaLookup
pizza_id | topping_id
Table: Topping
id | name
posted by atomly at 9:31 AM on January 11, 2008
Table: Pizza
id | picture | title
Table: PizzaLookup
pizza_id | topping_id
Table: Topping
id | name
posted by atomly at 9:31 AM on January 11, 2008
Best answer: In Access, you need to create a relationship between your two tables linking pizza.pizzatoppings with toppings.id
For queries, (I'll assume you're just using design view) you need to select from both tables. You can leave out the ID fields, and just have the topping name in your result set.
This assumes a pizza will only have one topping, so that brings us to bonus question 1.
What you want to do is called a "Many to many" relationship. One pizza can have many toppings, and a topping can be on multiple pizzas. The standard solution to this is to create a third table. Delete the relationship between pizza and toppings, if you created it, and create a new table called pizza_toppings. It's fields will be pizza_id and toppings_id. You will also need to add a pizza_id field to your pizza table. pizza_id and toppings_id should both be primary keys in the new table.
So, you have the following data
Pizza
Pizza_id pizzapicture pizzatitle
1 somepic.jpg hawaiian
Toppings
toppings_id toppings
1 pepperoni
2 pineapple
pizza_toppings
Pizza_id toppings_id
1 1
1 2
pizza and toppings should both have one-to-many relationships with pizza_toppings, and no relationship with each other.
Here's an example with pictures of the relationships
As for question 2... as I've said this is called a "many to many" relationship. Also, check out "one to one" and "one to many" as well as database normalization. These terms should help you with the google.
posted by utsutsu at 9:32 AM on January 11, 2008 [1 favorite]
For queries, (I'll assume you're just using design view) you need to select from both tables. You can leave out the ID fields, and just have the topping name in your result set.
This assumes a pizza will only have one topping, so that brings us to bonus question 1.
What you want to do is called a "Many to many" relationship. One pizza can have many toppings, and a topping can be on multiple pizzas. The standard solution to this is to create a third table. Delete the relationship between pizza and toppings, if you created it, and create a new table called pizza_toppings. It's fields will be pizza_id and toppings_id. You will also need to add a pizza_id field to your pizza table. pizza_id and toppings_id should both be primary keys in the new table.
So, you have the following data
Pizza
Pizza_id pizzapicture pizzatitle
1 somepic.jpg hawaiian
Toppings
toppings_id toppings
1 pepperoni
2 pineapple
pizza_toppings
Pizza_id toppings_id
1 1
1 2
pizza and toppings should both have one-to-many relationships with pizza_toppings, and no relationship with each other.
Here's an example with pictures of the relationships
As for question 2... as I've said this is called a "many to many" relationship. Also, check out "one to one" and "one to many" as well as database normalization. These terms should help you with the google.
posted by utsutsu at 9:32 AM on January 11, 2008 [1 favorite]
Hrm. If my response was too much, please feel free to ask for clarification.
posted by utsutsu at 2:23 PM on January 11, 2008
posted by utsutsu at 2:23 PM on January 11, 2008
Response by poster: I don't think it was too much, but I haven't been able to get it working correctly yet. BUT the "many to many" tip has gotten me much more close to what I need. I'll keep monkeying at it, and see where it gets me. Thanks all!
posted by dirtdirt at 12:18 PM on January 12, 2008
posted by dirtdirt at 12:18 PM on January 12, 2008
Response by poster: Was the third table in Atomly and Utsutsu's design to facilitate more than one topping per pizza? Because I do not understand the benefit of the third table. It seems to me like one table with a field that points to a value in a second table should be fine. But, of course, since I cannot get it to work, what the hell do I know? Heh.
posted by dirtdirt at 12:22 PM on January 12, 2008
posted by dirtdirt at 12:22 PM on January 12, 2008
Yes, the third table is to allow the "many to many", which allows more than one topping to be on a pizza AND allows a topping to be on more than one pizza.
If the pizza table simply references an id in the toppings table, you only have one topping per pizza, or you need to add a field for each topping effectively limiting the max toppings per pizza.
There are some other ways to work around this. The toppings field in the pizza table could be a string.. a series of numbers each referencing a topping... but this will complicate anything you want to do with this, and I think you would need to do some VBA coding to make it work right.
Another choice, would be to make the primary key in the pizza table to be composed of BOTH pizza_id and toppings_id, and then you have multiple rows to describe a single pizza.
Both of these alternatives are not necessarily best practices, but they can get the job done.
posted by utsutsu at 3:57 PM on January 12, 2008
If the pizza table simply references an id in the toppings table, you only have one topping per pizza, or you need to add a field for each topping effectively limiting the max toppings per pizza.
There are some other ways to work around this. The toppings field in the pizza table could be a string.. a series of numbers each referencing a topping... but this will complicate anything you want to do with this, and I think you would need to do some VBA coding to make it work right.
Another choice, would be to make the primary key in the pizza table to be composed of BOTH pizza_id and toppings_id, and then you have multiple rows to describe a single pizza.
Both of these alternatives are not necessarily best practices, but they can get the job done.
posted by utsutsu at 3:57 PM on January 12, 2008
This thread is closed to new comments.
posted by dirtdirt at 9:13 AM on January 11, 2008