Problem creating view

Hi
I have the following set up:
CREATE TABLE PURCHASE
(
ITEM_IDint NOT NULL,
CUST_IDint NOT NULL,
)
go
CREATE TABLE ITEM
(
ITEM_IDint NOT NULL,
ITEM_NAMEvarchar(32) NOT NULL
)
go
CREATE TABLE CUST
(
CUST_IDint NOT NULL,
CUST_NAMEvarchar(32) NOT NULL
)
go
CREATE VIEW PURCHASE_VIEW
AS
SELECT P.ITEM_ID, I.ITEM_NAME, P.CUST_ID, C.CUST_NAME
FROM PURCHASE P, ITEM I, CUST C
WHERE (P.ITEM_ID = I.ITEM_ID) AND (P.CUST_ID = C.CUST_ID)
go
The problem I have is, the PURCHASE_VIEW only shows rows in PURCHASE
for which a corresponding
ITEM_NAME and CUST_NAME exist in ITEM and CUST. However, I want the
view to return a row for each row of the PURCHASE table, but with the
ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
in ITEM or CUST.
Is there some way I can achieve this?
Thanks,
Neil
> However, I want the
> view to return a row for each row of the PURCHASE table, but with the
> ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> in ITEM or CUST.
You can use an outer join will return purchases even without corresponding
items and customers. I must ask how how a purchase was possible for an
non-existent item or customer. Perhaps the foreign keys are missing,
leading to data integrity problems.
CREATE VIEW PURCHASE_VIEW
AS
SELECT
P.ITEM_ID,
I.ITEM_NAME,
P.CUST_ID,
C.CUST_NAME
FROM PURCHASE P
LEFT OUTER JOIN ITEM I ON
P.ITEM_ID = I.ITEM_ID
LEFT OUTER JOIN CUST C ON
P.CUST_ID = C.CUST_ID
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1171809173.950612.119490@.l53g2000cwa.googlegr oups.com...
> Hi
> I have the following set up:
> CREATE TABLE PURCHASE
> (
> ITEM_ID int NOT NULL,
> CUST_ID int NOT NULL,
> )
> go
> CREATE TABLE ITEM
> (
> ITEM_ID int NOT NULL,
> ITEM_NAME varchar(32) NOT NULL
> )
> go
> CREATE TABLE CUST
> (
> CUST_ID int NOT NULL,
> CUST_NAME varchar(32) NOT NULL
> )
> go
> CREATE VIEW PURCHASE_VIEW
> AS
> SELECT P.ITEM_ID, I.ITEM_NAME, P.CUST_ID, C.CUST_NAME
> FROM PURCHASE P, ITEM I, CUST C
> WHERE (P.ITEM_ID = I.ITEM_ID) AND (P.CUST_ID = C.CUST_ID)
> go
> The problem I have is, the PURCHASE_VIEW only shows rows in PURCHASE
> for which a corresponding
> ITEM_NAME and CUST_NAME exist in ITEM and CUST. However, I want the
> view to return a row for each row of the PURCHASE table, but with the
> ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> in ITEM or CUST.
> Is there some way I can achieve this?
> Thanks,
> Neil
>
|||On 18 Feb, 15:02, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> You can use an outer join will return purchases even without corresponding
> items and customers. I must ask how how a purchase was possible for an
> non-existent item or customer. Perhaps the foreign keys are missing,
> leading to data integrity problems.
Many thanks, Dan. I will give this a try.
To answer your question, this is a contrived example, which I
simplified for the purpose of this post.