Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

My SQL View

Posted on 2015-02-13
3
Medium Priority
?
90 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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

604 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