Any SQL guru's out there?

[blacksunshine]

Any SQL guru's out there?

[BigL]

Was back when I was coding but since I've been in management i'm a little rusty. What do you need help with?

[scotton]

I'm more of a gu than a guru, but I might be able to help. Are you talking about SQL the language or MSSQL the database?

[HuntForHorns]

I was back in the day. Funnest time of my whole career.

[blacksunshine]

ok, lets see if I can make this easy to understand...

this is all access so pretty much standard SQL will work, well, access db on a 2k3 server and using ASP to query....if I can get the basics I can generally hack out any minor issues...it has just been forever since I used that one brain cell..

I am tayloring the example to archery to make it easy.

so say I have a database with a table of bow manufacturers and tables for each manufacturer containing bow models...

table.bowmfgrs
bowmfgrs.name
bowmfgrs.city
bowmfgrs.state
bowmfgrs.compound

for the above you would just have the mfgrs name and then a Y under the bowmfgrs.compound field if they have made a compound bow

then you have the mfgrs tables, the name of each of these tables is the same as the bowmfgrs.name field in the first table.

(bowmfgrs.name1)
(bowmfgrs.name1).modelname
(bowmfgrs.name1).year
(bowmfgrs.name1).color

(bowmfgrs.name2)
(bowmfgrs.name2).modelname
(bowmfgrs.name2).year
(bowmfgrs.name2).color

(bowmfgrs.name3)
(bowmfgrs.name3).modelname
(bowmfgrs.name3).year
(bowmfgrs.name3).color

ok, so say I want to have a query that will return the results for all the red bows but I want the mfg name, model name, year and color all returned for only the red bows.

I have the query down for finding the red bows for each specified bowmfgr but want to try and use a query of the mfr database that uses it's returned results of mfgrs with compounds to then show only the red ones...ie, show me all the mfrs that made red compound bows, show mfr, modelname, year and color...

make sense?

[Bullydog]

ok, lets see if I can make this easy to understand...

this is all access so pretty much standard SQL will work, well, access db on a 2k3 server and using ASP to query....if I can get the basics I can generally hack out any minor issues...it has just been forever since I used that one brain cell..

I am tayloring the example to archery to make it easy.

so say I have a database with a table of bow manufacturers and tables for each manufacturer containing bow models...

table.bowmfgrs
bowmfgrs.name
bowmfgrs.city
bowmfgrs.state
bowmfgrs.compound

for the above you would just have the mfgrs name and then a Y under the bowmfgrs.compound field if they have made a compound bow

then you have the mfgrs tables, the name of each of these tables is the same as the bowmfgrs.name field in the first table.

(bowmfgrs.name1)
(bowmfgrs.name1).modelname
(bowmfgrs.name1).year
(bowmfgrs.name1).color

(bowmfgrs.name2)
(bowmfgrs.name2).modelname
(bowmfgrs.name2).year
(bowmfgrs.name2).color

(bowmfgrs.name3)
(bowmfgrs.name3).modelname
(bowmfgrs.name3).year
(bowmfgrs.name3).color

ok, so say I want to have a query that will return the results for all the red bows but I want the mfg name, model name, year and color all returned for only the red bows.

I have the query down for finding the red bows for each specified bowmfgr but want to try and use a query of the mfr database that uses it's returned results of mfgrs with compounds to then show only the red ones...ie, show me all the mfrs that made red compound bows, show mfr, modelname, year and color...

make sense?

Let me pour another Crown....Im almost there...

[scotton]

how about this:
table.bowmfgrs
bowmfgrs.id
bowmfgrs.name
bowmfgrs.city
bowmfgrs.state
bowmfgrs.compound

table.bow
bow.mfgrs_id
bow.model_name
bow.color

Then you tie them together by the manf id. So you want all red bows, you would say: select bow.*, bowmfgrs.name from bow inner join bowmfgrs on bow.mfgrs_id = bowmfgrs.id where bow.color = 'red'

or to get only red bear bows:
select bow.*, bowmfgrs.name from bow inner join bowmfgrs on bow.mfgrs_id = bowmfgrs.id where bow.color = 'red' and bowmfgrs.name = 'Bear'

or only red bows made in texas:
select bow.*, bowmfgrs.name from bow inner join bowmfgrs on bow.mfgrs_id = bowmfgrs.id where bow.color = 'red' and bmfgrs.state = 'TX'

If you set up the tables the way you want, you will have problems down the road.

