select top 1 in subquery returns multiple records

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » select top 1 in subquery returns multiple...

28 posts, Page 1 of 3

1 2 3 »»»

select top 1 in subquery returns multiple records

Rate Topic

Display Mode

Topic Options

Author

Message

ksharpe

Posted Monday, October 22, 2012 8:35 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 20, 2012 7:20 AM
Points: 76, Visits: 67

Hi

I have two tables:

table contact1: obviously contact information
table contsupp: a catch-all for a various types of detail, 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.

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

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

when I run the subquery in brackets, it gives me only one record; does top 1 not work in a nested query?

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

Then, I change it to look like this (someone suggested it to me, I don’t know about
OVER(PARTITION BY ...)):

[CODE=plain]
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) 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
[/code]

Here, the where conditions are in the sub-query WHERE clause, but that doesn’t work for in the first query no matter what.

So, this worked fine and gave me only the first course detail record.

But, when I add the next line, it goes back to giving me all of the courses for a contact. If the join has returned only 1 record, shouldn’t it join on just that record, that the contsupp.contsupref record referenced in the join maincourses on should be that record only?

[code=plain]
inner join maincourses mc on mc.code = contsupp.contsupref and mc.ucrstart > '01/01/2010' and mc.code like 'trg%'
[/code]
I’m sure I’m missing something quite obvious but I don’t see it.

Thanks in advance for your assistance.

kim

Post #1375463

Want a cool Sig

Posted Monday, October 22, 2012 8:58 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, April 26, 2013 2:58 PM
Points: 221, Visits: 452

You're adding a 3rd table which is not what you originally asked for. If "maincourses" is a 1 to many relationship then without restricting it to a single row you'll get multiple rows in your dataset. Somehow you have to make the record distinct enough to get a single row returned. Not enough info.

---------------------------------------------------------------
Mike Hahn - Future MCM 2025 Smile

Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar Hehe

I want a personal webpage Cool

I want to win the lotto BigGrin

I want a gf like Tiffa w00t

Post #1375478

ksharpe

Posted Monday, October 22, 2012 11:09 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 20, 2012 7:20 AM
Points: 76, Visits: 67

thank you Want a cool sig for your very prompt reply.

ah, yes, quite right, I did mention only the two tables at the beginning, apologies.

maincourses has a one-to-one relationship with the course code - field contsupref on table contsupp

i.e. for the - hopefully one only - row returned from contsupp, the course code field=contsupref will be unique in maincourses

so I thought that if one row is returned from contsupp, it would be the course code (contsupref) on which main courses will be joined.

the purpose of the mc.ucrstart > '01/01/2010' in the maincourse is to return rows for only those courses that were taken after January 1st 2010;

the condition mc.code like 'trg%' is actually redundant, as the condition in the main query:

WHERE contsupp.contact = 'course name' and contsupp.contsupref like `

should return only course rows, those rows in contsupp in which the contact='course name' and the contsupref field is like 'trg%' on which the maincourses is joined.

a contact will never take a two courses with the same course code.

here is some simplified data to give you a better idea

[code=other]
table contact1:

accountno contact (contact name)

1234 sam
2345 geoffrey
2234 daniel
5513 julia
2115 abbey

table contsupp:

accountno contact contsupref country

1234 course name TRG2155 Introduction to HACCP Planning
1234 course name TRG2156 HACCP1
1234 course name TRG2157 HACCP2
1234 course name TRG2158 HACCP3

2345 course name TRG2201 Microbiology V
2345 course name TRG2318 Ingredient Labelling
2345 course name TRG2156 HACCP1

2234 course name TRG2201 Microbiology V
2234 course name TRG2202 Microbiology VI

5513 course name TRG2160 Microbiology I
5513 course name TRG2161 Microbiology II
5513 course name TRG2184 Microbiology III

2115 course name TRG1901 Allergens and
2115 course name TRG1902 Preparing for Audits
2115 course name TRG1903 SQF Certification Level 1
2115 course name TRG1904 SQF Certification Level 2
2115 course name TRG2001 SQF Certification Level 3
2115 course name TRG2002 BRC Certification
2115 course name TRG2003 Audits for Internal Auditors

table maincourses:

code start date

TRG1901 10/01/2009
TRG1902 10/08/2009
TRG1903 11/14/2009
TRG1904 12/10/2009

TRG2001 02/12/2010
TRG2002 04/21/2010
TRG2003 05/06/2010

TRG2155 01/01/2010
TRG2156 01/01/2010
TRG2157 01/01/2010
TRG2158 01/01/2010

TRG2160 01/01/2010
TRG2161 01/01/2010
TRG2184 01/01/2011

TRG2201 01/01/2012
TRG2202 01/01/2012

TRG2318 02/09/2008

the report I want the query to report is:

1234 sam TRG2155 01/01/2010
2345 geoffrey TRG2201 01/01/2012 (first row on file and course taken after January 2010)
2234 daniel TRG2201 01/01/2012 (first row on file and course taken after January 2010)
5513 julia TRG2160 01/01/2010
2115 abbey (no courses since the beginning of 2010)

I hope this makes it a little bit easier to see what it is I`m looking for.

