Solved

MySql geting values from muliple colums and dispalying it in to one. Showing upcoming expirations

Posted on 2014-12-15
16
166 Views
Last Modified: 2014-12-18
So i have an table that looks like this

+------------------+----------------+-----------------+---------------+
| licxp1            | licxp2          | licxp3         | licxp4        |
+------------------+----------------+-----------------+---------------+
| 2014-12-18   | 2014-10-29 | 2014-12-11 |2014-12-01|
| 2014-12-29   | 2014-10-22 | 2014-12-18 |2014-12-15|
| 2014-12-18   | 2014-11-18 | 2014-12-17 |2014-12-12|
| 2014-12-27   | 2014-12-19 | 2014-12-18 |2014-12-07|
| 2014-12-18   | 2014-12-18 | 2014-12-19 |2014-12-05|
| 2014-12-23   | 2014-12-18 | 2014-12-18 |2014-12-16|
| 2014-12-18   | 2014-12-18 | 2014-12-18 |2014-12-18|
+------------------+----------------+-----------------+---------------+

And i need it to look like this while getting the First name from another table.

+------------------+----------------+
| Name          |Expring lics |
+------------------+----------------+
| John Doe      | 2014-10-29 |
| Johnny Cash | 2014-10-22 |
|Bruice Willis  | 2014-11-18 |
| Led Zeplin    | 2014-12-19 |
| Def leapord  | 2014-12-18 |
| Iron Madden| 2014-12-18 |
| etc.                | 2014-12-18 |
+------------------+----------------+

                                      ^
                                      |
                                      |
                                      |
                                      |
              This field gets all the up coming expiration that are 15 days from today from the license table

Also i need it to repeat for example if there are 2 licenses for John Doe i need it to display like this :


+------------------+----------------+
| John Doe      | 2014-10-10 |
| John Doe      | 2014-10-21 |
| John Doe      | 2014-10-27 |
| John Doe      | 2014-10-20 |
| John Doe      | 2014-10-22 |
| Johnny Cash | 2014-10-21 |
|Bruice Willis  | 2014-11-18 |
| Led Zeplin    | 2014-12-19 |
| Def leapord  | 2014-12-18 |
| Iron Madden| 2014-12-18 |
| etc.                | 2014-12-18 |
+------------------+----------------+

Thanks in advanced ! Ask me anything im very new to mysql so i will try my hardest ! Thanks again!
0
Comment
Question by:artnetworks
  • 7
  • 6
  • 3
16 Comments
 
LVL 50

Expert Comment

by:Steve Bink
ID: 40502785
You probably want the UNION keyword.  From your example, if I want to get all license dates in a single column:
SELECT licxp1 FROM LicenseTable
UNION
SELECT licxp2 FROM LicenseTable
UNION
SELECT licxp3 FROM LicenseTable
UNION
SELECT licxp4 FROM LicenseTable

Open in new window


Be aware the UNION will remove any duplicate rows it finds.  If you want to see that duplicate information, try UNION ALL instead.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40502892
The issue is if John Doe has two expiration dates, it is useless as you would still need to know which are expiring. one way is to concatenate the license:expiration date in one column but that will involve many more queries than necessary as the above example covers, the license:date will need to alter the example and replace the licxpx with ('licxpx:'+licxpx) or if the data type use a convert of the datetime expiry column prior to the sting combination.

