Help builiding Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help builiding Query

Author

Topic

gammaman
Starting Member

3 Posts

Posted - 2010-05-01 : 11:28:32

I need help building a select query to select items that are part of a specific contract. When they place an order they should only see those items

Item table
itemNumber number Primary Key

Contracts table
contractNumber number

Contract Contents Table
contrNo number references contracts(contractNumber)
itemNo number references Items(itemNumber),
Primary key (contrNo,itemNo)

Orders
orderNum number Primary key
contrNum number references contracts(contractNumber)

See I want to be able to select a contract # when placing an order and based on the contr # selected, only show items that belong to that contract.

I have no experience at all with SQL so help will be greatly appriciaed.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-01 : 12:52:14

Please give some sample data and wanted output.

No, you're never too old to Yak'n'Roll if you're too young to die.

gammaman
Starting Member

3 Posts

Posted - 2010-05-01 : 14:14:58

I figured that out but need help with another. Need to find the price of an item in an order which belongs to a specific contract.

How can I write the following. Select contractprc from toSupply where
Table Contracts contractNumber = Table toSupply contractNum
And Table madeOf itemNum = Table tosupply itemNum

Here are the tables again

TO SUPPLY:
contractNum references Contracts(contractNo)
ItemNo references ITEMS(itemNumber)
contractPrc number

Contracts:
contractNo Primary Key

ITEMS:
itemNo Primary Key

MADE OF:
itemNum references ITEMS(itemNo)
orderNum references Orders(orderNo)

ORDERS:
orderNo Primary Key
contractNum references Contacts(contractNo)


So say in the Order table I have the following record

OrderNum   ContractNum
1000           1000

and in the made of table I have


Item Num    OrderNum
1000          1000
1004          1000


Contract table I have 
    contractNum
     1000
     1001
     1002

and in the toSupply table


ContrNum   ItemNo    contractPrice
1000       1001       90
1000       1004       10 
1001       1001       50 
1004       1004       40

I want to find the price of the item that has a specific contract Number

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-01 : 14:28:41

[code]
SELECT s.ItemNo,s.contractPrice
FROM toSupply s
JOIN Contract c
ON c.contractNum = s.ContrNum
JOIN MadeOf mo
ON mo.[Item Num] = s.ItemNo
JOIN Order o
ON o.OrderNum = mo.OrderNum
WHERE c.contractNum = @contractNo
[/code]

through @contractNo pass the reqd contract no to get associated items and their price

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gammaman
Starting Member

3 Posts

Posted - 2010-05-01 : 14:31:39

Ok so I decided it was time to cheat a little. I used Oracles built in Query Generator to get the following


 select	 "TOSUPPLY"."CONTRACTPRC" as "CONTRACTPRC",
	 "TOSUPPLY"."ITEMNO" as "ITEMNO" 
 from	 "ORDERS" "ORDERS",
	 "MADEOF" "MADEOF",
	 "TOSUPPLY" "TOSUPPLY" 
 where   "MADEOF"."ITEMNUM"="TOSUPPLY"."ITEMNO"
 and	 "ORDERS"."CONTRACTNUM"="TOSUPPLY"."CONTRACTNUM"

The only thing I still need to know is how to specify a specific
order number for which I want to see the prices. So say where order number = 1000. In reality though this "1000" would be stored in a php variable.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-01 : 14:41:21

see how i passed variable on last post. use similar method ( I dont know how exactly in Oracle as I'm not an Oracle guy and nor is this an Oracle forum!)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources