HELP with Views

HELP with Views

Post by JD » Fri, 19 Sep 2003 19:58:51



Hi I need to created a view.

I have a table with

1.  All customers, -  Customers
                                ID; PK
                                Name etc...
2.  Sheet the fill out every time the come in - SurveyForm
                                                                    FormID;
PK
                                                                    ID; FK -
Customers

Information etc...
3.  Products that were distributed - Products
                                                       ProdID; PK
                                                        Product
                                                        ProductDesc
4.  Supplies Needed - Supplies
                                   SupplyID
                                   Supply
                                   SupplyDesc
5. Link Table between Survey Form and product- SurveyProducts

FormID; FK - SurveyForm

ProdID; FK - Products
6.  Link Table between Survey Form and Supplies- SurveySupply

FormID; FK - SurveyForm

SupplyID; FK - Supplies

I use the statement but I dont return any values:

SELECT Customers.FName, Customers.LName,
    Products.Product
FROM SurveyProd INNER JOIN
    SurveyForm ON
    SurveyProd.FormID = SurveyForm .FormID INNER JOIN
    Products ON
    SurveyProd.ProdID = Products.ProdID INNER JOIN
    Customers ON
    SurveyForm .PartID = Customers.PartID

Can anyone help?

Thanks
J

 
 
 

HELP with Views

Post by Jens Sü?meye » Fri, 19 Sep 2003 20:13:18


Thanks for the descirption, but its better to post DDL here, that we can
view the structure in details (Much better if you also post some sample data
in DML) and the results you want to get.

But if no result are returned then the corresonding values in the tables
doesnt fit together.

HTH, Jens S?meyer.



Quote:> Hi I need to created a view.

> I have a table with

> 1.  All customers, -  Customers
>                                 ID; PK
>                                 Name etc...
> 2.  Sheet the fill out every time the come in - SurveyForm

FormID;
> PK
>                                                                     ID;
FK -
> Customers

> Information etc...
> 3.  Products that were distributed - Products
>                                                        ProdID; PK
>                                                         Product
>                                                         ProductDesc
> 4.  Supplies Needed - Supplies
>                                    SupplyID
>                                    Supply
>                                    SupplyDesc
> 5. Link Table between Survey Form and product- SurveyProducts

> FormID; FK - SurveyForm

> ProdID; FK - Products
> 6.  Link Table between Survey Form and Supplies- SurveySupply

> FormID; FK - SurveyForm

> SupplyID; FK - Supplies

> I use the statement but I dont return any values:

> SELECT Customers.FName, Customers.LName,
>     Products.Product
> FROM SurveyProd INNER JOIN
>     SurveyForm ON
>     SurveyProd.FormID = SurveyForm .FormID INNER JOIN
>     Products ON
>     SurveyProd.ProdID = Products.ProdID INNER JOIN
>     Customers ON
>     SurveyForm .PartID = Customers.PartID

> Can anyone help?

> Thanks
> J


 
 
 

HELP with Views

Post by Vishal Parka » Fri, 19 Sep 2003 20:50:32


probably there is no records in surveyprod that matches formid and prodid
from surveyform and products table respectively.
but this just a guess. There is a possibility that any of the join is
failing.

To get correct solution post relevent ddls and some sample records.

--
- Vishal

 
 
 

HELP with Views

Post by JD » Fri, 19 Sep 2003 21:04:30


That was it.  I can't believe I missed that.

Thanks Vishal


Quote:> probably there is no records in surveyprod that matches formid and prodid
> from surveyform and products table respectively.
> but this just a guess. There is a possibility that any of the join is
> failing.

> To get correct solution post relevent ddls and some sample records.

> --
> - Vishal

 
 
 

1. Help with VIEW

I have 5 tables T1, T2, T3, T4, T5. They all. share some common fields
PROD_ID, PROD_ID_TYP for example. I am trying to create a VIEW using union
as below.

Create VIEW my_VIEW as
Select PROD_ID, PROD_ID_TYP, FIELD1, FIELD2,  myFUNC(PROD_ID) FIELD3 FROM T1
UNION
Select PROD_ID, PROD_ID_TYP, FIELD1, FIELD2,  myFUNC(PROD_ID) FIELD3 FROM T2
UNION
Select PROD_ID, PROD_ID_TYP, FIELD1, FIELD2,  myFUNC(PROD_ID) FIELD3 FROM T3
UNION
Select PROD_ID, PROD_ID_TYP, FIELD1, FIELD2,  myFUNC(PROD_ID) FIELD3 FROM T4
UNION
Select PROD_ID, PROD_ID_TYP, FIELD1, FIELD2,  myFUNC(PROD_ID) FIELD3 FROM
T5;

Notice that I use a function myFUNC(PROD_ID) to generate FIELD3. I am
experiencing really bad performance when i try to query the view. Is there a
better solution to this ?

Thanks much

2. SLQServer 6.0 Performance (or lack of it)

3. HELP: GPF viewing Crystal print preview

4. Upload Pictures

5. Need help with views and locking

6. Stripping data out of a field, slightly diffrent case this time

7. Help - Create view

8. ESMTP Test

9. Help with view definition.

10. Revisited: Help with View - No Data Returned

11. help: dynamic view

12. HELP: Aggregate View Conditional Opperators

13. Help With View