Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Basic Sql Join query from 4 tables

 
deepa bhaskar
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, Can anyone help me select , sql join query for 4 tables.
fName,lName from Employee table and address from address_info table ?
the attachments are below. Thankyou.


query?


 
Campbell Ritchie
Sheriff
Posts: 51368
87
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the Ranch anyway.
As Liutauras says, you have arrived in the wrong place; the “Ranch Office” forum is for discussing the website itself. Let's try one of our databases fora.
I shall also see if I can add code tags to your post. Always use the tags; with any luck you will see how much better it looks
 
Dave Tolls
Ranch Hand
Posts: 2095
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
deepa bhaskar wrote:
fName,lName from Employee table and address from address_info table ?




What is wrong with that query as it stands?

The one thing I can see is you are JOINing to the empdept_idadd table twice, so doubling the number of matches, when I suspect you actually only want the rows in that table that match both the deptid and the addID, which would be a single JOIN clause.
 
deepa bhaskar
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you i will use code tags.

Thank you Campbell Ritchie.I am getting the below error code.
Error Code: 1066. Not unique table/alias: 'employee'

Though when creating the table I've selected pk,nn,uq. How to resolve it?thanks for your help.
 
Dave Tolls
Ranch Hand
Posts: 2095
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ah, missed the mistake (though the double join is probably also a mistake):

This is your current FROM:


Unless you use aliases, you cannot join a table to itself.
In the above you are selecting from employee, and then doing an INNER JOIN to the same table, without an alias.
To do that you would need something like:


Now, I suspect you don't actually want this.
You probably want:


You'll have to check for typos, but essentially you are trying to join employee to address. The only route for that is via empdeptID (which is on both employee and empdept_addID) which gives you your addID. And the a join to the address table completes the link.
 
deepa bhaskar
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Wow, Thankyou so much Dave Tolls, I got it. I appreciate your help. Thankyou.
SELECT e.lName,a.address
FROM employee e
JOIN Empdept_idadd as eda ON eda.empdeptID = e.empdeptid
JOIN Address_info as a ON a.addID = eda.addID;

And can you suggest, where can I learn sql and stored procedures? thanks.
 
Dave Tolls
Ranch Hand
Posts: 2095
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Depends on what database you are using.

I picked up pretty much all my Oracle stuff from the Oracle documentation.
Same for MySQL and SQL Server.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic