pickarooney February 2016

Select from multiple tables without cartesian product

I'm trying to create a command to copy the details of client memberships from one database to another with identical structure. I've pared it down to the bare essentials for the purposes of the question so the four items to copy are the expiry date, the subscription ID, the client ID and the item ID (which is the service which comprises the subscription). The clients have a common GUID in both bases. The subscription ID is a unique long int which should be the same in both bases and the expiry date is just a date. So far, so easy. The tricky part is that the item_id is not necessarily the same in each database. I need to map from one to the the other with a where statement, which I know how to do.

My problem is that I need to select from the destination database's own ITEM table (item_0) in order to get and insert the correct item_id and when I do this I get thousands of duplicate rows returned. I assume I need to use a join to avoid this but as I have nothing meaningful to join item_0 to I can't get any results.

insert into DestDB..subscription (expiry_date,id,client_id,item_id)
select 
    sub_1.expiry_date,
    sub_1.id,
    cli_0.id as client_id,
    item_0.id as item_id,
from SourceDB..subscription sub_1,
    DestDB..item item_0,
    DestDB..client cli_0
inner join SourceDB..client cli_1
   on cli_1.[guid] = cli_0.[guid] 
where sub_1.id not in (select id from DestDB..subscription)  
and item_0.id = 
     (select id from DestDB..collectiondetails 
             where service_ID =
              (select id from DestDB..service s_0 where s_0.code = 
                 (select code from SourceDB..service s_1 where s_1.id = 
                    (select service_ID from Source..collectiondetails item_1         where item_1.id = sub_1.item_id)))
             and collection_ID =
               (select id from DestDB..collection col_0
                  where col_0.code = 
                    (select code from SourceDB..collection col_1 where col_1        

Answers


Y.B. February 2016

I am afraid the updated question is even more confusing. Is this Select in Where clause guaranteed to return one record only or should it be in instead of =?

If there is no rule to identify a particular DestDB..item from the list of matching then the top one should do as well. It would still seem that item_0 can be omitted altogether:

insert into DestDB..subscription (expiry_date,id,client_id,item_id)
select 
    sub_1.expiry_date,
    sub_1.id,
    cli_0.id as client_id,
    (select Top 1 id from DestDB..collectiondetails   --<- Limit to top 1 only
             where service_ID =
              (select id from DestDB..service s_0 where s_0.code = 
                 (select code from SourceDB..service s_1 where s_1.id = 
                    (select service_ID from SourceDB..collectiondetails item_1 where item_1.id = sub_1.item_id)))
             and collection_ID =
               (select id from DestDB..collection col_0
                  where col_0.code = 
                    (select code from SourceDB..collection col_1 where col_1.id =
                       (select collection_ID from SourceDB..collectiondetails item_1 where item_1.id = sub_1.collection_ID)))
     ) as item_id,
from SourceDB..subscription sub_1,
    DestDB..client cli_0
inner join SourceDB..client cli_1
   on cli_1.[guid] = cli_0.[guid] 
where sub_1.id not in (select id from DestDB..subscription)  

Please note: if DestDB..item is empty the question example statement would not insert anything, but this answer one - would with item_id set to NULL.

I personally would try splitting this task into two separate statements in one transaction:

  1. Insert into target table with NULL item_id.
  2. Update target table with new item_id where item_id is NULL.
  3. (optional) Delete unwanted records where appropriate item_id

Post Status

Asked in February 2016
Viewed 3,247 times
Voted 11
Answered 1 times

Search




Leave an answer