p2p.wrox.com: Archives - asp

asp_databases thread: SV: Re: SV: Re: SV: Re: SV: Re: SV: very important

Message #1 by =?us-ascii?Q?Hakan_Frennesson?= <hakan@c...> on Tue, 19 Feb 2002 10:17:30 +0100

Hi!



1: SELECT tbl_cat.Catname, tbl_subcat.Subcatname, tbl_accessory.Accname,

tbl_accessory.Weight, tbl_accessory.Color

FROM tbl_cat INNER JOIN tbl_subcat ON tbl_cat.CatID = tbl_subcat.CatID INNER

JOIN tbl_accessory ON tbl_subcat.SubcatID = tbl_accessory.SubcatID

WHERE tbl_cat.Catname='something' AND tbl_subcat.Subcatname='somethingelse'



2:



Here you only insert in one table:

Insert Into tbl_accessory (Accname, SubcatID, Weight, Color) Values

('somename',somecatID,someweight,'somecolor')



In that form you have a drop down menu displaying your subcatnames, but the

subcatID is inserted into the table.



For basic SQL please have a look at:

http://www.geocities.com/SiliconValley/Vista/2207/sql1.html



Hakan



-----Ursprungligt meddelande-----

Fran: arshad siddiqui [mailto:ash_arshad@y...]

Skickat: den 19 februari 2002 08:45

Till: ASP Databases

Amne: [asp_databases] Re: SV: Re: SV: Re: SV: Re: SV: very important





Hi Hakan,

Thanks for the design.The design is really helpful.I

know you must be tired of my question.actually I have

never worked on the related tables.

I visited the URL'srovided by you but only theoretical

basics are there.

One last question I wanna ask you.Can you please give

me the sql queries for:

1. To display the

Catname,Subcatname,Accessoryname,color,weight,price

where Catname=something and subcatname=something.

2.An insert sql statement to insert into the database.

insert into database

CatId,Catname,Subcatid,subcatname,AccessoryId,Accessoryname,color,weight,pri

ce

etc.



These two queries will solve my problem.

Thank you

Regards

--Arshad--

--- Hakan_Frennesson <hakan@c...> wrote:

> Hi!

>

> I am not sure how much you know about the basics of

> relational database

> design/implementation, but I feel there is lot you

> need to understand before

> you go ahead.

>

> Read my previous posts and think about WHERE you

> store data.

>

> In tbl_accessory you store data about each Accessory

> and which subcat it

> belongs to.

> Since every subcat is related to a cat, you don?t

> need to store the catID in

> this table.

>

> You have to create the tables Category and

> Subcategory and fill these tables

> with valid information. I give you one example (if a

> subcat has to be

> related to a cat):

>

> In tbl_cat

>

> CatID	Catname

> 1	Engine

> 2	Lights

> 3	Chassi

>

> In tbl_subcat

> SubcatID	Subcatname	CatID

> 1		Valves	1

> 2		Sparkplugs	1

> 3		Bulbs		2

> 4		Bumper	3

> etc

>

> Then in the form you have fields that insert date in

> ONE table, ie

> tbl_accessory

> If you think about it, what happens if the

> information about which subcat an

> accessoire belongs to never gets stored in

> tbl_accessory? You want this data

> to be stored in another table. That way you will

> never know what what subcat

> each and every accessoire belong to, because you

> never store that

> information. There is lot more to be said but please

> read more about

> relational database design and SQL. This is a good

> start:

> http://www.datatexcg.com/

> http://sqlcourse.com/

> Microsoft link (watch for line wrapping)

>

http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fwebcasts%2F

> wc060600%2Fwcblurb060600%2Easp

>

> Hakan

>

>

> -----Ursprungligt meddelande-----

> Fran: arshad siddiqui [mailto:ash_arshad@y...]

> Skickat: den 18 februari 2002 05:57

> Till: ASP Databases

> Amne: [asp_databases] Re: SV: Re: SV: Re: SV: very

> important

>

>

> Hi,

> Thanks for your help.

> Its a great help for me as I was too much confused

> for

> my database design part.

> Can you please tell me how to insert in all the

> three

> tables.Suppose I want to insert

>

AccessoryId,CategoryID,CategoryName,SubcategoryID,SubcategoryName,Color,weig

> ht

> etc.because CategoryID and CategoryName is to be

> inserted in tbl_Categry,SubCategoryID and

> SubCategoryName in tbl_Subcategory,and rest in

> tbl_accessory.

> I know I am taking too much of your time but your

> suggestions are very helpful for me.

> Thanks

> Regards

> --Arshad--

>

>

>

>

> --- Hakan_Frennesson <hakan@c...> wrote:

> > Hi!

> >

> > Well,, this design would perfectly achieve what

> you

> > want:

> >

