CODEWITHSUNDEEP
sql
user3684663
user3684663

Reputation: 1

SQL Combining results from multiple tables, and rows, in to one row in one table

so here's my situation. I have two tables (keysetdata115) containing vendor information and keysetdata117 that contains either a Remit or Payment address.

Here are the structures with one sample entry: keysetdata115:

keysetnum   ks183   ks178   ks184                    ks185       ks187  usagecount
2160826     1       6934    AUDIO DIGEST FOUNDATION  26-1180877  A      0

keysetdata117 (I truncated values for ks192 and ks191 to fit formatting)

keysetnum   ks183   ks178   ks188   ks189   ks190     ks192     ks191   usagecount
2160827     1       6934    P001    P       EBSCO...  TOP OF... A       0
2160828     1       6934    R002    R       EBSCO...  123 SE... A       0

There is no 1:1 relationship and the only thing that makes a unique record is the combination or Remit Code,Payment Code, vendor number and vendor group.The codes can only be obtained by referencing the address and / or name.

Ideally what I'd like to do is set this up so that I can pass in the addresses and return all the related values.

I'm dumping this in a table called 'dbo.test' right now (for testing obviously), that has the following entries and what the correspond to in the above tables: vengroup (ks183), vendnum (ks178), remit (ks188), payment (ks188)... ks188 will be a remit or payment based off the value in ks189.

This is what I'm doing so far, using 3 select queries and it works, but there's a lot of redundancy and it's very inefficient.

Any suggestions on how I can streamline it would be MUCH appreciated.

insert into dbo.test (vengroup,vendnum)
  select ks183, ks178
  from hsi.keysetdata115
  where ks184 like 'AUDIO DIGEST%'

update dbo.test
set dbo.test.remit = y.remit
from
dbo.test tst
INNER JOIN
    (Select ksd.ks188 as remit, ksd.ks183 as vengroup, ksd.ks178 as vendnum
    from hsi.keysetdata117 ksd
    inner join dbo.test tst
        on tst.vengroup = ksd.ks183 and tst.vendnum = ksd.ks178
    where ksd.ks190 like 'EBSCO%' and ks189 = 'R') y
on tst.vengroup = y.vengroup and tst.vendnum = y.vendnum


update dbo.test
set dbo.test.payment = y.payment
from
dbo.test tst
INNER JOIN
    (Select ksd.ks188 as payment, ksd.ks183 as vengroup, ksd.ks178 as vendnum
    from hsi.keysetdata117 ksd
    inner join dbo.test tst
        on tst.vengroup = ksd.ks183 and tst.vendnum = ksd.ks178
    where ksd.ks190 like 'EBSCO%' and ks189 = 'P') y
on tst.vengroup = y.vengroup and tst.vendnum = y.vendnum

Thanks so much for any suggestions!

Upvotes: 0

Views: 37

Answers (1)

TommCatt
TommCatt

Reputation: 5636

You can do what you want in one statement. You just have to do the selection on the run. The way the statement below is written, if Remit gets the value, Payment gets a null and vice versa. If you want the other value to be non-null, just add an else clause to the cases. Like then b.ks188 else 0 end.

INSERT INTO dbo.TEST( vengroup, vendnum, remit, payment )
  SELECT  a.ks183, a.ks178, 
          CASE b.ks189 WHEN 'R' THEN b.ks188 END, 
          CASE b.ks189 WHEN 'P' THEN b.ks188 END 
  FROM  keysetdata115 a
  JOIN  keysetdata117 b
    ON  b.ks183 = a.ks183
    AND b.ks178 = a.ks178
    AND b.ks190 LIKE 'EBSCO%' 
  WHERE a.ks184 LIKE 'AUDIO DIGEST%';

Upvotes: 1

Related Questions