Query Help .NET
-
I have a company table and a product table and i am tring to get informaion out of both of them. However, I Guess you know that i did not achieve my goal that is why i am here for help. I can only get the CompantID AND the CompanyName to return.
Looking for the answer here.
Thanks!
ALTER PROCEDURE
GetBlindCompany
AS
SELECT
Company.CompanyID, Company.CompanyName, Product.ModelName, Product.ProductImage, Product.ProductDescription
FROM
Company INNER JOIN
Product ON Company.CompanyID = Product.CompanyID
GROUP BY
Company.CompanyID
Dam again!
-
Well. Are you sure that SP runs?
Semantically it is incorrect...
..and maybe this question would have been better off if it appeared in the T-SQL section?
//Hanslindgren
-
Query will fail unless all columns listed in select are not in group by. You wouldn't be getting anything but an error
SELECT Company.CompanyID, Company.CompanyName, Product.ModelName, Product.ProductImage, Product.ProductDescription
FROM Company INNER JOIN
Product ON Company.CompanyID = Product.CompanyID
GROUP BY Company.CompanyID, Company.CompanyName, Product.ModelName, Product.ProductImage, Product.ProductDescription
Regards,
gova -
...but if you do include all columns in your GROUP BY, there is not much point to the query. You could be better off doing a DISTINCT as it would not hint as much on any need for GROUPing and a malformed query.
-
Hanslindgren could you please give an example?
Thanks
Dam again!
-
Hmm.. Well, for example:
ALTER PROCEDURE GetBlindCompany
AS
SELECT DISTINCT
Company.CompanyID, Company.CompanyName, Product.ModelName, Product.ProductImage, Product.ProductDescription
FROM
Company INNER JOIN Product ON Company.CompanyID = Product.CompanyID
Or maybe you meant an example of:
ALTER PROCEDURE GetBlindCompany
AS
SELECT
Company.CompanyID, Company.CompanyName, Product.ModelName, Product.ProductImage, Product.ProductDescription, COUNT(*)
FROM
Company INNER JOIN Product ON Company.CompanyID = Product.CompanyID
GROUP BY Company.CompanyID, Company.CompanyName, Product.ModelName, Product.ProductImage, Product.ProductDescription
-
Currently i am creating these procedures in vb.net; however, i do have sql2000 enterprise manager but i find it more difficult to create stored procedures. Do you have any suggestions for creating procedures in the Enterprise manager? "Even this so- called wizard is really not to helpful"
Thank you!
Dam again!
-
Did the examples help you?
I would suggest creating your SPs through SQL Query Analyzer
Wizards can be good, but they are not so flexible...
Learn T-SQL and create them from scratch! Or use a wizard and customize the output... But for SPs I guess doing them from scratch is much faster.
-
OK.
Thanks.
Dam again!
-
I have added a Parameter the this query and it is giving me a ambigious column name error. What am i missing here?
============I added the @IDCompany==================
ALTER PROCEDURE GetBlindCompanyInformation
@IDCompany int
AS
SELECT DISTINCT Company.CompanyID, Company.CompanyName, Product.ModelName, Product.ProductImage, Product.ProductDescription
FROM Company INNER JOIN
Product ON Company.CompanyID = Product.CompanyID
WHERE CompanyID = @IDCompany
Dam again!
-
That you havn't specified WHICH of the two CompanyID columns you intend with your WHERE clause. Remember that you have two. One in each table. (You need this even if you have your ON clause that actually is showing that they have to be equal)
-
Got it!
ALTER PROCEDURE GetBlindCompanyInformation
@IDCompany int
AS
SELECT DISTINCT Company.CompanyID, Company.CompanyName, Product.ModelName, Product.ProductImage, Product.ProductDescription
FROM Company INNER JOIN
Product ON Company.CompanyID = Product.CompanyID
WHERE Company.CompanyID = @IDCompany
Dam again!
-
=Table============
CREATE TABLE [Metro 2" Blind Size Price] (
[BlindSizePriceID] [int] NOT NULL ,
[MaxHeight] [decimal](9, 2) NOT NULL ,
[MaxWidth] [int] NOT NULL ,
[Price] [money] NOT NULL ,
[CompanyID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
CONSTRAINT [PK_SizePrice] PRIMARY KEY NONCLUSTERED
(
[BlindSizePriceID]
 
ON [PRIMARY]) ON [PRIMARY]
GO
=======Query=============
CREATE PROCEDURE [dbo].[usp_GetPrice]
@WidthRequested decimal(9,2),
@HeightRequested decimal(9,2),
@ProductID int
AS
-- get the proper price for the width, height and product selected
SELECT MIN([Price]) AS Price
FROM [Samples].[dbo].[SizePrice]
WHERE MaxWidth>=@WidthRequested AND MaxHeight>=@HeightRequested AND ProductID=@ProductID GROUP BY ProductID
Dam again!
-
Seems nice but I see no correlation between the procedure and the table DDL you have posted...
It is also considered to be best practice to avoid using the space character or characters like "
You would probably find it best in the future if you stick with SQL Server objects with just alphanumeric characters...
-
I post the topic question above so that i know the thread will post.
Some time back i was given this create table a query for a solution to price my blinds by product.
here is my question now. I can have many companys that make the same product at different prices; therefore i would like to create a base table (When i can, i am sure that i will have many base tables for different products)
EXAMPLE: CompanyA sells 34 width x 75 Length 2" Wood blinds for $54.65, and CompanyB sells the same product and size for $10.00 more. What can i add to my query that will allow me to use the base table for one company (The one that charges the least) for all over the other companys at a percentage highers.
Since CompanyB is $10.00 higher can i add some sort of % higher to the query?
Thanks....
Dam again!
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply