Solved

My SQL View

Posted on 2015-02-13
3
88 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

724 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