My SQL View

I am trying to create a view in my asterisk server mysql db so that I can see IPs mapped to accounts. The issue is that the information is held on the same server in two seperate databases

database1 = asterisk
database2 = endpointconfig

database2 has two tables that I need and database1 has 1 table I need.

I tried the following code below and am definatley doing something wrong
CREATE VIEW SITEPHONE
AS
SELECT D.name,extension FROM asterisk.users
INNER JOIN endpointconfig.endpoint_account.account O ON O.account = D.extension

Open in new window



What I need is there is a vale in database 1. so total is "asterisk.users.name" is what I want to pull from that table by matching its value of 'asterisk.users.extension" to endpointconfig.endpoint_account.account" then I need to match that table up to another table by endpointconfig.endpoint.id = endpointconfig.endpoint.id

So

asterisk.users.extension = endpointconfig.endpoint_account.account
endpoint.endpoint.id = endpointconfig.endpoint.id

Results in view should be
asterisk.users.extension
asterisk.users.name
endpointconfig.endpoint.last_known_ipv4
endpointconfig.endpoint.mac_address
desiredforsomeAsked:
Who is Participating?
 
desiredforsomeAuthor Commented:
Ill give you the credit for it. I had just figured it out. This was what I went with

CREATE VIEW EMPLOYEE AS
 
SELECT
asterisk.users.name as "Employee Name", endpointconfig.endpoint_account.account as Extension, endpointconfig.endpoint.last_known_ipv4 as "IP ADDRESS" , endpointconfig.endpoint.mac_address as "MAC ADDRESS"
From asterisk.users 
inner join 
endpointconfig.endpoint_account
 on asterisk.users.extension = endpointconfig.endpoint_account.account
inner join 
endpointconfig.endpoint 
on endpointconfig.endpoint_account.id_endpoint = endpointconfig.endpoint.id

Open in new window

0
 
SimonCommented:
IfI've understood you corretly, you are not returning any columns from endpointconfig.endpoint_account, but just using it as a joining table between asterisk.users and endpointconfig.endpoint...

SELECT 
U.extension,
U.name,
EP.last_known_ipv4,
EP.mac_address

from asterisk.users U inner join endpointconfig.endpoint_account EPA
on U.extension=EPA.account
inner join endpointconfig.endpoint EP on EPA.id = EP.id

Open in new window

0
 
desiredforsomeAuthor Commented:
Great work!
0
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.

All Courses

From novice to tech pro — start learning today.