Address

Author Topic: MYSql query help  (Read 1813 times)

aspazatak

  • FamiLAB Member
  • Jr. Member
  • *
  • Posts: 50
    • View Profile
MYSql query help
« on: October 11, 2013, 10:37:55 AM »

I have a question I need some assistance with. I'm trying to perform a query and match data from multiple databases (see below for MySql output).
 
 
The databases I need to use are gateway and wsnoweb, the tables are gateway.id, gateway.value, gateway.value_type, wsnoweb.id, wsnoweb.is_provisioned 
 
What I want to do is find all radio id's where:
wsnoweb.is_provisioned = "False" 
gateway.value_type = "0" 
gateway.value > "260" OR < "220"
 
 
In plain English I am looking for radios that are not provisioned on a customer tank and are reporting something other than a 50% reading (245 or so). I've had a rash of bad radios recently and I want to see how many potential others I may have out there.
 
 
The issue I am having with the query is how to span the 2 databases. Do I use a Union, Join, or something else?

Any  help would be appreciated.
 
 
mysql> show databases; 
+--------------------+ 
| Database | 
+--------------------+ 
| information_schema | 
| gateway | 
| mysql | 
| performance_schema | 
| test | 
| wsnoweb | 
+--------------------+ 
6 rows in set (0.00 sec) 
 
mysql> use gateway 
Database changed 
mysql> show tables; 
+-------------------+ 
| Tables_in_gateway | 
+-------------------+ 
| gateway_data | 
+-------------------+ 
1 row in set (0.00 sec) 
 
mysql> describe gateway_data; 
+--------------------+--------------+------+-----+---------+----------------+ 
| Field | Type | Null | Key | Default | Extra | 
+--------------------+--------------+------+-----+---------+----------------+ 
| id | bigint(11) | NO | PRI | NULL | auto_increment | 
| mtu_id | int(11) | NO | MUL | 0 | | 
| kp_receiver | int(11) | NO | | 0 | | 
| kp_system | int(11) | NO | | 0 | | 
| value_type | int(11) | NO | MUL | 0 | | 
| value | int(11) | NO | | 0 | | 
| kp_signal_strength | int(11) | NO | | 0 | | 
| kp_signal_level_id | int(11) | NO | | 0 | | 
| network_id | varchar(255) | NO | MUL | | | 
| receiver_time | datetime | YES | MUL | NULL | | 
| gateway_time | datetime | YES | MUL | NULL | | 
| receiver_id | varchar(255) | NO | MUL | | | 
| receiver_ip | int(11) | YES | MUL | NULL | | 
+--------------------+--------------+------+-----+---------+----------------+ 
13 rows in set (0.00 sec) 
 
mysql> use wsnoweb 
Reading table information for completion of table and column names 
You can turn off this feature to get a quicker startup with -A 
 
Database changed 
mysql> show tables; 
+--------------------------------------+ 
| Tables_in_wsnoweb | 
+--------------------------------------+ 
| auth_group | 
| auth_group_permissions | 
| auth_permission | 
| auth_user | 
| auth_user_groups | 
| auth_user_user_permissions | 
| django_admin_log | 
| django_content_type | 
| django_session | 
| django_site | 
| fillups_uploadedfilldata | 
| frontend_customer | 
| frontend_dealer | 
| frontend_dealer_account | 
| frontend_radio | 
| frontend_radiochangeevent | 
| frontend_radiochangeevententry | 
| frontend_serviceaddress | 
| frontend_supplier | 
| frontend_tank | 
| frontend_technician | 
| invoicing_adjustment | 
| invoicing_fillup | 
| invoicing_invoice | 
| invoicing_invoicecounter | 
| invoicing_invoicelineitem | 
| reporting_report | 
| sitetree_tree | 
| sitetree_treeitem | 
| sitetree_treeitem_access_permissions | 
+--------------------------------------+ 
30 rows in set (0.00 sec) 
 
[size=78%]mysql[/size][/size][size=78%]> [/size][/size][size=78%]describe[/size][/size][size=78%] frontend_radio;[/size]
+--------------------+--------------+------+-----+---------+----------------+ 
| Field | Type | Null | Key | Default | Extra | 
+--------------------+--------------+------+-----+---------+----------------+ 
| id | int(11) | NO | PRI | NULL | auto_increment | 
| network_id | varchar(255) | NO | MUL | NULL | | 
| dealer_id | int(11) | YES | MUL | NULL | | 
| dealer_radio_id | varchar(255) | YES | MUL | NULL | | 
| tank_id | int(11) | YES | UNI | NULL | | 
| is_provisioned | tinyint(1) | NO | MUL | NULL | | 
| provisioned_date | datetime | YES | MUL | NULL | | 
| is_billing_enabled | tinyint(1) | NO | MUL | NULL | | 
| billing_date | datetime | YES | MUL | NULL | | 
| receiver_url | varchar(255) | YES | | NULL | | 
+--------------------+--------------+------+-----+---------+----------------+ 
10 rows in set (0.00 sec) 

