• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

What is wrong with my SQL statement?


SELECT a.ID
       ,a.DateCreated
       ,a.NominatorName
       ,a.NominatorHomePhone
       ,a.NominatorWorkPhone
       ,a.NominatorEmail
       ,a.NominatorAddress
       ,a.NominatorCityStateZip
       ,a.Relationship
       ,a.CoachName
       ,a.CoachHomePhone
       ,a.CoachWorkPhone
       ,a.CoachAddress
       ,a.CoachTeam
       ,a.CoachSport
       ,a.CoachAgeLevel
       ,a.CoachYears
       ,a.CoachAccomplishments
       ,a.CoachPhoto
       ,a.CoachTestimonial
       ,b.CoachId
       ,b.AuthorName
       ,b.AuthorPhone
       ,b.AuthorEmail
       ,b.Relationship
       ,b.Testimonial
FROM Nominations a .Testimonials b;

What is wrong with the above statement?  

When I run this query I received 18,096 records, which in no correct since the Nominations table only has 156-records and the Testimonial table only has 116-records.
0
Positive_Coaching_Alliance
Asked:
Positive_Coaching_Alliance
  • 2
  • 2
2 Solutions
 
chapmandewCommented:
SELECT a.ID
       ,a.DateCreated
       ,a.NominatorName
       ,a.NominatorHomePhone
       ,a.NominatorWorkPhone
       ,a.NominatorEmail
       ,a.NominatorAddress
       ,a.NominatorCityStateZip
       ,a.Relationship
       ,a.CoachName
       ,a.CoachHomePhone
       ,a.CoachWorkPhone
       ,a.CoachAddress
       ,a.CoachTeam
       ,a.CoachSport
       ,a.CoachAgeLevel
       ,a.CoachYears
       ,a.CoachAccomplishments
       ,a.CoachPhoto
       ,a.CoachTestimonial
       ,b.CoachId
       ,b.AuthorName
       ,b.AuthorPhone
       ,b.AuthorEmail
       ,b.Relationship
       ,b.Testimonial
FROM Nominations a
JOIN Testimonials b ON a.NominationID = b.NominationID  --not sure which fields to join on
0
 
szigetiCommented:
What's happening, is since you don't specify a join condition, its joining each row with every other row:

156 rows times 116 rows = 18,096 rows!
0
 
Positive_Coaching_AllianceAuthor Commented:
Hi chapmandew and szigeti,

Thank you very much for both of your answers.

What I am trying to do is list the results from both tables then sort them by the PK and FK such that those records that do not have a relationship also appear.  Basically, I would like to have both tables sorted by their PK and FK relationship with the remaining records that do not have a relationship also listed.

Is this possible?

Thank you in advance for your help.

JR
0
 
szigetiCommented:
Well, the best thing is to think about how the data is related. Does a nomination have one or more testimonials, or does a testimonial have one or more nominations? If there is no relation between them, does it make sense to attempt to join the two? It might be better to just run two seperate selects.

Lets say nomatinations can have one or more testimonials, and there is a column nomination_id in both tables (I'm assuming because I can't tell from the example given). You want to return all nominations, even if there are *no* testimonials, then you use an "outer join":

Check out the SQL below, or this link for further details:
http://en.wikipedia.org/wiki/Join_(SQL)#Outer_joins
SELECT a.ID
       ,a.DateCreated
       ,a.NominatorName
       ,a.NominatorHomePhone
       ,a.NominatorWorkPhone
       ,a.NominatorEmail
       ,a.NominatorAddress
       ,a.NominatorCityStateZip
       ,a.Relationship
       ,a.CoachName
       ,a.CoachHomePhone
       ,a.CoachWorkPhone
       ,a.CoachAddress
       ,a.CoachTeam
       ,a.CoachSport
       ,a.CoachAgeLevel
       ,a.CoachYears
       ,a.CoachAccomplishments
       ,a.CoachPhoto
       ,a.CoachTestimonial
       ,b.CoachId
       ,b.AuthorName
       ,b.AuthorPhone
       ,b.AuthorEmail
       ,b.Relationship
       ,b.Testimonial
FROM Nominations a LEFT OUTER JOIN Testimonials b ON a.nomination_Id = b.nomination_id;

Open in new window

0
 
Positive_Coaching_AllianceAuthor Commented:
Hi    chapmandew: and szigeti,

Thank you very much for you help.

I really appreciate it!

JR
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now