Need very basic Microsoft SQL help (homework related)

[NYCfixie]

Hi all-

So I am taking a "Data Management Concepts and Database Administration" class using Microsoft SQL and so far it has been great. This week, I hit a wall once we started creating functions probably because I took programming more than 20 years ago and do not have a database background. If you can help me with my homework (no it is not cheating because I am an adult paying a fortune for grad school) I would really appreciate it. I have read the class text, watched the videos, checked a few online resources including SQL Tutorial but am stuck and cannot make it work; I assume that I am missing something really simple in the syntax and/or order of using SELECT, WHERE, HAVING, Aggregate functions, and parameters.

If this is easier by email or phone, please PM me and I will send you my contact info.

We are using a simple relational DB:

VENDOR
- VendorID (PK)
- VendorName

PRODUCT
- ProductID (PK)
- ProductName
- ProductDescription
- QtyOnHand

VENDORPRODUCT
- VendorProductID (PK)
- Cost
- Product.ProductID (FK)
- Vendor.VendorID (FK)



Here is what I need to complete:

1) Code and execute the statement to create a function called PreferredVendor that accepts a product id parameter as an input and returns the Vendor ID of the Vendor with the lowest cost in VENDORPRODUCT.

2) Code and execute the select statement that returns the ProductName, ProductDescription, QtyOnHand, and the preferred VendorID (use your function).



For #1, I was trying to use a SELECT statement to get the VendorID and using a WHERE clause to filter by MIN(Cost) but could not make it work. Again, I think I am mixing up the use and syntax of the statements.

For #2, I thought I could use a SELECT and JOIN statement for ProductName, ProductDescription, QtyOnHand but since I cannot complete #1 I can't use my function in this answer.


Any help would be greatly appreciated.

[roseyscot]

I can't help you but I would love to take a class like this to supplement my job skills. I'm an implementation consultant, but work closely with interface engineers and would benefit from basic SQL skills...

[xjoex]

NYCFixie - The first part should be really simple like this, I haven't used SQLServer in a few years but...

select Vendor.VendorID from VENDORPRODUCT where Product.ProductID = ? order by cost limit 1


Rosey - You can take a distance class from me, you'll learn fundamentals of SQL design and then connect a PHP front end to it. I'll give you a few cloud machines. I teach it every semester. Of course it's security related, but send me a PM if you want more info.

-Joe

[NYCfixie]

thanks but does not seem to work.

[jscott]

Joe is on the right track, but T-SQL uses non-ISO syntax for row limits:

SELECT TOP 1 Vendor.VendorID
FROM VendorProduct
WHERE Product.ProductID = @arg
ORDER BY Cost

Should do it. Of course you have to wrap that in the CREATE FUNCTION jazz and I'm assuming your product ID parameter is named @arg.

Using MIN(Cost) feels intuitively right but actually is the long way around. You can do it, but it would result in code that's much harder to read.

I PMed you my contact info if you'd like to discuss further.

[NYCfixie]

This looks promising. I took a break for dinner so I will try this later and report back.

I'll try the select statement first with a fixed productID and if it works I can wrap the function around it with parameters.

Thanks to you and Joe for the help as I only have until 1159pm EST today to submit it.

Joe is on the right track, but T-SQL uses non-ISO syntax for row limits:

SELECT TOP 1 Vendor.VendorID
FROM VendorProduct
WHERE Product.ProductID = @arg
ORDER BY Cost

Should do it. Of course you have to wrap that in the CREATE FUNCTION jazz and I'm assuming your product ID parameter is named @arg.

Using MIN(Cost) feels intuitively right but actually is the long way around. You can do it, but it would result in code that's much harder to read.

I PMed you my contact info if you'd like to discuss further.

[NYCfixie]

I sent you a hangout invite

I got this far but received an error stating that "SELECT statements within a Function cannot return data to a client"


CREATE FUNCTION dbo.PreferredVendor (@ProductID int)
RETURNS int
AS
BEGIN
DECLARE @VendorID
SELECT TOP 1 VendorID
FROM VendorProduct
ORDER BY Cost
RETURN @VendorID
END
GO

Joe is on the right track, but T-SQL uses non-ISO syntax for row limits:

SELECT TOP 1 Vendor.VendorID
FROM VendorProduct
WHERE Product.ProductID = @arg
ORDER BY Cost

Should do it. Of course you have to wrap that in the CREATE FUNCTION jazz and I'm assuming your product ID parameter is named @arg.

Using MIN(Cost) feels intuitively right but actually is the long way around. You can do it, but it would result in code that's much harder to read.

I PMed you my contact info if you'd like to discuss further.

[jscott]

Sorry, I have not seen a hangout invite. PM me with your email, we'll try the other direction.

The problem I see is that you need to connect your @VendorID local var to the SELECT function. Or you can probably bypass the local var entirely, since you don't need to use the value in the function. You're also not using the @ProductID arg to select which product you want.

Try this:

CREATE FUNCTION dbo.PreferredVendor (@ProductID int)
RETURNS int
AS
BEGIN
RETURN (
SELECT TOP 1 VendorID
FROM VendorProduct
WHERE Product.ProductID = @ProductID
ORDER BY Cost
)
END
GO

I'll be AFK for the next 20 mins or so.

[NYCfixie]

And here us my second attempt


CREATE FUNCTION dbo.PreferredVendor (@ProductID int)
RETURNS int
AS
BEGIN
DECLARE @VendorID int
DECLARE @Cost int
SELECT @Cost = MIN(COST)
FROM VendorProduct
WHERE ProductID = @ProductID
RETURN @VendorID
END
GO

[jscott]

(Engage Socratic method)

Why create a variable for cost? You want to return a vendor id, right? You don't actually care about the numerical cost, you just need the vendor id associated with the lowest cost.

I think your select syntax from attempt #1 is closer to the mark, you just need to figure out the function syntax to return a value.

[NYCfixie]

I emailed you as well (will PM my email in case it is getting caught in spam due to SQL code) but here is what worked (and you suggested)

ALTER FUNCTION dbo.PreferredVendor (@ProductID int)
RETURNS int
AS
BEGIN
RETURN
(
SELECT TOP 1 VendorID
FROM VendorProduct
WHERE ProductID = @ProductID
ORDER BY Cost
)

END
GO

SELECT dbo.PreferredVendor (@ProductID)



Any chance you can help with the next one:
Code and execute the select statement that returns the ProductName, ProductDescription, QtyOnHand, and the preferred VendorID (use your function).

This is what I have so far but is not working obviously:

SELECT ProductName, ProductDescription, QtyOnHand
FROM Product
JOIN (dbo.PreferredVendor) ON VendorProduct.ProductID = Product.ProductID



*****
And yes, this is what happens when you take a standard 16 week in-person class and jam it all into an 11 week online class and expect the same results. Read that as frustration because I chose the program I am in because it was originally 16 week online classes and then they switched to 11 week after I was accepted. If the program was not so well known I would transfer to a less expensive and local state school. Anyway, thanks for the help...

[NYCfixie]

jscott - can't PM you, your box is full and my emails might not be getting through or are stuck in spam due to SQL code....

[jscott]

We're connected on email now, let's keep going there.
(sorry to everybody following along at home)

[xjoex]

Glad you got it working!

-Joe