ki4swy

  • FamiLAB Member
  • Full Member
  • *
  • Posts: 213
  • skill mad scientist
    • View Profile
Re: MYSql query help
« Reply #1 on: October 11, 2013, 11:16:25 AM »
what are the common fields between the tables..


what field in wsnoweb.frontend_radio links to gateway.gateway_data

also, this statement is not valid:

The databases I need to use are gateway and wsnoweb, the tables are gateway.id, gateway.value, gateway.value_type, wsnoweb.id, wsnoweb.is_provisioned 

"gateway" and "wsnoweb" are database.
id/value/value_type are part of tables gateway_data


"id", "is_provisioned" are fields of table frontend_radio.


you will need to do a join .


so,
select gateway_data.* , frontend_radio.*
from gateway.gateway_data inner join wsnoweb.frontend_radio on gateway_data.id = frontend_radio.id
where gateway_data.value_type = 0 and (gateway_data.value > 260 or gateway_data.value < 260) and frontend_radio.is_provisioned = "false"

now,
a) the syntax maybe a little off, i don't have the database handy, so can't fix it ( i am a Micrsoft SQL Server Syntax guy, mysql maybe a little diff )
b) i am assuming the tables related via "id" values ( probably not )
c) i assume there is a 1:1 relation, so everything is in both tables. ( if not, look @ a left or outter join )
d) you can also do a where X is in ( select id from X where xxxxxx ) sub query and not do a join.



"Good engineers keep thick authoritative books on their shelf. Not for their own reference, but to throw at people who ask stupid questions. Hoping a small fragment of knowledge will osmotically transfer with each cranial impact." - eeguru

If it runs on electricity, i have it :)

aspazatak

  • FamiLAB Member
  • Jr. Member
  • *
  • Posts: 50
    • View Profile
Re: MYSql query help
« Reply #2 on: October 11, 2013, 11:20:08 AM »
You are correct that id is the field that ties them together.


I don't have any db experience (networking guy), so I'm learning as I go. Thanks for the help. I'll try it out and see what happens.

aspazatak

  • FamiLAB Member
  • Jr. Member
  • *
  • Posts: 50
    • View Profile
Re: MYSql query help
« Reply #3 on: October 11, 2013, 11:26:01 AM »
Had to make one change because of the data I provided (false = 0), but it worked great.  Thanks for the help

ki4swy

  • FamiLAB Member
  • Full Member
  • *
  • Posts: 213
  • skill mad scientist
    • View Profile
Re: MYSql query help
« Reply #4 on: October 11, 2013, 11:28:14 AM »
yeah, normally false/true are small ints :)
"Good engineers keep thick authoritative books on their shelf. Not for their own reference, but to throw at people who ask stupid questions. Hoping a small fragment of knowledge will osmotically transfer with each cranial impact." - eeguru

If it runs on electricity, i have it :)

ki4swy

  • FamiLAB Member
  • Full Member
  • *
  • Posts: 213
  • skill mad scientist
    • View Profile
Re: MYSql query help
« Reply #5 on: October 11, 2013, 11:29:05 AM »
no problem :)


pretty good for just typing it off the cuff i guess



"Good engineers keep thick authoritative books on their shelf. Not for their own reference, but to throw at people who ask stupid questions. Hoping a small fragment of knowledge will osmotically transfer with each cranial impact." - eeguru

If it runs on electricity, i have it :)

aspazatak

  • FamiLAB Member
  • Jr. Member
  • *
  • Posts: 50
    • View Profile
Re: MYSql query help
« Reply #6 on: October 11, 2013, 12:19:51 PM »
On other typo was in the data values should be <240 or >260 where you have 260 in both fields, but I agree pretty good for off the cuff typing

ki4swy

  • FamiLAB Member
  • Full Member
  • *
  • Posts: 213
  • skill mad scientist
    • View Profile
Re: MYSql query help
« Reply #7 on: October 11, 2013, 12:31:02 PM »
doh!
"Good engineers keep thick authoritative books on their shelf. Not for their own reference, but to throw at people who ask stupid questions. Hoping a small fragment of knowledge will osmotically transfer with each cranial impact." - eeguru

If it runs on electricity, i have it :)