Solved

My SQL View

Posted on 2015-02-13
3
85 Views
Last Modified: 2015-02-18
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
0
Comment
Question by:desiredforsome
  • 2
3 Comments
 
LVL 18

Assisted Solution

by:Simon
Simon earned 500 total points
ID: 40608725
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
 

Accepted Solution

by:
desiredforsome earned 0 total points
ID: 40608768
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
 

Author Closing Comment

by:desiredforsome
ID: 40616148
Great work!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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