Member Avatar for jimbo84

Hi,

Im learning SQL and I've hit a bit of a wall early on....

I have these 2 statements

SELECT AcNum, FamName, GiveName, DeptNum, count(AcNum)
FROM academic
NATURAL JOIN interest
GROUP BY AcNum
HAVING count(AcNum) < 4
SELECT GiveName, FamName, DeptNum, AcNum
FROM academic NATURAL JOIN department
WHERE LOWER(state)= 'ny';

They both work fine independently, but how can I make it one statement and keep the natural join.

  • 4 Contributors
  • 3 Replies
  • 83 Views
  • 16 Hours Discussion Span
  • Latest Post Latest Post by cgyrob
Member Avatar for lighthead

1) I believe NATURAL JOIN is not supported in MSSQL.
2) You cannot select FamName, GiveName, DeptNum without group by including them.

You must be using a different database server, surely not MSSQL.
Regarding your question about combining the two, the columns in the tables are needed to further answer your question.

Member Avatar for sknake

lighthead is right -- this doesn't appear to be MSSQL and you need to group by the additional fields you are selecting. I think it would look like this:

SELECT AcNum, FamName, GiveName, DeptNum, count(AcNum)
FROM academic NATURAL JOIN interest
WHERE LOWER(state)= 'ny'
GROUP BY AcNum, FamName, GiveName, DeptNum
HAVING count(AcNum) < 4
Member Avatar for cgyrob

Natural Joins can be used in Oracle, but I have never used them. They are like using the 'on' or 'using' construct for inner joins but the construct will distinguish which fields are the same and join them so you do not need to explicitly name them.

Your DB has to be designed so that the joining fields have the same name, which is probably why I never use them.

Besides I like controlling what is being joined myself, just call me a control freak.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.