6 Tables, 17 Columns, Need 1 Solution

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

SQL Server 2000 Forums

Transact-SQL (2000)

6 Tables, 17 Columns, Need 1 Solution

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-17 : 16:24:01

I have six tables. This might be a bad design but I have no option.


TblTemplate        Customer        ContactDetail        
IdNo               FirstName       OldContactFName
SendTo             LastName        OldContactLName
                   EmailId         EmailId
                   ContactId       ContactId
                   OrgName
                   UniqueId
                   SellerId                   

Product            Service         Seller
OrderNo            OrderNo         SellerId 
OrderDate          OrderDate       SellerName
ProdId             ServiceId       EmailId    
ProdDesc           ServiceDesc     SupportEmailId
ContactId          ProdId
SellerId           SellerId
                   ContactId

The Values in these tables are something like this:


TblTemplate   
------------
IdNo    SendTo
1       Customer    
2       Customer  
3       Seller
4       SupportSeller 

Customer
--------
FirstName     LastName     EmailId   ContactId   OrgName    UniqueId SellerId
Karuna        Karan        a@b.c      1           xyz        11111    1
Karuna        Karan        a@b.c      2           xyz        11011    3
Arun          Kumar        b@a.c      3           abc        10023    2
Arvind        Kumar        c@b.a      4           def        98765    3

ContactDetail
-------------
OldContactFName   OldContactLName    OldContactEmailId    ContactID
Rajesh                                x@y.z                  1

Product
-------
OrderNo   OrderDate     ProdId     ProdDesc    ContactId    SellerId
1          12/01/2005   X123       ABCD         1             1
1          12/01/2005   X131       BCDA         1             1
2          12/01/2005   X231       CDEF         2             3


Service
-------
OrderNo   OrderDate     ProdId     ProdDesc    ContactId    SellerId    ProdId

1          12/01/2005   S123       ABCD         1             1        X123
1          12/01/2005   S131       BCDA         1             1        X131
8          20/01/2005   S123       MNOP         4             3        A123
4          25/01/2005   S456       ABCD         3             2        X456

Seller
--------
SellerId      SellerName       EmailId       SupportEmailId
1             Seller1          s1@s1.s1      ss1@ss1.ss1
2             Seller2          s2@s2.s2      ss2@ss2.ss2
3             Seller3          s3@s3.s3      ss3@ss3.ss3

I want to fetch around 16 columns from these tables.
They are:


TblTemplate        Customer        ContactDetail        
IdNo               FirstName       OldContactFName
SendTo             LastName        OldContactLName
                   EmailId         EmailId
                   OrgName         ContactId
                   UniqueId

Product            Service         Seller
ProdDesc           ServiceDesc     SellerName
                                   EmailId    
                                   SupportEmailId

The Records should be based on the IdNo Of TblTemplate.Fetching the IdNo depends on the date of purhcase.Say, if the difference between today and orderdate is 10 then it will IdNo 1.The Product and Service are grouped based on the order date. If both product and service is purchased on the same date then its grouped as one column, if product purchase not with service then it will be a different column. If service purchased without product then it will be another column.

The Final Table structure has to be something like this.
FinalTable


IdNo    FirstName    LastName    EmailId    OrgName    UniqueId
OldContactFName    OldContactLName    OldcontactEmail
(ProdDesc+ServiceDesc)    ProdDesc    ServiceDesc    SellerName    Service.EmailId    SupportEmailId

I have tried to post with as much information as possible,Please bear with me for the long post...

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-17 : 17:08:32

What is it that you want help with?

CODO ERGO SUM

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-17 : 17:14:07

I'm trying to get the results in the final table structure.
I'm not sure where to start with this...

But I'm thinking about creating 2 or may be 3 views of these 6 tables and build a query on those views...

Any suggesstions?

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-17 : 17:36:36

It is not at all obvious just how these tables are supposed to relate to each other, so I think you need to explicitly state that. Several of the tables do not even appear to have candidate primary keys, so it is very hard to guess how they are supposed to be related.

Also, is there some selection criteria that you want to use, or do you just want to join these tables together.

Finally, the following columns do not seem to be in any of your tables, so where are they supposed to come from?
OldcontactEmail
Service.EmailId

CODO ERGO SUM

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-17 : 17:43:01

quote:
Originally posted by Michael Valentine Jones

Finally, the following columns do not seem to be in any of your tables, so where are they supposed to come from?
OldcontactEmail
Service.EmailId
CODO ERGO SUM

Sorry it should be ContactDetail.EMailId and Seller.EmailId.

Its Three in the morning here...I'm sitting here all night on this.

I'll post the relationships and the desired output in a 10-15 mins time.

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-17 : 18:18:24

Here we go..

TblTemplate Customer ContactDetail
IdNo FirstName OldContactFName
SendTo LastName OldContactLName
EmailId EmailId
ContactId ContactId
OrgName
UniqueId
SellerId

Product Service Seller
OrderNo OrderNo SellerId
OrderDate OrderDate SellerName
ProdId ServiceId EmailId
ProdDesc ServiceDesc SupportEmailId
ContactId ProdId
SellerId SellerId
ContactId

The lone loose table here is TblTemplate.

1. ContactId relates the contact person of the customer with all other tables.The ContactId again depends on sellerId also.
The contact can have more than 1 reseller so for each reseller he will have different contactid.

2. SellerId is unique on seller table.

3. The contact id and sellerid in the product and service table will
say which contact brought the product / service from which reseller.
This relationship can be derived using issue 1 too.

4. serviceid depends upon the productid. For one product there can
be different kind of services.

5. Since these services are optional, the customer may buy product
with service in that case, it has to match with the order no of both tables.

6. The customer may also buy services with out products in a order,
in that case there will not be a corresponding order no in the
product table and vice versa (product with out service)
The Product Description & Service Description will be same.
So description of any one of those will fit.

7. Along with these if the details of first 3 columns
FirstName,LastName,EmailId) does not match with that of
ContactDetail, it has to be fetched.

8. Fetching of IdNo from TblTemplate depends upon the difference of
days between todays date and order date.Say if its more than 10 days
then I have to fetch 1.

IdNo FirstName LastName EmailId OrgName UniqueId OldContactFName OldContactLName OldcontactEmail
(ProdDesc+ServiceDesc) ProdDesc ServiceDesc SellerName Service.EmailId SupportEmailId

1 Karuna Karan a@b.c xyz 11111 Rajesh x@y.z
"ABCD,BCDA" Seller1 s1@s1.s1 ss1@ss1.ss1
1 Karuna Karan a@b.c xyz 11011 Rajesh x@y.z
"CDEF"; Seller1 s1@s1.s1 ss1@ss1.ss1

4 Arun Kumar b@a.c abc 10023
"MNOP"; Seller3 s3@s3.s3 ss3@ss3.ss3

4 Arvind Kumar c@b.a def 98765
"ABCD"; Seller2 s2@s2.s2 ss2@ss2.ss2

If the columns doesnt have any values they can be null.

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-04-17 : 22:15:32

Everyone here speaks SQL. When you want to communicate your tables to us, try to do it in the language that we all understand - SQL.

Can you relay to us the actual table definitions? That way we will know all of the relevant DRI and other constraints.

HTH

=================================================================
Every act of conscious learning requires the willingness to suffer an injury to one's self-esteem. That is why young children, before they are aware of their own self-importance, learn so easily; and why older persons, especially if vain or important, cannot learn at all. -Thomas Szasz, author, professor of psychiatry (1920- )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources