Mysql – theSQL query with field replacemnt

MySQL

I'm need to run a query from mySQL 5.6 which involves looking up date from different tables. I'm new to mySQL and I'm not sure what this operation is called, or else I would have just googled the subject… I'm hoping to get someone to point me in the right direction… Please note, I can't change the structure of the tables, as this is pre-populated from the application I am using.

example:

3 tables in the same local database: network_segments, Buildings, Departments

network_segments description:

network_segment_ID, display_name, starting_ip, ending_ip, building, department
1, 10.0.0.1, 10.0.0.254, 1, 2
2, 10.1.0.1, 10.1.0.254, 2, 3
3, 10.4.0.1, 10.4.0.254, 3, 1

building table description:

building_ID, description
1, ACHQ
2, EHQ
3, AHQ

departments table description:

department_ID, description
1, marketing
2, sales
3, hr

I want to "select * from network_segments" but in stead of the building and department IDs coming back, I'd like the description field for those IDs:

1, 10.0.0.1, 10.0.0.254, ACHQ, sales
2, 10.1.0.1, 10.1.0.254, EHQ, hr
3, 10.4.0.1, 10.4.0.254, AHQ, marketing

any advise would be appreciated…

Thank you!

Best Answer

select network_segment_ID, display_name, starting_ip, ending_ip, bld.description, dept.description
from network_segments
join buildings bld on (building = building_ID)
join departments dept on (department = department_ID);

Related Solutions

MySQL Recursive Query – Using Stored Procedure and CURSOR

So the three questions I'm hoping someone can answer are:

1.Should I be using a CURSOR here or is there an alternative that will get the same recursive result?

No, you should not. The CURSOR keep being opened over and over. Thinking of the overhead make me cringe. Personally, I stay away from CURSORs.

2.How can I get the results back in a single result set so that it can be used in the same fashion as a subselect?

3.What is the proper way of using the results from the CALL because I can't at least as far as I've tried get the sample SELECT statement above to work? I believe this is because I can't use CALL inline but I'm not sure.

I would like to bail you out of this by suggesting something I learned from my college days when learning about data structures. You need to perform tree traversal using a queue. This allows you to start at a root and express all descendants of a tree.

The algorithm goes like this

STEP 01 : Start with an empty queue
STEP 02 : Dequeue Node From the Front of the Queue
STEP 03 : Enqueue All Children of the Latest Node
STEP 04 : Process Info From the Latest Node
STEP 05 : If the Queue is Not Empty, Go Back to STEP 02
STEP 06 : All Done

This allows you to traverse a recursive structure without using Programmatic Recursion. At this point, you are probably asking: How can I traverse a tree structure without recursion?

I wrote a post about how to script three Stored Procedures that can using a loop in a single CALL that will traverse a table with nodes and its parent in a table:

GetParentIDByID
GetAncestry
GetFamilyTree

The post is Find highest level of a hierarchical field: with vs without CTEs (Oct 24, 2011). It contains the Stored Procedures already written that will traverse the following table structure:

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment | 
| parent_id  | int(11)      | YES  |     | NULL    |                | 
| name       | varchar(255) | YES  |     | NULL    |                | 
| notes      | text         | YES  |     | NULL    |                | 
+------------+--------------+------+-----+---------+----------------+

Please read the code carefully and apply the principles.

Give it a Try !!!

Mysql – Peer review database design for various reports

I think you are doing well going for the 4th normal form and exploding your data. Now, as you found out, you need to de-normalise to support for historical data support: if the review will always account for a particular department's score whatever the current department of the reviewee_id is, then yes, you definitely need to add a department at the review table and make sure you know what/why it is there for.

Push the logic further: what if there is a 4th and 5th category added in a year? How do you calculate past and future averages then? On 3 or 5 categories? Then another year later, the first category gets removed? Will you delete all scores for that category, or change yet again the average's calculation based on the number of "category" at the time of the "date" of the "score"?

Sometime, too much normalisation is not the best course of action. You have a good core here. Time to custom-fit now. Take a step back, plan for the future, ask the end-users to make sure you support all their foreseeable needs, and look at the impacts of adding/removing/updating departments, employees and categories on historical reporting.

As for reporting, if you are using powerful data tools like IBM Cognos, it will be easy to generate data cubes, then let managers do their own reports. If you are using tools like Microsoft Excel with "external data", then the easiest course of action would be to pre-create Views or Stored Procedures that crunch up the data. The caveat here is that you will have to create a new View/SP for every manager that comes up with a new request/idea/variant.

Best Answer

Related Solutions

MySQL Recursive Query – Using Stored Procedure and CURSOR

Mysql – Peer review database design for various reports

Related Question