?
Solved

How to quiry from the same table 2 times

Posted on 2007-11-23
11
Medium Priority
?
247 Views
Last Modified: 2010-03-20
Hello,

i am trying to write a quiry that has 2 tables.

Table1:
Companies:
Company Name, Manager Name 1, Manager Name 2.
Exampe Data.
Company ABC, 1, 2
Company DEB, 2, 1

Table 2:
Managers:
Manger_ID, Manager_name
Example Data.
1, Alex
2, Peter
3, Stan

Need to create a quiry to Display manager names.

Thanks in advance,

Roman
0
Comment
Question by:dizel247
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 20

Expert Comment

by:informaniac
ID: 20341860
Is Manager 1 the same as Manager_Id
0
 

Author Comment

by:dizel247
ID: 20341873
Manager1 and Manger2 - to make it clear Person To manage Sales, Person to manage Purchasing

1 is Manager ID
2 is manager ID

So to answer your question YES Manger 1 is related to managers.manager_id

Thanks in advance,

Roman
0
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 20342357
Based on your input the query will be like as given below.

select * from Companies a, Managers b where a.[Manager Name 1]=b.Manger_ID
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 20342360
or as this.
select * from Companies , Managers  where companies.[Manager Name 1]=managers.Manger_ID
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 252 total points
ID: 20342451
dizel247 said:
>>Manager1 and Manger2 - to make it clear Person To manage Sales, Person to manage Purchasing

I would suggest altering your design to something more like:

tblCompanies
----------------------------------------------------------
CompanyID (PK)
CompanyName

tblRoles
----------------------------------------------------------
RoleID (PK)
RoleDescr

tblManagers
----------------------------------------------------------
ManagerID (PK)
LName
FName

tblManagerCompany
----------------------------------------------------------
CompanyID (PK, FK)
ManagerID (PK, FK)
RoleID (FK)

With this structure, you can track additional managers if you need to, without having to alter your
basic design.
0
 

Author Comment

by:dizel247
ID: 20344882
shasund your solution didnt work for some reason.

matthewspatrick, I have though of that the problem is that I have this information updated on the Companies section. If I create the way you mention I will have to update different tables @ the same time. I belive its will be difficult.

Roman
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20345538
dizel247 said:
>>matthewspatrick, I have though of that the problem is that I have this information updated on the
>>Companies section. If I create the way you mention I will have to update different tables @ the
>>same time. I belive its will be difficult.

Please explain what you mean.  The design I suggested should *eliminate* the need to update more
than one table at a time, because it removes redundant data.
0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 248 total points
ID: 20346518

Select A.CompanyName, B.Manager_Name, C.Manager_Name
From Company A
LEFT JOIN Managers B ON B.Manager_ID = A.Manager1_ID
LEFT JOIN Managers C ON C.Manager_ID = A.Manager2_ID

Open in new window

0
 
LVL 1

Expert Comment

by:Computer101
ID: 20962587
Forced accept.

Computer101
Community Support Moderator
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question