> > tbl_category:

> > CatID, Catname

> >

> > tbl_subcategory:

> > SubcatID, Subcatname, CatID

> >

> > tbl_accessory:

> > AccID, AccName, SubcatID, Color, Weight, etc

> >

> > Relate CatID in tbl_category with CatID in

> > tbl_subcategory (one-to-many relation)

> > and SubcatID in tbl_subcategory with SubcatID in

> > tbl_accessory (one-to-many relation)

> >

> > First you list your categories which are linked.

> > Clicking a link takes you to all related

> > subcategories

> > Clicking a subcategory takes you to all related

> > accessoires

> >

> > I can?t see any other way of doing it. What do you

> > mean wouldn?t work with

> > this?

> >

> > Hakan

> >

> >

> >

> >

> > -----Ursprungligt meddelande-----

> > Fran: arshad siddiqui

> [mailto:ash_arshad@y...]

> > Skickat: den 17 februari 2002 06:09

> > Till: ASP Databases

> > Amne: [asp_databases] Re: SV: Re: SV: very

> important

> >

> >

> > Hi,

> > Thanks for your invaluable suggestion.

> > Actually I want to display all the accessories

> > related

> > to a particular category and subcategory.That is

> > fist

> > of all the categories shuld be displayed and when

> > you

> > click on one category all of its sub categories

> > should

> > come then you can click on any of the subcategory

> to

> > get all the accessories.

> > I wanna make an admin page also so that in that I

> > enter the category,subcategory and all the details

> > of

> > accessory and submit it.In that case you need not

> > have

> > to make entry into the database manually.

> > You can get a beeter idea what I want to do by

> going

> > to the following URL:

> > http://store.summitracing.com

> > I think now you must get the idea about what

> exactly

> > I

> > need to know.

> > Thanks again

> > Regards

> > --Arshad-

> >

> >

>

=====================================================

> > --- Hakan_Frennesson <hakan@c...> wrote:

> > > Hi!

> > >

> > > If a subcat is related to a category you can do

> > > this:

> > >

> > > tbl_category:

> > > CatID, Catname

> > >

> > > tbl_subcategory:

> > > SubcatID, Subcatname, CatID

> > >

> > > tbl_accessory:

> > > AccID, AccName, SubcatID, Color, Weight, etc

> > >

> > > Relate CatID in tbl_category with CatID in

> > > tbl_subcategory (one-to-many

> > > relation)

> > > and SubcatID in tbl_subcategory with SubcatID in

> > > tbl_accessory (one-to-many

> > > relation)

> > >

> > > I should really know more about what you want to

> > > achieve with your database

> > > to design it properly, but my first solution is

> > > easiest and will probably do

> > > the job for you.

> > >

> > > The people that fill out the form, are they

>

=== message truncated ===





__________________________________________________

Do You Yahoo!?

Yahoo! Sports - Coverage of the 2002 Olympic Games

http://sports.yahoo.com






$subst('Email.Unsub').

Message #2 by arshad siddiqui <ash_arshad@y...> on Tue, 19 Feb 2002 02:22:53 -0800 (PST)

Hi,

Thanks

First query is ok.its working fine.

But in second query what if I want to insert a new

category,new subcategory and accessory details will it

work.because in this query there is no mention of

category table.

Your help is very important for me.as I am not well

equipped with database expertise and the things I need

is urgent otherwise I would have consulted some books.

Thanks

Regards

--Arshad--









--- Hakan_Frennesson <hakan@c...> wrote:

> Hi!

> 

> 1: SELECT tbl_cat.Catname, tbl_subcat.Subcatname,

> tbl_accessory.Accname,

> tbl_accessory.Weight, tbl_accessory.Color

> FROM tbl_cat INNER JOIN tbl_subcat ON tbl_cat.CatID

> = tbl_subcat.CatID INNER

> JOIN tbl_accessory ON tbl_subcat.SubcatID 

> tbl_accessory.SubcatID

> WHERE tbl_cat.Catname='something' AND

> tbl_subcat.Subcatname='somethingelse'

> 

> 2:

> 

> Here you only insert in one table:

> Insert Into tbl_accessory (Accname, SubcatID,

> Weight, Color) Values

> ('somename',somecatID,someweight,'somecolor')

> 

> In that form you have a drop down menu displaying

> your subcatnames, but the

> subcatID is inserted into the table.

> 

> For basic SQL please have a look at:

>

http://www.geocities.com/SiliconValley/Vista/2207/sql1.html

> 

> Hakan

> 

> -----Ursprungligt meddelande-----

> Fran: arshad siddiqui [mailto:ash_arshad@y...]

> Skickat: den 19 februari 2002 08:45

> Till: ASP Databases

> Amne: [asp_databases] Re: SV: Re: SV: Re: SV: Re:

