select top 1 in subquery returns multiple records

  • 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 ...)):

    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?

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

    I’m sure I’m missing something quite obvious but I don’t see it.

    Thanks in advance for your assistance.

    kim

  • 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 - MCSomething someday:-)
    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 😎
    I want to win the lotto 😀
    I want a gf like Tiffa :w00t: Oh wait I'm married!:-D

  • 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

    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

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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

  • ksharpe (10/22/2012)


    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

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

    kim

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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

  • 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. 😀

    _______________________________________________________________

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • so many tricks, so little time!

  • ok, here's my create table for the table listing the courses taken by a contact:

    insert #contact_courses

    select 1234, 'course name', 'TRG2155', 'Introduction to HACCP Planning' union all

    select 1234, 'course name', 'TRG2156', 'HACCP1' union all

    select 1234, 'course name', 'TRG2157', 'HACCP2' union all

    select 1234, 'course name', 'TRG2158', 'HACCP3' union all

    select 2345, 'course name', 'TRG2201', 'Microbiology V' union all

    select 2345, 'course name', 'TRG2318', 'Ingredient Labelling' union all

    select 2345, 'course name', 'TRG2156', 'HACCP1' union all

    select 2234, 'course name', 'TRG2201', 'Microbiology V' union all

    select 2234, 'course name', 'TRG2202', 'Microbiology VI' union all

    select 5513, 'course name', 'TRG2160', 'Microbiology I' union all

    select 5513, 'course name', 'TRG2161', 'Microbiology II' union all

    select 5513, 'course name', 'TRG2184', 'Microbiology III' union all

    select 2115, 'course name', 'TRG1901', 'Allergens and Pathogens' union all

    select 2115, 'course name', 'TRG1902', 'Preparing for Audits' union all

    select 2115, 'course name', 'TRG1903', 'SQF Certification Level 1' union all

    select 2115, 'course name', 'TRG1904', 'SQF Certification Level 2' union all

    select 2115, 'course name', 'TRG2001', 'SQF Certification Level 3' union all

    select 2115, 'course name', 'TRG2002', 'BRC Certification' union all

    select 2115, 'course name', 'TRG2003', 'Audits for Internal Auditors'

    which seems to work and I can list the courses that a contact has taken, by contact accountno

    however, I can't define a foreign key referring to a temporary file I'm told, so I can't join the files to give me even the list with all the courses a contact, let alone the top 1 row only or the course start date.

    is there a way to do this?

    thanks amuch.

    kim

    p.s. mea culpe! in the real world, well here @ the centre @ least, all course information is contained in maincourses, not in the contact_courses file. you must have thought our db quite unnormalized, but of course not significant here.

  • I assume this is a GoldMine database. If I understand your system correctly, try this:

    SELECT c1.accountno,

    c1.contact,

    contsupp.contact,

    SUBSTRING(contsupp.contsupref, 1, 3),

    contsupp.country,

    contsupp.accountno

    FROM contsupp JOIN contact1 c1

    ON contsupp.ACCOUNTNO = c1.ACCOUNTNO

    WHERE contsupp.contact = 'course name'

    AND SUBSTRING(contsupp.contsupref, 1, 3) = 'trg'

    GROUP BY

    c1.accountno,

    c1.contact,

    contsupp.accountno,

    contsupp.contact,

    contsupp.contsupref,

    contsupp.country

    It could be improved, but I think this is good enough to get you where you want to be...

  • You still never posted the last table, only the inserts for it. So I had to make some more assumptions about your tables.

    Then what you posted as sample data did not really match what you stated you want as output.

    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)

    But then you inserted contact_course rows that were in that range. I commented out those inserts in my "final" code below. I think this is what you are looking for. At the very least it should be close enough that you can finish it.

    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

    (

    CourseCode 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'

    create table #contact_courses

    (

    AccountNo int,

    SomePointlessColumn varchar(50),

    CourseCode varchar(25),

    CourseName varchar(50)

    )

    insert #contact_courses

    select 1234, 'course name', 'TRG2155', 'Introduction to HACCP Planning' union all

    select 1234, 'course name', 'TRG2156', 'HACCP1' union all

    select 1234, 'course name', 'TRG2157', 'HACCP2' union all

    select 1234, 'course name', 'TRG2158', 'HACCP3' union all

    select 2345, 'course name', 'TRG2201', 'Microbiology V' union all

    select 2345, 'course name', 'TRG2318', 'Ingredient Labelling' union all

    select 2345, 'course name', 'TRG2156', 'HACCP1' union all

    select 2234, 'course name', 'TRG2201', 'Microbiology V' union all

    select 2234, 'course name', 'TRG2202', 'Microbiology VI' union all

    select 5513, 'course name', 'TRG2160', 'Microbiology I' union all

    select 5513, 'course name', 'TRG2161', 'Microbiology II' union all

    select 5513, 'course name', 'TRG2184', 'Microbiology III' union all

    select 2115, 'course name', 'TRG1901', 'Allergens and Pathogens' union all

    select 2115, 'course name', 'TRG1902', 'Preparing for Audits' union all

    select 2115, 'course name', 'TRG1903', 'SQF Certification Level 1' union all

    select 2115, 'course name', 'TRG1904', 'SQF Certification Level 2'

    --union all

    --select 2115, 'course name', 'TRG2001', 'SQF Certification Level 3' union all

    --select 2115, 'course name', 'TRG2002', 'BRC Certification' union all

    --select 2115, 'course name', 'TRG2003', 'Audits for Internal Auditors'

    --select * from #contact

    --select * from #Courses

    --select * from #contact_courses

    declare @MinDate datetime = '20100101'

    select AccountNo, ContactName, CourseCode, StartDate from

    (

    select c.AccountNo, c.ContactName, co.CourseCode, co.StartDate, ROW_NUMBER() over(partition by c.AccountNo order by co.StartDate desc) as RowNm

    from #contact c

    left join #contact_courses cc on cc.AccountNo = c.AccountNo

    left join #Courses co on co.CourseCode = cc.CourseCode and co.StartDate >= @MinDate

    ) x

    where x.RowNm = 1

    drop table #contact

    drop table #Courses

    drop table #contact_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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • As an alternative to my previous post, this should work too:

    SELECT DISTINCT c1.accountno,

    c1.contact,

    contsupp.contact,

    SUBSTRING(contsupp.contsupref, 1, 3),

    contsupp.country,

    contsupp.accountno

    FROM contsupp JOIN contact1 c1

    ON contsupp.ACCOUNTNO = c1.ACCOUNTNO

    WHERE contsupp.contact = 'course name'

    AND SUBSTRING(contsupp.contsupref, 1, 3) = 'trg'

    Just keep in mind that GoldMine's roots are entrenched in dBase, not SQL.

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply