Solved

My SQL View

Posted on 2015-02-13
3
86 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Creating and Managing Databases with phpMyAdmin in cPanel.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

730 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