One to many I guess?

[Mike Hughes]

I have a query.
It has two tables, one CINT3 AND one NOLDBA_COURT_CV_002_PARTIES the tables are joined by the field DOCKET_NO.

The problem I have is that when the query is run and there is more than one NAME_FIRST, NAME_LAST, ETC. I end up with several lines with the same DOCKET_NO if there are more than one NAME_FIRST, NAME_LAST, ETC.

I want one line with the DOCKET_NUMBER and all NAME_FIRST, NAME_LAST, ETC, not several.
I have created the code below and it gives me all the FIRST_NAME,LAST_NAME, ETC associated to the DOCKET_NUMBER on a single line but then that line repeats itself several time.
Can someone help me change the query to have one line for each DOCKET_NUMBER with all the NAME_FIRST, NAME_LAST,ETC without repeating the lines.

I'm sure this is confusing but it is the only way I can explain it


SELECT DISTINCT
a.[DATE RUN],
a.COURT,
a.RECEIVED,
a.SCHEDULED,
a.DOCKET,
a.TYPE,
a.ACTION,
b.NAME_FIRST AS [MEM B FIRST],
b.NAME_LAST AS [MEM B LAST],
b.DT_BIRTH AS [MEM B DOB],
b.SSN AS [MEM B SSN],
c.NAME_FIRST AS [MEM C FIRST],
c.NAME_LAST AS [MEM C LAST],
c.DT_BIRTH AS [MEM C DOB],
c.SSN AS [MEM C SSN]
FROM
CINT3 a,
NOLDBA_COURT_CV_002_PARTIES b,
NOLDBA_COURT_CV_002_PARTIES c
WHERE
b.SEQ_EVENT_GLOBAL_END=0 and
c.SEQ_EVENT_GLOBAL_END=0 and
b.DOCKET_NO = a.DOCKET and
c.DOCKET_NO = a.DOCKET

 

[jdraw]

So, for clarity, if there are 4 people associated with a Docket, you only 1 record.

One record per docket regardless of how many people are linked to that docket.
Is that correct?

 

[Mike Hughes]

Yes, that is correct

 

[Mike Hughes]

I have attached a sample DB
For each docket number the query is returning two records. One for each names attached to the docket number. The names on the two records are from the same family and I want one line with the docket number and both names.

 

[jdraw]

Not as a query with all people on 1 line per docket number.

I was trying 1 name per docket (and using First).

Perhaps a custom function.

 

[boblarson]

I think you want this:

http://allenbrowne.com/func-concat.html

 

[Mike Hughes]

Thanks, I give it a try