> SV: very important

> 

> 

> Hi Hakan,

> Thanks for the design.The design is really helpful.I

> know you must be tired of my question.actually I

> have

> never worked on the related tables.

> I visited the URL'srovided by you but only

> theoretical

> basics are there.

> One last question I wanna ask you.Can you please

> give

> me the sql queries for:

> 1. To display the

> Catname,Subcatname,Accessoryname,color,weight,price

> where Catname=something and subcatname=something.

> 2.An insert sql statement to insert into the

> database.

> insert into database

>

CatId,Catname,Subcatid,subcatname,AccessoryId,Accessoryname,color,weight,pri

> ce

> etc.

> 

> These two queries will solve my problem.

> Thank you

> Regards

> --Arshad--

> --- Hakan_Frennesson <hakan@c...> wrote:

> > Hi!

> >

> > I am not sure how much you know about the basics

> of

> > relational database

> > design/implementation, but I feel there is lot you

> > need to understand before

> > you go ahead.

> >

> > Read my previous posts and think about WHERE you

> > store data.

> >

> > In tbl_accessory you store data about each

> Accessory

> > and which subcat it

> > belongs to.

> > Since every subcat is related to a cat, you don?t

> > need to store the catID in

> > this table.

> >

> > You have to create the tables Category and

> > Subcategory and fill these tables

> > with valid information. I give you one example (if

> a

> > subcat has to be

> > related to a cat):

> >

> > In tbl_cat

> >

> > CatID	Catname

> > 1	Engine

> > 2	Lights

> > 3	Chassi

> >

> > In tbl_subcat

> > SubcatID	Subcatname	CatID

> > 1		Valves	1

> > 2		Sparkplugs	1

> > 3		Bulbs		2

> > 4		Bumper	3

> > etc

> >

> > Then in the form you have fields that insert date

> in

> > ONE table, ie

> > tbl_accessory

> > If you think about it, what happens if the

> > information about which subcat an

> > accessoire belongs to never gets stored in

> > tbl_accessory? You want this data

> > to be stored in another table. That way you will

> > never know what what subcat

> > each and every accessoire belong to, because you

> > never store that

> > information. There is lot more to be said but

> please

> > read more about

> > relational database design and SQL. This is a good

> > start:

> > http://www.datatexcg.com/

> > http://sqlcourse.com/

> > Microsoft link (watch for line wrapping)

> >

>

http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fwebcasts%2F

> > wc060600%2Fwcblurb060600%2Easp

> >

> > Hakan

> >

> >

> > -----Ursprungligt meddelande-----

> > Fran: arshad siddiqui

> [mailto:ash_arshad@y...]

> > Skickat: den 18 februari 2002 05:57

> > Till: ASP Databases

> > Amne: [asp_databases] Re: SV: Re: SV: Re: SV: very

> > important

> >

> >

> > Hi,

> > Thanks for your help.

> > Its a great help for me as I was too much confused

> > for

> > my database design part.

> > Can you please tell me how to insert in all the

> > three

> > tables.Suppose I want to insert

> >

>

AccessoryId,CategoryID,CategoryName,SubcategoryID,SubcategoryName,Color,weig

> > ht

> > etc.because CategoryID and CategoryName is to be

> > inserted in tbl_Categry,SubCategoryID and

> > SubCategoryName in tbl_Subcategory,and rest in

> > tbl_accessory.

> > I know I am taking too much of your time but your

> > suggestions are very helpful for me.

> > Thanks

> > Regards

> > --Arshad--

> >

> >

> >

> >

> > --- Hakan_Frennesson <hakan@c...> wrote:

> > > Hi!

> > >

> > > Well,, this design would perfectly achieve what

> > you

> > > want:

> > >

> > > tbl_category:

> > > CatID, Catname

> > >

> > > tbl_subcategory:

> > > SubcatID, Subcatname, CatID

> > >

> > > tbl_accessory:

> > > AccID, AccName, SubcatID, Color, Weight, etc

> > >

> > > Relate CatID in tbl_category with CatID in

> > > tbl_subcategory (one-to-many relation)

> > > and SubcatID in tbl_subcategory with SubcatID in

> > > tbl_accessory (one-to-many relation)

> > >

> > > First you list your categories which are linked.

> > > Clicking a link takes you to all related

> > > subcategories

> > > Clicking a subcategory takes you to all related

> > > accessoires

> > >

> > > I can?t see any other way of doing it. What do

> you

> > > mean wouldn?t work with

> > > this?

> > >

> > > Hakan

> > >

> > >

> > >

> > >

> 

=== message truncated ===





__________________________________________________

Do You Yahoo!?

Yahoo! Sports - Coverage of the 2002 Olympic Games

http://sports.yahoo.com

Return to Index

P2P Archives