Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 23rd, 2007, 04:26 PM
Registered User
 
Join Date: May 2007
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Group by Count * >1?

I am still new and learning SQL and am having trouble with repeated results. I am basically returning records from a table but it keeps repeating the names 5 times each. There are 4 to 5 names. They should only appear one time each with their links. I'm not sure but I think I need to adjust my display section to make this happen or the select code. Not sure. Below is my SQL code followed by the display section in that order.

Code:
SELECT T_ProgramGuests.ProgramID, T_ProgramGuests.GuestName, T_ProgramGuests.GuestDescription, T_ProgramLinks.URL, T_ProgramLinks.Description FROM T_ProgramGuests LEFT OUTER JOIN T_ProgramLinks ON T_ProgramGuests.ProgramID = T_ProgramLinks.ProgramID GROUP BY T_ProgramGuests.ProgramID, GuestName HAVING (COUNT(*) > 1)WHERE (T_ProgramGuests.ProgramID = 9734)
Code:
<%
   While Not RS.Eof
%>

<br />
<strong><% Response.Write RS ("GuestName") %> </strong> <% Response.Write RS("GuestDescription") %><br /><br />
Related Links:<br />
<li class='basic'><A HREF="<%= RS("URL") %>"><%= RS("Description") %></A></li>

<% RS.MoveNext
   Wend
%>
Can I use some variation of this to correct this problem?

Code:
SELECT T_ProgramGuests, GuestName
FROM T_ProgramGuests
GROUP BY T_ProgramGuests.ProgramID, GuestName
HAVING (COUNT(*) > 1)
 
Old May 23rd, 2007, 04:42 PM
Registered User
 
Join Date: May 2007
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I know that count will count records in a table so maybe I didn't ask the right question. There must be some type of sql code I can use to accomplish this. Any ideas?
 
Old May 23rd, 2007, 05:35 PM
Registered User
 
Join Date: May 2007
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm trying to eliminate duplicate returns. Does anyone know how this is done in SQL?
 
Old May 23rd, 2007, 05:45 PM
Registered User
 
Join Date: May 2007
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I found something interesting on this site. But I dont understand how using Max is going to prevent duplicates.

http://sqljunkies.com/Forums/ShowPost.aspx?PostID=12975
 
Old May 23rd, 2007, 09:06 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

hi there..

maybe a distinct will solve your problem???

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========
 
Old May 24th, 2007, 10:28 AM
Registered User
 
Join Date: May 2007
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

gbianchi, actually I've tried distinct before using this query:

SELECT DISTINCT T_ProgramGuests.ProgramID, T_ProgramGuests.GuestName,
T_ProgramGuests.GuestDescription, T_ProgramLinks.URL, T_ProgramLinks.Description
FROM T_ProgramGuests LEFT OUTER JOIN T_ProgramLinks ON T_ProgramGuests.ProgramID = T_ProgramLinks.ProgramID
WHERE (T_ProgramGuests.ProgramID = 9734)

All it does is display the four names in alphabetical order and repeats them and their links. Here is a sample
of the output on screen:

Andrew Gumbel correspondent for the Independent of London and the author of Steal This Vote: Dirty Elections and the Rotten History of Democracy in America

Related Links:
# 'Still Angry, Kerry Supporters Nationwide Try to Change Ohio Elections,' The Boston Globe

Andrew Gumbel correspondent for the Independent of London and the author of Steal This Vote: Dirty Elections and the Rotten History of Democracy in America

Related Links:
# This is a test link for the John Doe name

Andrew Gumbel correspondent for the Independent of London and the author of Steal This Vote: Dirty Elections and the Rotten History of Democracy in America

Related Links:
# This is another test linked created Tuesday May 15 2007at 12 noon by Eric

Andrew Gumbel correspondent for the Independent of London and the author of Steal This Vote: Dirty Elections and the Rotten History of Democracy in America

Related Links:
# 'Steal this Vote,' Andrew Gumbel interview by Julian Brookes, Mother Jones

Andrew Gumbel correspondent for the Independent of London and the author of Steal This Vote: Dirty Elections and the Rotten History of Democracy in America

Related Links:
# 'Democrats Say 2004 election failed in Ohio,' The Washington Post

As you can see it repeats the same name 5 times until all related links to that name have been displayed. Then
it moves onto the next name and does the same thing. How can I prevent this?
 
Old May 24th, 2007, 10:46 AM
Registered User
 
Join Date: May 2007
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok I did a quick test of a basic Distinct query against my table. It did not repeat
names this time, which is good. But for the ID I used when tested in my browser
it still showed all names associated with that table. Now I need it to "only" show
me these names and their links using Distinct or DistinctRow.

Mark Crispin Miller author of The Bush Dyslexicon.
Andrew Gumbel correspondent for the Independent of London
Walter Mebane professor of government at Cornell University
Bryan Jones Donald R. Matthews Distinguished Professor

Related Links:
# 'Democrats Say 2004 election failed in Ohio,' The Washington Post
# 'Still Angry, Kerry Supporters Nationwide Try to Change Ohio Elections,' The Boston Globe
# 'Steal this Vote,' Andrew Gumbel interview by Julian Brookes, Mother Jones
# Mark Crispin Miller's blog

The below are all the ID's and Names inside the T_ProgramGuests table.

9726 Joan Didion
9734 Mark Crispin Miller
9734 Andrew Gumbel
9734 Walter Mebane
9734 Tamara Murphy
9786 Greg Atkinson
9785 Doris Kearns Goodwin
9785 Tom Douglas
9785 Jane Doe
9784 Ryan Smith

So which is better to use?
 
Old May 24th, 2007, 03:50 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

the problem is not in your query.. is in your display..

your query will return a lot of records with the same name if it can join them with the other table...
if you one only one name but a lot of "joined" strings, then you will have to display your data with other method...

I'm correct about what are you trying to achieve???

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========





Similar Threads
Thread Thread Starter Forum Replies Last Post
Iterate count within for-each-group stolte XSLT 1 November 19th, 2007 07:40 PM
Problem with Count() function 4 group of records Odeh Naber Access 0 July 24th, 2007 03:28 AM
putting <element> round group of same elements jefke XSLT 0 May 24th, 2004 10:18 AM
Group By query with count problem ptaylor SQL Language 2 May 12th, 2004 09:27 AM
Help! - How do i count and group this gfowajuh SQL Server 2000 4 August 19th, 2003 05:13 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.