SQL Server one to many relationship

Transact-SQL
1

I have a table PCT that links to table PCTC. The PCT table contains a transaction record where the PCTC table could contain many records associated with the 1 PCT record. PCT has a primary key of tran_num and PCTC has a primary key of cov_num with a foreign key of tran_num. There will always be at least 1 record in the PCTC table associated with the record in PCT. I want to select records from PCT and for each PCT record I want to select to display a single field from each associated PCTC record.

Example:

PCT, tran_num = 100, patient = 99
PCTC, cov_num = 1, tran_num = 100, payor = ONE
cov_num = 2, tran_num = 100, payor = TWO

I want my select to output:

100 99 ONE TWO

If there is only 1 associated record in PCTC then I want my select to output:

100 99 ONE NULL

I have researched the internet for days and it seems it should be simple but I have tried everything I can think of. Note that I may have been looking at it for too long to see straight. Thanks for any help you can provide!

2

Maybe you can use something like this:

create table #pct (tran_num int,patient int);
create table #pctc (cov_num int,tran_num int,payor varchar(10));

insert into #pct (tran_num,patient)
 values(100,99)
      ,(101,100)
      ,(102,101)
      ,(103,102)
;

insert into #pctc (cov_num,tran_num,payor)
 values(1,100,'ONE')
      ,(2,100,'TWO')
      ,(2,100,'THREE')
      ,(4,102,'ONE')
      ,(5,103,'ONE')
      ,(6,103,'THREE')
;

select tran_num
      ,patient
      ,[1]
      ,[2]
  from (select a.tran_num
              ,a.patient
              ,b.payor
              ,b.rn
          from #pct as a
               left outer join (select tran_num
                                      ,payor
                                      ,row_number() over(partition by tran_num order by cov_num) as rn
                                  from #pctc
                               ) as b
                            on b.tran_num=a.tran_num
                           and b.rn<=2
       ) as a
 pivot (min(a.payor)
        for a.rn in ([1],[2])
       ) as b
;

drop table #pctc;
drop table #pct;
3

No, you do not. The term link refers to pointer chains in the old network databases. Did you mean a REFERENCE, which is totally different? Links are for traversals and have no DRI actions. If you had followed basic forum netiquette, we would see the DDL you ha ve tried. Please fix this and we can go at it again.

No, rows are not records. SQL only has referencing, not associations. Your narrative is all pure Network Database terminology!

I know that this is probably your first year of SQL and RDBMS, but if you takr the timr to learn the proper terms, you will learn the proper concepts and this will save your butt later.

You have now hit on a weak spot in SQL! We use a set-oriented model of data. This means an empty set is just fine with us.We have trouble at the conceptual level with “:at least reference in the PCTC table to the PCT table”.

Back in the network model you have in your mindset, a pointer had to point at something, so you guys had problems with emptiness.

Here is my guess for a very bad skeleton. You would never use numeric types for identifiers, etc. but all we have is your sample data.

CREATE TABLE PCT
(tran_num INTEGER NOT NULL PRIMARY KEY,
patient_id INTEGER NOT NULL);

CREATE TABLE PCTC
(cov_num INTEGER NOT NULL PRIMARY KEY,
tran_num INTEGER NOT NULL
REFERENCES PCT (tran_num)
ON DELETE CASCADE,
payor_name VARCHAR(10) NOT NULL);

CREATE VIEW PCTC_X
AS
SELECT cov_num, tran_num, payor_name
FROM PCTC
WHERE EXISTS
(SELECT *
FROM PTC
WHERE PTC.tran_num = PCTC.tran_num)
WITH CHECK OPTION;

This is tricky and lots of experienced programmers do not know it. The WITH CHECK OPTION will force the SQL engine to evaluate the WHERE clause on every update, insert and delete. This is portable ANSI/ISO Standard SQL. No, triggers cannot be optimized or ported among SQL products. And they are procedural code :frowning:

Such views have to be updatable. All your work has to be done thru this view (use the GRANT/DENY stuff in the DCL to protect the base table). You can add a row to PTC, but it will not show up in PCTC_X until you add one or more matching rows in PCTC.

Write a procedure to handle insertions into two tables in one transaction. There are some tricks here, but that is another topic. See why I say it take 3-5 years to become a good SQL programmer? :grinning: