I don't think I know enough about the tables (is there a
primary key) or what the end result should look like (one
row per IP address, do you need to know what table matched)
to give a very good answer, but a simple UNION will probably
get you part of the way there:
SELECT DISTINCT 'DIALUP', Full_Name, Framed_IP_Address
FROM DIALUP WHERE Framed_IP_Address = "127.0.0.1"
UNION
SELECT DISTINCT 'DSL', Full_Name, Framed_IP_Address
FROM DSL WHERE Framed_IP_Address = "127.0.0.1"
mysql> select * FROM DIALUP;
+-----------+-------------------+
| Full_Name | Framed_IP_Address |
+-----------+-------------------+
| Tom | 127.0.0.1 |
| Dick | 127.0.0.2 |
| Harry | 127.0.0.4 |
+-----------+-------------------+
3 rows in set (0.00 sec)
mysql> select * FROM DSL;
+-----------+-------------------+
| Full_Name | Framed_IP_Address |
+-----------+-------------------+
| Jane | 127.0.0.1 |
| Jill | 127.0.0.2 |
| Janet | 127.0.0.3 |
+-----------+-------------------+
3 rows in set (0.00 sec)
mysql> SELECT DISTINCT 'DIALUP', Full_Name, Framed_IP_Address
-> FROM DIALUP WHERE Framed_IP_Address = "127.0.0.1"
-> UNION
-> SELECT DISTINCT 'DSL', Full_Name, Framed_IP_Address
-> FROM DSL WHERE Framed_IP_Address = "127.0.0.1";
+--------+-----------+-------------------+
| DIALUP | Full_Name | Framed_IP_Address |
+--------+-----------+-------------------+
| DIALUP | Tom | 127.0.0.1 |
| DSL | Jane | 127.0.0.1 |
+--------+-----------+-------------------+
2 rows in set (0.00 sec)
____________________________________________________________
Eamon Daly
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, July 06, 2004 7:39 AM
Subject: Query from mulitple tables where data will only be in one table but
that table unkown
Ok,
So here is what I am trying to do, I have 2 tables one with DSL IP
addresses and one with Dialup addresses. I need to be able to query both
tables and extract the information by IP address but I will not know
which table the data is in before I do the search.
Basically I want to have a query that states "look in both tables and if
the ip is found in either then give me some fields from that table"
What I have tried already is things like this:
Select DISTINCT DIALUP.Full_Name, DIALUP.Framed_IP_Address,
DSL.Full_Name, DSL.Framed_IP_Address
>From DIALUP, DSL
Where DSL.Framed_IP_Address = "65.xxx.196.175" or
DIALUP.Framed_IP_Address = "65.xxx.196.175"
LIMIT 500
However that doesn't work because it returns rows from the table that
the IP address is NOT in as well, because of the self join that is being
done.
All help will be GREATLY appreciated.
Chris Hood
Investigator Verizon Global Security Operations Center
Email: <mailto:[EMAIL PROTECTED]>
[EMAIL PROTECTED]
Desk: 972.399.5900
Verizon Proprietary
NOTICE - This message and any attached files may contain information
that is confidential and/or subject of legal privilege intended only for
the use by the intended recipient. If you are not the intended
recipient or the person responsible for delivering the message to the
intended recipient, be advised that you have received this message in
error and that any dissemination, copying or use of this message or
attachment is strictly forbidden, as is the disclosure of the
information therein. If you have received this message in error please
notify the sender immediately and delete the message.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
