October 22, 2012 at 8:35 am
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
October 22, 2012 at 8:59 am
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
October 22, 2012 at 11:09 am
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
October 22, 2012 at 11:55 am
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/
October 22, 2012 at 12:05 pm
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
October 22, 2012 at 12:08 pm
ksharpe (10/22/2012)
Hi SeanThanks 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/
October 22, 2012 at 12:10 pm
Great, thanks. I`ll figure it out as soon as possible.
kim
October 22, 2012 at 12:22 pm
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/
October 22, 2012 at 12:26 pm
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
October 22, 2012 at 12:28 pm
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/
October 22, 2012 at 12:29 pm
so many tricks, so little time!
October 22, 2012 at 1:49 pm
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.
October 22, 2012 at 2:31 pm
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...
October 22, 2012 at 2:33 pm
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/
October 22, 2012 at 3:08 pm
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