need SQL query advice on cross referencing three tables

I'm trying to lear SQL. I'm a total noob, please bear with me.

I have these two queries, both of which work, but I'm hoping to get some advice as to which is preferable, or if there is a better way of doing it.

SELECT AVG(price) AS PriceAverage FROM bib inner join (SELECT sub.subcode, accno FROM sub inner join indx on sub.subcode = indx.subcode where subject = 'SOCIAL CHANGE')A on bib.accno = A.accno;

Open in new window



SELECT AVG(price) AS PriceAverage FROM bib inner join (select accno,subcode from indx where subcode in ( select subcode from sub where subject = 'SOCIAL CHANGE'))A on bib.accno = A.accno;

Open in new window


Basically, I have to cross reference three tables to get the info  I'm looking for.

Thanks in advance, and apologies for any cringe factor...
Kyle
LVL 25
Kyle HamiltonData ScientistAsked:
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.

PortletPaulfreelancerCommented:
I would simply suggest you try more joins together, you don't have to stop at 2 tables then use a subquery. Like this:

SELECT
  AVG(????.price) AS PriceAverage
FROM bib
INNER JOIN sub ON bib.accno = sub.accno
INNER JOIN indx ON sub.subcode = indx.subcode
WHERE ????.subject = 'SOCIAL CHANGE'

Trouble is there are 2 instances where you have failed to reference a field with its table alias. You should always (always!) reference each field by the table it comes from.
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
Kyle HamiltonData ScientistAuthor Commented:
That looks so much better, thank you!

Best,
Kyle
0
PortletPaulfreelancerCommented:
:) no problem. cheers. Paul
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.