Help in Table mapping - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Help in Table mapping

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2011-07-28 : 15:19:04

Table1:
ProductId
ProductDescription

Tables For Subscribers:
Table: I_10
SubscriberId
SubscriberName
ProductId

Table: I_22
SubscriberId
SubscriberName
ProductId

Table: I_31
SubscriberId
SubscriberName
ProductId

Table: I_55
SubscriberId
SubscriberName
ProductId

Table: I_66
SubscriberId
SubscriberName
ProductId

Table: I_85
SubscriberId
SubscriberName
ProductId

I need to map ProductId with Subscribers.
Table1 contains all Products with ProductID
And balance table is for Subscribers which has multiple ProductID's. These tables may be extended for subscribers. Here I_85 represents table name and in this 85 is subscriberID.

Now from above tables i need a query to found ProductsID's SubscriberId.
Ex: If Table1 has values
[ProductID] [ProductDesciption]
100 Pepsi Prodcuts
101 Coke Prodcuts
102 Limca Products
103 Maaza Products

Ex: I_55 --> Table for Pepsi Subscribers --> 55 SubscriberId
This table has many ProductID (ex: 100-->ProductId)

SO need a Query for ProdcutId's SubscriberID and Name.
Ex: If ProductId-->100
Then It must return 55-->SubsriberId
Pepsi-->Subscriber's Name

Please help me in this. Because dere is no Master datas for this mapping..

Regards,
Kalaiselvan R
Love Yourself First....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 02:37:02

so you want all subscribers to be listed in single row or as different rows?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2011-07-29 : 06:04:48

Need Subscribers in single row.
Ex:
[PRODUCTID]
[SUBSCRIBERID]
[SUBSCRIBERNAME]

For all productID's and its related SubscriberID's from multiple table which i mentioned for each Subscribers.

Regards,
Kalaiselvan R
Love Yourself First....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 11:50:40

so it will be like

Product subscribeid1 subscribername1 subscriberid2 subscribername2....
?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2011-08-01 : 18:37:48

no it will be like

Product1, subscriberID1, SubscriberName1
Product2, subscriberID1, SubscriberName1
Product3, subscriberID1, SubscriberName1
Product4, subscriberID1, SubscriberName1
Product5, subscriberID2, SubscriberName2
Product6, subscriberID2, SubscriberName2
Product7, subscriberID2, SubscriberName2
Product8, subscriberID2, SubscriberName2 etc....

Regards,
Kalaiselvan R
Love Yourself First....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 01:03:49

isnt it just a matter of merging all tables using union all?


SELECT ProductID,SubscriberId,
SubscriberName
FROM I_10
UNION ALL

SELECT ProductID,SubscriberId,
SubscriberName
FROM I_22
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2011-08-02 : 23:40:24

Sir, I think you cant get my question. Union all is simple one, even I too know to do it.

Am asking how to get the datas from Subscriber tables to Table 1. Table 1 has Data for ProductId and Product Description. Need to add Subscriber Id and Subscriber Name to that table.

Table 1:
ProductId
ProductDescriptiopn

For Each ProductId need to get the SubscriberId and Description. This productId may be in any of the Subscriber Tables which I mentioned Above.
Ex: It may be in I_77 OR I_55 OR I_45 etc..

Need the output has If ProductId=100 and that ProductId is in I_85 table then output will be
[ProductId] 100
[SubscriberId] 85
[SubDescription] Pepsi

Cant able to join Table1 with I-85 directly since like I_85 there will be many tables for Subscribers.
If you cant get my question still reply me will xplain you in detail.

Regards,
Kalaiselvan R
Love Yourself First....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-03 : 00:44:47

then also its same as what i told. you can join your main table with union all query like


update t
set t.subscriberid= u.subscriberid,
t.subscribername=u.subscribername
from [Table 1] t
join (SELECT ProductID,SubscriberId,
SubscriberName
FROM I_10
UNION ALL

SELECT ProductID,SubscriberId,
SubscriberName
FROM I_22
...


)u
on u.ProductID= t.ProductID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources