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.
Positive_Coaching_AllianceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Positive_Coaching_AllianceAuthor Commented:
Hi    chapmandew: and szigeti,

Thank you very much for you help.

I really appreciate it!

JR
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.