The table structure is incomplete but here is a guess
select a.name, (if ( datediff(d,b.licxp1,gatedate()) >15 then 'Valid' else b.licxp1) as licexp1,(if ( datediff(d,b.licxp2,gatedate()) >15 then 'Valid' else b.licxp1) as licxp2,(if ( datediff(d,b.licxp1,gatedate()) >15then 'Valid' else b.licxp3) as licxp3,(if ( datediff(d,b.licxp4,gatedate()) >15 then 'Valid' else b.licxp1) as licxp4 from <individual name table> a inner join <license expiration table> b  on a.id=b.customerid

The table will look as follows:
name     |licxp1      |licxp2       |licxp3      |licxp4   |
john doe|valid         |2014-10-29|2014-10-22|valid       |
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 40502972
My example was merely to demonstrate the utility of UNION, not to give an explicit query that would work.  To simplify arnold's example, your actual query to pull all the dates of interest might look something like this:
SELECT licxp1 FROM LicenseTable WHERE DATEDIFF(d,licxp1,NOW()) =<15
UNION
SELECT licxp2 FROM LicenseTable WHERE DATEDIFF(d,licxp2,NOW()) =<15
UNION
SELECT licxp3 FROM LicenseTable WHERE DATEDIFF(d,licxp3,NOW()) =<15
UNION
SELECT licxp4 FROM LicenseTable WHERE DATEDIFF(d,licxp4,NOW()) =<15

Open in new window

Of course, that would still be relatively useless without the user info, so you'll need to take those results and join them appropriately.  Assuming you have a user_id field in the license table, which relates to the id field of your user table:
SELECT b.id, b.username, a.expiredate, a.fieldnum FROM
    (SELECT user_id, licxp1 as expiredate, 1 as fieldnum FROM LicenseTable WHERE DATEDIFF(d,licxp1,NOW()) =<15
     UNION
     SELECT user_id, licxp2 as expiredate, 2 as fieldnum FROM LicenseTable WHERE DATEDIFF(d,licxp2,NOW()) =<15
     UNION
     SELECT user_id, licxp3 as expiredate, 3 as fieldnum FROM LicenseTable WHERE DATEDIFF(d,licxp3,NOW()) =<15
     UNION
     SELECT user_id, licxp4 as expiredate, 4 as fieldnum FROM LicenseTable WHERE DATEDIFF(d,licxp4,NOW()) =<15
    ) a
    INNER JOIN users b
      ON a.user_id=b.id

Open in new window

That would give you each expiring date, as well as the date field from which it came (fieldnum), and the associated username and id.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40503007
Steve, my comment was in no way meant to suggest anything related to your example.  The asker's request in addition to complicating the query unnecessarily provides no context to the results received which will require a subsequent query.
A response such as
name     |Expiring Licenses
john doe|3
Johnny cash|2
Led Zeplin|1

User and how many licenses are expiring
will be just as useful but could be combined in a single query with several if datediff to ........
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 40503325
@arnold, no offense taken - your comment helped me realize that I had described a possible solution, but not how it would actually be implemented.  I posted the subsequent examples to be more thorough, and more closely match the OPs original desired result.  I even incorporated your idea of knowing which field was the data source (your if() expression, vs my fieldnum).  

So, really, half of my last post is yours.  :)
0
 
LVL 76

Expert Comment

by:arnold
ID: 40503869
My if test, incorrectly responds with valid, versus the upcoming expiration date.

Edited the response to correct. To minimize changes switch to larger than 15 days.
Also, the datediff used is for MSSQL. Mysql, subtracts dates and defaults to days the first indicator in the example is not needed. As well as now() needs to be used instead of getdate().
0
 

Author Comment

by:artnetworks
ID: 40504041
Hey guys sorry im a little confused let me attach the database structure maybe you guys can tell me where i built the table structure wrong. Sorry im a noob lol.schema
0
 

Author Comment

by:artnetworks
ID: 40504042
all the columns under the "licenses" are expiration dates besides  the id column.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:artnetworks
ID: 40504046
also can you please write me an example with tables that i attached with so i can better understand how to execute the query. sorry.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40504154
Your design is missing any relationship information between, among tables.
I.e. Presumably you have multiple companies, several nurses, chha's,  lvn's.
Are you managing the individuals that were sent from different companies, or are being sent as your firms reps to different companies?
Presumably each individual will have some type of licensing.
One option is to create a single table for an individual/license table for each grouping I.e. Nurse/license nurse_id/license_id index each non unique with a combined unique index.
Chha's/license etc. chha_id/license_id
NOTE that a per employee type/license table it makes it your coding responsibility to manage that data integrity. I.e. If on skill/educational level there are steps I.e. Chha is one, LVn is next and Nurse is third such that a person is hired as a chha, after a certain time, promoted/advanced to LVn or straight to a nurse. The handling of transitioning the record from chha to the respective table as well as updating the licenses table to make sure you do not end up with three license rows for an individual as a Chha, LVn and as a nurse.

A more complex structure of relationship tables to deal with a single license table to different tables would have a Tablename/type, employees IDS, license_ID
I.e. Nurse_table, nurse_id (as index non unique) , license_id and all three as unique.
Chha_table,chha_id,license_id
In this case you still have to manage the transitions I.e, when a person is moved from chha, to nurse, you might/would want to maintain the existing license record, by updating the relationship after the adding the employee to nursing,

............ Trying to assess all the possible values seems to drag this process out tooooooooooo long.

Similarly for nurse/company depending on whether a nurse can be sent to multiple companies, or a company sends nurses to you. I.e. Nurse A on MTuFR goes to company1, the other workdays, company2 is where the services are needed.


You could consolidate your setup to have one first name, lastname, hire_date record, by adding a type  chha, LVn, nurse consolidating the three tables into one table with one additional column.
Since an individual can only have one license record,
You could add a column to the license table of type int that could be a foreign key to the individual or the reverse, add a reference in the individual to their license row, adding both could be as well depending on the queries you run, I.e. Search licenses that are expiring and then tie back to the user.

Is restructuring the licenses table an option?
Licensed, license type, expiration,employee_id,company_id

Presumably, you need to keep track of information, you would need a record keeping/transactional table that will include information on changes.

Are you using mysql's workbench to design your data/table structure?
0
 

Author Comment

by:artnetworks
ID: 40504223
Oh ok i see. Yes im using mysql's workbench. Let me attach a better picture to describe what im trying to accomplish. Yes i can move the tables in any way. I have not populated the database yet. project2.jpg
0
 

Author Comment

by:artnetworks
ID: 40504225
The Nurses |LVN |CHHA and companies are just other sub categories. they will never be moved around.
0
 

Author Comment

by:artnetworks
ID: 40504226
Thanks for your patients guys :) Im too new and  this looks way more complicated than i taught.
0
 

Author Comment

by:artnetworks
ID: 40504229
The other problem is the nurses have 6-15 licenses. And we send them out. so I need to fetch all the expiring dates for all their licenses not just one.
0
 
LVL 76

Expert Comment

by:arnold
ID: 40504237
The issue is aligning the data.
One table would be companies. A company has multiple employees. Question can the same employee work for multiple companies?
One table would be employees
Each employee can have multiple licenses.
A license table could be the way you have it such that all license types are included within, what this does is complicates /makes less modular should new licensing is required as that would require a modification of the licensing table to add the requisite additional column. An alternative is to use a licensing_type and the licensing table where each row will include the reference to the employee, the reference to the type, and the expiry date
Because each license will have its own row, the results will be as you asked,
Name, type, expiry
Using a join query between the employee, license, license_type

To simplify the design of your database, you need to define the business process which in turn guide you on the design of the database which will then guide on the query.
0
 
LVL 76

Accepted Solution

by:
arnold earned 500 total points
ID: 40505214
company: companyid, company name, address, phone, fax, active,
Employee: employeeid, employee name, address, phone, active, date of hire, date of termination..
EmployeeType: employeetypeid, description, requirements,date started in this position, date ended (deals with if one can start and progress)
licensetype: licensetypeid,name,description, requirements
license: licenseid,fk employeeid,fk licensetypeid,fk employeetype,expiry_date (the employeetypeid reference deals with whether licenses that are no longer suitable for the position they are in are still required......), active (active status of license to deal with individuals transitioning to other jobs where this license is no longer needed)

To keep track of all changes, you would need additional table to record when a company information was updates, similarly for an employee,license, licensetype, employeetype, etc. i.e. a new license is required for certain position, you simply add a new licensetype and then each employee can have another license record.

in the basic form, your query, updates , logic will enforce data integrity.
As you advance, you could use triggers/constraints to enforce business model and data integrity deals with mistakes i.e. deleting entries..... on which other other records rely i.e. deleting an employee type, licensetype, etc.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

705 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now