[scotton]

if you don't like the inner joins, you can change it to:
select bow.*, bowmfgrs.name from bow, bowmfgrs where bow.mfgrs_id = bowmfgrs.id and bow.color = 'red'

I can't recall if Access likes inner joins or not.

[scotton]

Also, if you want to sort it by manf and then model name, stick an order by on the end:
select bow.*, bowmfgrs.name from bow, bowmfgrs where bow.mfgrs_id = bowmfgrs.id and bow.color = 'red' order by bowmfgrs.name, bow.model_name

and depending on your input, you might need to make the color lower case so you don't get case issues, like:

select bow.*, bowmfgrs.name from bow, bowmfgrs where bow.mfgrs_id = bowmfgrs.id and lower(bow.color) = 'red' order by bowmfgrs.name, bow.model_name

or change the color to a code, like red = 1, blue = 2, etc. The database will search easier if you make as much of it integers as possible, but if it's small enough for Access it probably won't matter.

[blacksunshine]

how about this:
table.bowmfgrs
bowmfgrs.id
bowmfgrs.name
bowmfgrs.city
bowmfgrs.state
bowmfgrs.compound

table.bow
bow.mfgrs_id
bow.model_name
bow.color

Then you tie them together by the manf id. So you want all red bows, you would say: select bow.*, bowmfgrs.name from bow inner join bowmfgrs on bow.mfgrs_id = bowmfgrs.id where bow.color = 'red'

or to get only red bear bows:
select bow.*, bowmfgrs.name from bow inner join bowmfgrs on bow.mfgrs_id = bowmfgrs.id where bow.color = 'red' and bowmfgrs.name = 'Bear'

or only red bows made in texas:
select bow.*, bowmfgrs.name from bow inner join bowmfgrs on bow.mfgrs_id = bowmfgrs.id where bow.color = 'red' and bmfgrs.state = 'TX'

If you set up the tables the way you want, you will have problems down the road.

I was waiting on someone to say that....but, picture 600 mfr tables with anywhere from 1 to 150 records in each....would you still add that extra id column to each of the 600?

around 12,000 total records or so

[scotton]

No, I would have one table instead of 600. 600 tables with the same columns is bad database design. Combine them all into one table and add a column for manufacturer name.

[blacksunshine]

No, I would have one table instead of 600. 600 tables with the same columns is bad database design. Combine them all into one table and add a column for manufacturer name.

I knew you were going to tell me that.... ;(

will have to chew on that one for a while....the actual combining of the data will not be that big of a deal, just menial but then I will pull muscles trying to redo all the existing queries/asp pages....

ughhh

I guess this is what happens when you try and teach yourself sql by way of asp pages...10 years ago...

I would like to convert it over to mysql/php but still need to find an on the fly watermarker...

[scotton]

Picture a filing cabinet. Do you want a drawer for each manufacturer, or a drawer for all manufacturers with a folder inside for each manufacturer? It's a lot easier to add folders than drawers. Then you can have a drawer for bows and a drawer for arrows and let the magic of computers link them all together so you can search for what you want from each drawer. It's called database normalization. The idea is that you don't want to have the same data repeated over and over. So instead of having one big table with a row for every bow including the manf. name and address, you split out the bows in one table and the manfs in another and link them with a key. If you wanted to list the recommended arrows for each bow, you wouldn't list each arrow over and over in the bow table, you would make a table for each arrow and a lookup table that would have the bow id and arrow id for each arrow recommended for each bow. That way you can do a one to many relationship. In our filing cabinet example, this would be like putting a green sticky tab on each bow folder that shoots a 2513 well, and a red tab on each bow that shoots a 2117 well. So bow folder would have many stickys, some would have a few.

[scotton]

I knew you were going to tell me that.... ;(

will have to chew on that one for a while....the actual combining of the data will not be that big of a deal, just menial but then I will pull muscles trying to redo all the existing queries/asp pages....

ughhh

An old boss used to say "It's not a problem, it's an opportunity". Glad I don't work for that jerk anymore. Way too many opportunities.

You can find a way to make it work with your given structure, but it will be very difficult every time you want to change a query. I would rip off the bandaid and do it right and then it will be much easier for the future of your project. You should be able to write a query to populate your manf table, assuming you have the names of all the manfs in a table and the names match the table names. Then you just have to deal with the ASP code.

[scotton]

by the way, what is an on the fly watermarker? Never heard that expression.