Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have basically two views named customerV and productV.

the SQL statement in the customerV view is :

SELECT     CUSTOMER_REGISTRATION.SURNAME, CUSTOMER_REGISTRATION.OTHER_NAME, CUSTOMER_REGISTRATION.ADDRESS, RECIEVABLE.DATE, 
                      RECIEVABLE.PHONE#, RECIEVABLE.SERIAL#, RECIEVABLE.CHEQUE
FROM         CUSTOMER_REGISTRATION INNER JOIN
                      RECIEVABLE ON CUSTOMER_REGISTRATION.PHONE# = RECIEVABLE.PHONE#

while the SQL statement in the productV view is :

SELECT     PRODUCT, MAKE, MODEL
FROM         PRODUCTV

I just want to create a new view called serial_products which would join all the columns of the two views into a single view calledserial_products.

share|improve this question
4  
So what's the JOIN key going to be? If you JOIN the views, we have to know how they are related. –  JNK May 29 '12 at 13:12
1  
I don;t know what database you are using, but in SQL server views that call other views can be performance killers, I would make a new view not combine them. What are you hoping to accomplish? The only thing I can see is to create a list of all products for all customers unless you have some other fields that we need inthe base tables to perform the join. –  HLGEM May 29 '12 at 13:37
1  
And if this database is not yet in production, you should fix the misspelled tablename from Recievable to Receivable. Trust me, the people who have to maintain this will appreciate it if you do so. And phone number is a poor choice for a PK/FK as it changes (and they are reused sometimes for other people). –  HLGEM May 29 '12 at 13:39

1 Answer 1

Since you provide no common field to join on, I'm going to assume you want a cross join, where every customer will be linked to every product.

SELECT     CUSTOMER_REGISTRATION.SURNAME, CUSTOMER_REGISTRATION.OTHER_NAME,
           CUSTOMER_REGISTRATION.ADDRESS, RECIEVABLE.DATE,  RECIEVABLE.PHONE#,
           RECIEVABLE.SERIAL#, RECIEVABLE.CHEQUE, PRODUCT, MAKE, MODEL
FROM        CUSTOMER_REGISTRATION 
INNER JOIN  RECIEVABLE ON CUSTOMER_REGISTRATION.PHONE# = RECIEVABLE.PHONE#
CROSS JOIN  PRODUCTV
share|improve this answer
    
Sorry for not providing much info on the database, am a newbie here. The Database am using is MSSQL server 2005. I have a Database on Access so I am trying to replicate that database on MSSQLserver 2005. We are moving from Access to MSSqlserver and we have an retail inventory management application interface that works with the Access Database built using C#. so I have to come up with a database that fits the access database including columns,primary keys,foriegn keys and all. The database columns/P.K/F.K are outlined below: –  Peter May 30 '12 at 8:58
    
SELECT CUSTOMER_REGISTRATION.ID, CUSTOMER_REGISTRATION.SURNAME, CUSTOMER_REGISTRATION.OTHER_NAME,CUSTOMER_REGISTRATION.PHONE#, CUSTOMER_REGISTRATION.ADDRESS, GRN.DATE, GRN.PRODUCT, GRN.MAKE, GRN.MODEL, GRN.SERIAL#, RECEIVABLE.STAFF_NAME, RECEIVABLE.PHONE# AS Expr1, RECEIVABLE.DATE AS Expr2, RECEIVABLE.SERIAL# AS Expr3, RECEIVABLE.CHEQUE, MAKE_REGISTRATION.MAKE_ID, MAKE_REGISTRATION.MAKE AS Expr4, –  Peter May 30 '12 at 9:00
    
MODEL_REGISTRATION.MODEL_ID, MODEL_REGISTRATION.MODEL AS Expr5, PRODUCT_REGISTRATION.PRODUCT_ID, PRODUCT_REGISTRATION.PRODUCT AS Expr6,STAFF_REGISTRATION.STAFF_ID, STAFF_REGISTRATION.STAFF_NAME AS Expr7, SELLING_PRICE.PRODUCT AS Expr8,SELLING_PRICE.MAKE AS Expr9, SELLING_PRICE.MODEL AS Expr10, SELLING_PRICE.PRICE –  Peter May 30 '12 at 9:00
    
FROM MAKE_REGISTRATION INNER JOIN GRN ON MAKE_REGISTRATION.MAKE = GRN.MAKE INNER JOIN MODEL_REGISTRATION ON GRN.MODEL = MODEL_REGISTRATION.MODEL INNER JOIN PRODUCT_REGISTRATION ON GRN.PRODUCT = PRODUCT_REGISTRATION.PRODUCT INNER JOIN RECEIVABLE ON GRN.SERIAL# = RECEIVABLE.SERIAL# INNER JOIN SELLING_PRICE ON MAKE_REGISTRATION.MAKE = SELLING_PRICE.MAKE INNER JOIN STAFF_REGISTRATION ON RECEIVABLE.STAFF_NAME = STAFF_REGISTRATION.STAFF_NAME CROSS JOIN CUSTOMER_REGISTRATION –  Peter May 30 '12 at 9:02
1  
@Peter Put all those comments into your question, please. They don't belong as comments, but they are material to your problem. –  JNK May 30 '12 at 13:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.