SQL HELP
-
Hi,
I am trying to write a select query, but not very experienced with Joining tables. What i want to do is to find all those customers with their addresses who have purchased from product group 410 and 430 from 01/04/05 to present.
This is what i have so far, ive checked the field names and tables, these are correct, just need help with the joins.
select wcustomers.custname, waddresses.address1, waddressesaddress2, waddresses.address3, waddresses.postcode
from partgroup, wcustomers, waddresses, salesorders
where partgroup.pgcode = 410 and salesorders.sdate >= 01-04-2005
Thanks for your help
MO
-
Mo
This is just a guess because you haven't provided any table DDL. Why are there separate tables for customers and addresses?
SELECT c.custname, a.address1, a.address2, a.address, a.postcode
FROM waddressses a JOIN wcustomers c
ON a.custID = c.custID
JOIN salesorders o
ON c.orderID = o.orderID
JOIN partgroup p
ON o.pgcode = p.pgcode
WHERE p.pgcode in (410, 430)
AND o.sdate >= '2005-04-01 00:00:00.000'
If this doesn't fit your data, please provide DDL for the four tables, sample data and expected results.
John
-
Hi John,
Thanks for your relpy. sorry but whats a DDL?
Mo
-
DDL = Data definition language.
It looks something like this for a single table with everything scripted out :
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Order Details]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Order Details]
GO
CREATE TABLE [dbo].[Order Details] (
[OrderID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[UnitPrice] [money] NOT NULL ,
[Quantity] [smallint] NOT NULL ,
[Discount] [real] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order Details] WITH NOCHECK ADD
CONSTRAINT [PK_Order_Details] PRIMARY KEY CLUSTERED
(
[OrderID],
[ProductID]
 
ON [PRIMARY]GO
ALTER TABLE [dbo].[Order Details] ADD
CONSTRAINT [DF_Order_Details_UnitPrice] DEFAULT (0) FOR [UnitPrice],
CONSTRAINT [DF_Order_Details_Quantity] DEFAULT (1) FOR [Quantity],
CONSTRAINT [DF_Order_Details_Discount] DEFAULT (0) FOR [Discount],
CONSTRAINT [CK_Discount] CHECK ([Discount] >= 0 and [Discount] <= 1),
CONSTRAINT [CK_Quantity] CHECK ([Quantity] > 0),
CONSTRAINT [CK_UnitPrice] CHECK ([UnitPrice] >= 0)
GO
CREATE INDEX [OrderID] ON [dbo].[Order Details]([OrderID]) ON [PRIMARY]
GO
CREATE INDEX [OrdersOrder_Details] ON [dbo].[Order Details]([OrderID]) ON [PRIMARY]
GO
CREATE INDEX [ProductID] ON [dbo].[Order Details]([ProductID]) ON [PRIMARY]
GO
CREATE INDEX [ProductsOrder_Details] ON [dbo].[Order Details]([ProductID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order Details] ADD
CONSTRAINT [FK_Order_Details_Orders] FOREIGN KEY
(
[OrderID]
 
REFERENCES [dbo].[Orders] ([OrderID]
 
,CONSTRAINT [FK_Order_Details_Products] FOREIGN KEY
(
[ProductID]
 
REFERENCES [dbo].[Products] ([ProductID]
 
GO
When we have that information we can test our queries and make sure our solution is 100% working.
-
Mo
It stands for Data Definition language. Please read the following two articles, which are useful to first-time posters:
http://www.sqlservercentral.com/columnists/siyer/2645.asp, which contains a link to http://www.aspfaq.com/etiquette.asp?id=5006
John
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply