sub-query to return first record only from a related table 2

[kims212]

Hi

table contact1: obviously contact information
table contsupp: a catch all for a one to many per contact

we keep course detail in contsupp; each course a contact has taken is a row in this table - the field names are not descriptive.

I want to list only one course per contact, doesn't matter which, the first one it encounters is fine.

this code works fine except that it gives me all the courses that a contact has taken, not just the first.

when I run just the select in brackets, it gives me only one record.

also, when I put accountno = contsupp.accountno in where clause in the sub query, I get no rows returned.
when I move the where clause "contsupp.contact = 'course name' and contsupp.contsupref like 'trg%'" in the sub-query where clause - which is where I thought it ought to be because those fields are in contsupp - it gives me every contsupp record for a contact, not just courses.

I don't understand what I'm doing wrong.

SELECT   c1.accountno, c1.contact, contsupp.contact, contsupp.contsupref, contsupp.country, contsupp.accountno
FROM     contact1 c1
JOIN     contsupp
ON       contsupp.accountno =
         (
         SELECT  TOP 1 accountno 
         FROM    contsupp
	 WHERE   accountno = c1.accountno
         )
where contsupp.contact = 'course name' and contsupp.contsupref like 'trg%'
order by c1.contact

thanks in advance for your help.

kim

 

[fredericofonseca]

sample data and sample desired output would be helpful.

From what you say there, it looks like there are multiple records on contsupp with the same accountno - if this is indeed the case then it is correct you get several records back.

for the "top 1 xxx" to work as you wish, the xxx needs to be a unique field or at least one that if you join with other fields on contact1 makes it unique

There may be several solutions for you case - but we will need sample data, desired output, and possibly table definition (including indexes) and volume (record counts per table) so we can give you what we may consider is(are) the best option(s)






Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[bborissov]

SELECT   c1.accountno, c1.contact, contsupp.contact, contsupp.contsupref, contsupp.country, contsupp.accountno
FROM     contact1 c1
INNER JOIN  (SELECT *, ROW_NUMBER() OVER (PARTITION BY accountno ORDER BY ?????) AS RowN
                  FROM   contsupp
             where contsupp.contact = 'course name' and
                   contsupp.contsupref like 'trg%') contsupp
        ON contsupp.accountno = c1.accountno AND
           RowN = 1
order by c1.contact

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server

 

[kims212]

Thank you both for your very prompt replies.

[bold]Borislov:[/bold] your query does indeed work, however, when I add, after the line RowN = 1,

inner join maincourses mc on mc.code = contsupp.contsupref and mc.ucrstart > '01/01/2010' and mc.code like 'trg%'

to retrieve on the courses since the beginning of 2010, I get all of the courses for each person again.

[bold]Frederico:[/bold]

first, a question - or two: if when the subquery is run as a standalone query it works correctly and returns only the first course record for an account number, then why doesn't it work properly as a subquery.

yes, there are multiple records on contsupp with the same accountno, the link between contact1 and the contact detail records in contsupp; a contact may take several courses distinguished by only the course code. isn't that the way a many-to-one relationship works?

I'm not sure how much data you would like , but I'm listing all of the records on the contsupp (detail) table for one contact only; other contacts would have similar records with the exception of the number of courses. I don't know how to get the columns to line up, I did insert the [tab] [/tab] tags, but perhjaps I don't understand how that works, but the columns are:

account number
contsupp.contact - detail type, i.e. a row with a detail type of 'Course Name' contains data for a course taken by the contact
contsupp.rectype - always 'P' for course detail records & some others as well
contsupref - detail data dependent on detail type
contsup.title - additional detail data (course date)

what I want to return is only the first course detail record - the one I've highlighted
[tab]
A7121242416&1*VN!Ada A1092533626(3T%.)Joh O 1000001000 Ada Wong-Ferenci NULL
[highlight #FF99FF]
A7121242416&1*VN!Ada Course Name P TRG 1326 Nov 26-27
[/highlight]
A7121242416&1*VN!Ada Course Name P TRG 1329 Nov 28-29
A7121242416&1*VN!Ada Course Name P TRG 1318 May 9-11
A7121242416&1*VN!Ada Course Name P TRG 1312 May 7-8
A7121242416&1*VN!Ada Course Name P TRG 1378 May 14-15
A7121242416&1*VN!Ada Course Name P TRG 1322 June 24-25
A7121242416&1*VN!Ada Course Name P TRG1516 20-Jul
A7121242416&1*VN!Ada Course Name P TRG1654 13-Nov
A7121242416&1*VN!Ada Course Name P TRG1515
A7121242416&1*VN!Ada Course Name P TRG1746 19-Aug
A7121242416&1*VN!Ada Course Name P 22-Jul
A7121242416&1*VN!Ada Course Name P TRG1974 15-Apr
A7121242416&1*VN!Ada E-mail Address P ada.wongferenci@goldenboyfoods.com
A7121242416&1*VN!Ada E-mail Address P a.wongferenci@goldenboyfoods.com
A7121242416&1*VN!Ada E-mail Address P ada.wongferenci@hotmail.com
A7121242416&1*VN!Ada WEB SITE P

http://www.goldenboyfoods.com

[/tab]

I was going to attach a Word document showing the table definitions but I couldn't figure out how to do it. are you ok without it or should I email it?

I apologize, but I don't understand when you say:
"for the "top 1 xxx" to work as you wish, the xxx needs to be a unique field or at least one that if you join with other fields on contact1 makes it unique"

shouldn't the sub-query just create a list as if it was a regular query returning the records that match the criteria and then just return the top one? as I say, it works when the sub-query runs as a standalone.

thanks so much
kim

 

[fredericofonseca]

let me try and explain the top 1 query (and at the same time the query Borislav gave you (which by the looks of your supplied data may indeed be the correct solution))

Lets consider your query (with only required columns to show how it works)

SELECT   c1.accountno, c1.contact, contsupp.contsupref
FROM     contact1 c1
JOIN     contsupp
ON       contsupp.accountno =
         (
         SELECT  TOP 1 accountno 
         FROM    contsupp
	 WHERE   accountno = c1.accountno
         )
where contsupp.contact = 'course name' and contsupp.contsupref like 'trg%'
order by c1.contact

and lets use the following data

table contact1
accountno contact
1          name1
2          name2


Table contsupp

accountno contact      contsupref
1         course name  trg 1
1         course name  trg 2
1         email        trg 3
2         course name  trg 1
2         course name  trg 2
2         course name  trg 3

When you execute the following query individually (for testing as you did)
SELECT TOP 1 accountno
FROM contsupp
WHERE accountno = 1

you get just 1 row with value of "1" - this is indeed what you would expect with the top 1.

so now lets manually hardcode this value on the full query (as if it was just for a single account)

SELECT   c1.accountno, c1.contact, contsupp.contsupref
FROM     contact1 c1
JOIN     contsupp
ON       contsupp.accountno =
         (1 [b]-- this is the result of the select top 1 query[/b]
         )
where contsupp.contact = 'course name' and contsupp.contsupref like 'trg%'
order by c1.contact

Now... the above is saying "get me all records from contsupp where contsupp.accountno = 1"
As a result, you get 2 records back as there are 2 records on contsupp with accountno = 1 and contsupref like 'trg%'

If instead of using accountno you use a field that was unique within that table, for example a identity field, then it would return only 1 record as the uniquess of that field would give you the desired result.

When we don't have a unique field, one of the ways to deal with it is to do as Borislav

SELECT *, ROW_NUMBER() OVER (PARTITION BY accountno ORDER BY ?????) AS RowN
                  FROM   contsupp
             where contsupp.contact = 'course name' and
                   contsupp.contsupref like 'trg%'

This query retrieves all the records from contsupp, and while doing it it adds a number (RowN) which is unique within the "partition by" clause, in this case based on accountno
The output of the above based on my data would be

accountno contact      contsupref RowN
1         course name  trg 1        1
1         course name  trg 2        2
1         email        trg 3        3
2         course name  trg 1        1
2         course name  trg 2        2
2         course name  trg 3        3

So now that we have a way of uniquely identify a record we use that same field(s) on our join to the main query

SELECT   c1.accountno, c1.contact, contsupp.contact, contsupp.contsupref, contsupp.country, contsupp.accountno
FROM     contact1 c1
INNER JOIN  (SELECT *, ROW_NUMBER() OVER (PARTITION BY accountno ORDER BY ?????) AS RowN
                  FROM   contsupp
             where contsupp.contact = 'course name' and
                   contsupp.contsupref like 'trg%') contsupp
[b]        ON  contsupp.accountno = c1.accountno 
        AND RowN = 1[/b]
order by c1.contact

and this gives us a single contsupp record per accountno

With regards to the new question you have to Borislav
The extra records you seem to be getting are not from contsupp anymore, but rather due to the link to maincourse

Again this is likely due to several records on maincourse with the same "code"

I hope I explained myself correctly - hard sometimes to make these things clear.







Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[kims212]

Hi Frederico

thanks for your again prompt response - and for all of the effort to explain the processing.

I thought it easiest to insert my questions at the end of each of your explanations:


let me try and explain the top 1 query (and at the same time the query Borislav gave you (which by the looks of your supplied data may indeed be the correct solution))

Lets consider your query (with only required columns to show how it works)
CODE

SELECT c1.accountno, c1.contact, contsupp.contsupref
FROM contact1 c1
JOIN contsupp
ON contsupp.accountno =
(
SELECT TOP 1 accountno
FROM contsupp
WHERE accountno = c1.accountno
)
where contsupp.contact = 'course name' and contsupp.contsupref like 'trg%'
order by c1.contact


and lets use the following data
CODE

table contact1
accountno contact
1 name1
2 name2


Table contsupp

accountno contact contsupref
1 course name trg 1
1 course name trg 2
1 email trg 3
2 course name trg 1
2 course name trg 2
2 course name trg 3


When you execute the following query individually (for testing as you did)
SELECT TOP 1 accountno
FROM contsupp
WHERE accountno = 1

you get just 1 row with value of "1" - this is indeed what you would expect with the top 1.

but I would expect the same result in the nested select using the same data.

in your example above, there are two records in your contsupp table with the account number of "1" - and contsupp.contact = 'Course Name' and contsupp.contsupref like 'TRG%' - and it returns only one record. so why doesn't it return multiple records as it does with the nested select inside a 'primary' select?

when testing the query on its own, I've used a specific account number - with multiple contsupp records - as well as without an account number and just asking for the top 1 record with contsupp.contact = 'Course Name' and contsupp.contsupref like 'TRG%' of which there are of course many and still get only one record.

so now lets manually hardcode this value on the full query (as if it was just for a single account)

CODE

SELECT c1.accountno, c1.contact, contsupp.contsupref
FROM contact1 c1
JOIN contsupp
ON contsupp.accountno =
(1 -- this is the result of the select top 1 query
)
where contsupp.contact = 'course name' and contsupp.contsupref like 'trg%'
order by c1.contact

Now... the above is saying "get me all records from contsupp where contsupp.accountno = 1"

if the select is exactly the same when nested or run individually isn't it then asking for the same thing - "all the records from contsupp where contsupp.accountno = 1" how is the request any different nested or run individually?


As a result, you get 2 records back as there are 2 records on contsupp with accountno = 1 and contsupref like 'trg%'

If instead of using accountno you use a field that was unique within that table, for example a identity field, then it would return only 1 record as the uniquess of that field would give you the desired result.

what is the purpose of top 1 then? we don't have a unique field in the first example either, i.e. get the list of records that match the account number and return the first one.

When we don't have a unique field, one of the ways to deal with it is to do as Borislav

CODE

SELECT *, ROW_NUMBER() OVER (PARTITION BY accountno ORDER BY ?????) AS RowN
FROM contsupp
where contsupp.contact = 'course name' and
contsupp.contsupref like 'trg%'


This query retrieves all the records from contsupp, and while doing it it adds a number (RowN) which is unique within the "partition by" clause, in this case based on accountno

The output of the above based on my data would be
CODE

accountno contact contsupref RowN
1 course name trg 1 1
1 course name trg 2 2
1 email trg 3 3
2 course name trg 1 1
2 course name trg 2 2
2 course name trg 3 3


So now that we have a way of uniquely identify a record we use that same field(s) on our join to the main query



the select run individually didn't have a way to uniquely identify the record but was the first record of all the records matching the criteria.


CODE

SELECT c1.accountno, c1.contact, contsupp.contact, contsupp.contsupref, contsupp.country, contsupp.accountno
FROM contact1 c1
INNER JOIN (SELECT *, ROW_NUMBER() OVER (PARTITION BY accountno ORDER BY ?????) AS RowN
FROM contsupp
where contsupp.contact = 'course name' and
contsupp.contsupref like 'trg%') contsupp
ON contsupp.accountno = c1.accountno
AND RowN = 1
order by c1.contact


and this gives us a single contsupp record per accountno

With regards to the new question you have to Borislav
The extra records you seem to be getting are not from contsupp anymore, but rather due to the link to maincourse

as you said above, we are getting only one record returned from contsupp on the nested query with Borislav's query - then why is it matching all of the contsupp records for that account - with different "trg" codes - when I add in the match on maincourses, it's again returning records with other "trg%" codes, not jsut the one on the single record returned from the query; the "trg%" code on maincourses is unique.

if I get only one record from Borislav's query with one "trg", then it should match that "trg" code on maincourses for that "trg" only and return the date for only that course.



Again this is likely due to several records on maincourse with the same "code"

the "trg" code on maincourses is unique

I hope I explained myself correctly - hard sometimes to make these things clear.

I'm sorry to be asking for a further explanation, but it still doesn't make sense. I had a formula in report using Crystal Reports a while ago and there was a similar situation in that what worked correctly when run one way but differently when incorporated into another formula.

I would understand if you decided to give up on me; I've been known to persist until I can make sense of something.

thanks again
kim