SQL help in solving queries!

[mo_green]

hi am working on a project on databases, i have already created a conceptual schema design(ERD) which i translated into the logical relational schema below........

Customer{customer_id, customer_fname, customer_lname, customer_address, customer tel no, customer_type*}

Customer category{customer type, discount}//private =0, silver = 1.5, gold = 2.5, diamond = 4.5 //a customer has to be in one of the four categories. silver, gold and diamond customers are said to be business customers

category{category_id, category_name}

brand(brand id, brand name}

supplier{supplier id, supplier name, supplier address}

equipment{equipment id, equipment_name,category id*, brand id*, supplier id*, price, delivery time}

Transaction{transaction_id, equipment_id*, customer_id*, return_date, cancellation_reason, service_type//refund or replacement}

now the last part of my assignment expects me to carry out sql queries and am not very good with sql. the queries are below and some of the work i have tried on them.

The queries

1. For a given particular equipment, show current stock and current items on hire with expected return dates. the name of the equipment should be captured at run-time from the user.

am still working on this one.

2.for a particular business customer, show the current items on hire with expected return dates plus any previous complaints that were made by that customer that involved a replacement or refund.

SELECT t.Equipment_id, t.Return_date, t.Cancellation_Reason, t.Support
FROM [Transaction] AS t, Customer AS c, Membership AS m
WHERE t.Customer_id=c.Customer_id And m.Discount>1;

this solution does not give me the desired results

3.produce, for a given category, the total number of equipmets(i.e. items) under that category available in stock and the number currently on hire to customers.

4. A list of names and addresses of all suppliers along with the total number of equipments from all categorys that they currently supply.

SELECT S.Supplier_name, S.Supplier_address, COUNT(E.Supplier_id) AS Total
FROM Supplier AS S, Equipment AS E
WHERE S.Supplier_id=E.Suppler_id;

i thought i had actually got this right but it is giving me an error that "supplier_name" is not part of an aggregate function.

.......................................................Any help will be appreciated.

[Hack]

Welcome to DevX

When using aggregates (such as SUM, COUNT, MIN, MAX, etc) you MUST include ALL fields in the SELECT statement NOT involved in the aggregate, in an ORDER BY clause.

In this case, your SQL query needs to read

SELECT S.Supplier_name, S.Supplier_address, COUNT(E.Supplier_id) AS Total
FROM Supplier AS S, Equipment AS E
WHERE S.Supplier_id=E.Suppler_id
ORDER BY S.Supplier_name, S.Supplier_address

[mo_green]

Thanx for the heads up. will look it up and make adjustments.

[Hack]

When you have a moment, skim through this.

http://www.w3schools.com/sql/sql_groupby.asp

[mo_green]

Thanx for the help, i looked it up and got more than i bargained for. anyway i have rearranged my coding for the two question that am making progress on but for .

Q 2. i have come up with this code

SELECT t.Equipment_id, t.Return_date, t.Cancellation_reason, t.Support

FROM [Transaction] AS t, Customer AS c

WHERE Customer_name=c.Customer_name And c.Customer_id=t.Customer_id And c.Discount>0

ORDER BY Equipment_id, Return_date, Cancellation_reason, Support;

the code gives me the required results but how can i get the user to input a name, then after that give the resultset for that particular user.

Q4. i have come up with this code

SELECT s.Supplier_name, s.Supplier_address, COUNT(e.Supplier_id) AS Total
FROM Supplier AS s, Equipment AS e
WHERE s.Supplier_id=t.Supplier_id
GROUP BY e.Supplier_id
ORDER BY s.Supplier_name, s.Supplier_address;

but it is still giving me this error "you tried to execute a query that does not include the specified expression 'Supplier_name' as part of an agregate function"
where have i gone wrong? i have attached my database in case you want to see the queries as i have wrote them.

[Hack]

but it is still giving me this error "you tried to execute a query that does not include the specified expression 'Supplier_name' as part of an agregate function"
where have i gone wrong?

Look at your GROUP BY clause.

Now, consider this

When using aggregates (such as SUM, COUNT, MIN, MAX, etc) you MUST include ALL fields in the SELECT statement NOT involved in the aggregate, in an ORDER BY clause.

You do not have all the fields in your select statement NOT involved in the aggregate. You have the ONE field in your select that is involved. That is the problem.

Go back to Post #2 and look at the GROUP BY that I used.

[Hack]

the code gives me the required results but how can i get the user to input a name, then after that give the resultset for that particular user.

Are you doing this in Visual Basic or Access VBA? If so, what version?

Add a textbox to the screen and use the contents to return results for a particular user.

SELECT s.Supplier_name, s.Supplier_address, COUNT(e.Supplier_id) AS Total
FROM Supplier AS s, Equipment AS e
WHERE s.Supplier_id=t.Supplier_id
AND fieldintableforcustomername = '" & Text1.Text & "' " '<====== add this to your form
GROUP BY e.Supplier_id  '<====== fix your group by of course
ORDER BY s.Supplier_name, s.Supplier_address;