thanks so much
kim

Post #1375621

Sean Lange

Posted Monday, October 22, 2012 11:55 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 8,641, Visits: 8,273

I hope this makes it a little bit easier to see what it is I`m looking for.

Actually it really doesn't make it any easier. What would make it easier is if you turned that into consumable ddl and data. That means create table statements and insert statements. Then we can help you build the query. We are all volunteers on here and with what you posted it takes way too much effort to get this to a point where we can start helping.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1375646

ksharpe

Posted Monday, October 22, 2012 12:05 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 20, 2012 7:20 AM
Points: 76, Visits: 67

Hi Sean

Thanks for your very prompt reply - you all are amazing. I recognize that you are volunteers and for those of us who rely on your generosity of time, you are invaluable.

I will endeavour to write the create, insert & query statements using the data I`ve shown above. in actuality, I`ve created only a few tables in my life using sql but I`ve seen examples and I`ll figure it out. I`ll return when I`m able to create a table and insert the data but it`ll take me a bit.

thanks so much. I really hope that one day I`ll be able to help someone else.

kim

Post #1375651

Sean Lange

Posted Monday, October 22, 2012 12:08 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 8,641, Visits: 8,273

ksharpe (10/22/2012)

No need to generate thousands of rows or anything. Just enough data to meet the challenges you are facing. Normally I would try to parse together the stuff posted but I just couldn't quite figure out what was what with yours. Post back when you have it situated and we can figure it out pretty quickly.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1375656

ksharpe

Posted Monday, October 22, 2012 12:10 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 20, 2012 7:20 AM
Points: 76, Visits: 67

Great, thanks. I`ll figure it out as soon as possible.

kim

Post #1375660

Sean Lange

Posted Monday, October 22, 2012 12:22 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 8,641, Visits: 8,273

ksharpe (10/22/2012)

Great, thanks. I`ll figure it out as soon as possible.

kim

I think this is what you mean by 2 of the tables. The other table(s) is still pretty unclear what is going on there.

create table #contact
(
	AccountNo int,
	ContactName varchar(25)
)

insert #contact
select 1234, 'sam' union all
select 2345, 'geoffrey' union all
select 2234, 'daniel' union all
select 5513, 'julia' union all
select 2115, 'abbey'

create table #Courses
(
	Code varchar(25),
	StartDate datetime
)

insert #Courses
select 'TRG1901', '10/01/2009' union all
select 'TRG1902', '10/08/2009' union all
select 'TRG1903', '11/14/2009' union all
select 'TRG1904', '12/10/2009' union all
select 'TRG2001', '02/12/2010' union all
select 'TRG2002', '04/21/2010' union all
select 'TRG2003', '05/06/2010' union all
select 'TRG2155', '01/01/2010' union all
select 'TRG2156', '01/01/2010' union all
select 'TRG2157', '01/01/2010' union all
select 'TRG2158', '01/01/2010' union all
select 'TRG2160', '01/01/2010' union all
select 'TRG2161', '01/01/2010' union all
select 'TRG2184', '01/01/2011' union all
select 'TRG2201', '01/01/2012' union all
select 'TRG2202', '01/01/2012' union all
select 'TRG2318', '02/09/2008'

select * from #contact
select * from #Courses

drop table #contact
drop table #Courses

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1375666

ksharpe

Posted Monday, October 22, 2012 12:26 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, November 20, 2012 7:20 AM
Points: 76, Visits: 67

You are FAST!

ok, give me a few minutes and I'll see if I can create the contsupp table from your examples.

I'll be right back - I'll try it out on my server.

thanks so much Sean.

kim

Post #1375669

Sean Lange

Posted Monday, October 22, 2012 12:28 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:17 PM
Points: 8,641, Visits: 8,273

ksharpe (10/22/2012)

You are FAST!

ok, give me a few minutes and I'll see if I can create the contsupp table from your examples.

I'll be right back - I'll try it out on my server.

thanks so much Sean.

kim

LOL. I have learned a few tricks about converting what you posted into something usable by doing it a lot. You can get pretty creative with the Find - Replace tool in SSMS. BigGrin

Post #1375670

« Prev Topic | Next Topic »

28 posts, Page 1 of 3

1 2 3 »»»

Permissions