Solved

Second table rows as columns in query

Posted on 2013-06-28
16
371 Views
Last Modified: 2013-07-17
I'm trying to create a table with multiple columns pulling from another table of rows, however the rows in the second table I dont want to disaply if there is a 'removedJob'.

So my question is, is how can I do a kinda subquery to display my second table as coloms in my query, eg something like:-
Select `user`.`userID`, `user`.`Username`, `user`.`phoneNo`,
     (Select `services`.`jobAdded` FROM `services` where ((`services`.`priID` = `user`.`userID`) and (`jobRemoved` = null) and (`soc` = 'SOC1' ))) as `SOC1`,
     (Select `services`.`jobAdded` FROM `services` where ((`services`.`priID` = `user`.`userID`) and (`jobRemoved` = null) and (`soc` = 'SOC2' ))) as `SOC2`,
     (Select `services`.`jobAdded` FROM `services` where ((`services`.`priID` = `user`.`userID`) and (`jobRemoved` = null) and (`soc` = 'SOC3' ))) as `SOC3`
FROM `user`

So it should display a table such as:-
Table

My tables are setup like:-
CREATE TABLE `user` (
  `userID` varchar(36) NOT NULL,
  `Username` varchar(45) DEFAULT NULL,
  `PhoneNo` varchar(12) DEFAULT NULL,
  PRIMARY KEY (`userID`)
);
CREATE TABLE `services` (
  `priID` varchar(36) NOT NULL,
  `phoneNo` varchar(12) DEFAULT NULL,
  `ServiceCode` varchar(15) DEFAULT NULL,
  `jobAdded` int(11) DEFAULT NULL,
  `jobRemoved` int(11) DEFAULT NULL,
  PRIMARY KEY (`priID`)
);

Open in new window



And some test data, as the real data is a few hundred Mbs:-
INSERT INTO `services` VALUES ('a1','0123456789','SOC1',1,3),('a2','0123456789','SOC2',3,2),('a3','0123456789','SOC3',2,2),('a4','0123456789','SOC4',2,2),('a5','0123456789','SOC5',3,NULL),('a6','0123456789','SOC1',3,3),('a7','0123456789','SOC3',3,NULL),('a8','0123456789','SOC4',3,NULL),('a9','0123456789','SOC1',3,6),('aa','0123456789','SOC4',3,2),('ab','0123456789','SOC1',3,NULL),('ac','987654321','SOC3',3,NULL),('ad','987654321','SOC6',3,2),('ae','987654321','SOC9',3,NULL),('af','987654321','SOC10',3,2),('b1','987654321','SOC2',4,2),('b2','521452166','SOC1',3,NULL),('b3','521452166','SOC2',3,NULL);

INSERT INTO `user` VALUES ('15684311sdess','TCross','0123456789'),('65146512','BRama','987654321');

Open in new window

0
Comment
Question by:tonelm54
  • 9
  • 5
  • 2
16 Comments
 
LVL 11

Expert Comment

by:Louis01
ID: 39284358
Here you go:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'count(case when ServiceCode = ''',
      ServiceCode,
      ''' then 1 end) AS ',
      replace(ServiceCode, ' ', '')
    )
  ) INTO @sql
from `services`;

SET @sql = CONCAT('select u.userID, u.Username, u.PhoneNo, ', @sql, ' from `user` u
        left outer join `services` s
    on  u.phoneNo = s.phoneNo
 group by u.userID, u.Username, u.PhoneNo');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Open in new window


Note that I joined on PhoneNo - not sure if that is correct, if not, just change the join.
0
 
LVL 9

Expert Comment

by:edtechdba
ID: 39284400
This should work, straight forward.

SELECT u.userID, u.Username, u.PhoneNo,
      (CASE WHEN s.ServiceCode = 'SOC1'
                        AND s.jobRemoved IS NOT NULL THEN s.jobAdded
            ELSE NULL
      END) AS SOC1,
      (CASE WHEN s.servicecode = 'SOC2'
                  AND s.jobRemoved IS NOT NULL then s.jobAdded
            ELSE NULL
      END) AS SOC2,
      (CASE WHEN s.servicecode = 'SOC3'
                  AND s.jobRemoved IS NOT NULL then s.jobAdded
            ELSE NULL
      END) AS SOC3
FROM [user] u
INNER JOIN services s
      ON u.userID = s.priID
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39284484
This result:

USERID		USERNAME	PHONENO		SOC1	SOC2	SOC3	SOC4	SOC5	SOC6	SOC7	SOC8	SOC9	SOC10
15684311sdess	TCross		0123456789	3	(null)	3	3	3	(null)	(null)	(null)	(null)	(null)
65146512	BRama		987654321	(null)	(null)	3	(null)	(null)	(null)	(null)	(null)	3	(null)

Open in new window

from this code:
SELECT
      u.`userID`
    , u.`Username`
    , u.`phoneNo`
    , max(CASE WHEN s.`ServiceCode` = 'SOC1'  AND `jobRemoved` IS NULL THEN `jobAdded` END) AS SOC1
    , max(CASE WHEN s.`ServiceCode` = 'SOC2'  AND `jobRemoved` IS NULL THEN `jobAdded` END) AS SOC2
    , max(CASE WHEN s.`ServiceCode` = 'SOC3'  AND `jobRemoved` IS NULL THEN `jobAdded` END) AS SOC3
    , max(CASE WHEN s.`ServiceCode` = 'SOC4'  AND `jobRemoved` IS NULL THEN `jobAdded` END) AS SOC4
    , max(CASE WHEN s.`ServiceCode` = 'SOC5'  AND `jobRemoved` IS NULL THEN `jobAdded` END) AS SOC5
    , max(CASE WHEN s.`ServiceCode` = 'SOC6'  AND `jobRemoved` IS NULL THEN `jobAdded` END) AS SOC6
    , max(CASE WHEN s.`ServiceCode` = 'SOC7'  AND `jobRemoved` IS NULL THEN `jobAdded` END) AS SOC7
    , max(CASE WHEN s.`ServiceCode` = 'SOC8'  AND `jobRemoved` IS NULL THEN `jobAdded` END) AS SOC8
    , max(CASE WHEN s.`ServiceCode` = 'SOC9'  AND `jobRemoved` IS NULL THEN `jobAdded` END) AS SOC9
    , max(CASE WHEN s.`ServiceCode` = 'SOC10' AND `jobRemoved` IS NULL THEN `jobAdded` END) AS SOC10
FROM USER AS u
LEFT OUTER JOIN `services` AS s ON u.phoneNo = s.phoneNo
GROUP BY u.userID, u.Username, u.PhoneNo

Open in new window

see this operating at: http://sqlfiddle.com/#!2/0081dc/1
0
 
LVL 9

Expert Comment

by:edtechdba
ID: 39284575
It looks like the tables should be joined on the userID (services.priID = user.userID), not on the phone number (PhoneNo).
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39284620
I doubt it, here's a sample:

userid      15684311sdess
userid      65146512
priID      a1
priID      a2
priID      a3
0
 
LVL 9

Expert Comment

by:edtechdba
ID: 39284649
I agree, the sample data doesn't match up in that example. However,  (tonelm54) shows in the first post that the ID's are joined. That can be easily changed too.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39284667
Query Result:

USERID		USERNAME	PHONENO		SOC1	SOC2	SOC3
15684311sdess	TCross		0123456789	3	(null)	3
65146512	BRama		987654321	(null)	(null)	3*

Open in new window

* this entry disagrees with the provided image, but the data is there:
PRIID      PHONENO      SERVICECODE      JOBADDED      JOBREMOVED
ac            987654321      SOC3            3            (null)

I have assumed you would want all 10 SOC codes (not just 3), but if 3 is all you want then this will suffice:
SELECT
      u.`userID`
    , u.`Username`
    , u.`phoneNo`
    , max(CASE WHEN s.`ServiceCode` = 'SOC1'  AND `jobRemoved` IS NULL THEN `jobAdded` END) AS SOC1
    , max(CASE WHEN s.`ServiceCode` = 'SOC2'  AND `jobRemoved` IS NULL THEN `jobAdded` END) AS SOC2
    , max(CASE WHEN s.`ServiceCode` = 'SOC3'  AND `jobRemoved` IS NULL THEN `jobAdded` END) AS SOC3
FROM USER AS u
LEFT OUTER JOIN `services` AS s ON u.phoneNo = s.phoneNo
GROUP BY u.userID, u.Username, u.PhoneNo

Open in new window

if you do want all 10 use the code provided at ID: 39284484
0
 
LVL 9

Expert Comment

by:edtechdba
ID: 39284695
The join is a small piece of the code and can easily be modified, the structure of the query is much more important. If this were my question, I'd go with Louis01's response (ID: 39284358) because that code is dynamic and will auto-generate new columns when new service code's are added. Your code is a tweak of mine with a different join and adding max functionality (not sure if this is even relevant to the data being pulled).
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39284700
I have met the expected results.

Why any need to change the relationship? (which clearly would not work, the data indicates it won't)

the first post also includes these: and (`jobRemoved` = null)
so, with the utmost respect, I would much prefer to use the data that does work, and that meets the expected results, than follow partial sql that will not.

nb, the question states: "I dont want to display if there is a 'removedJob'."
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39284733
but again, while Louis01's dynamic code is cool, it doesn't do what has been asked.

that dynamic code is performing a count of records, that isn't what has been asked for.

please take a moment to re-look at the supplied image.

ps: dynamic has overheads that static does not, if the need is for a fixed number of columns - avoid dynamic, stick with static. If the number of columns does need to change then (and only then) would one go with dynamic. However that dynamic code needs to be based on case expressions, not a simple count() as it is now.
0
 
LVL 9

Expert Comment

by:edtechdba
ID: 39284753
Are you claiming that the code that you posted was created by you (I think not)? Or by me and then you made 1 small relevant tweak? It looks just like what I posted, same table aliases and capitalizations too. Done with this question .. I was just trying to help and have no interest in earning points.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39284756
>>Your code is a tweak of mine
regret to say that is untrue!!!!

I was working on the solution, and in fact didn't see yours at all :(
until I received a comment notice.

It is true, that with some minor changes to your code it would meet the requirements - right now it does not. (e.g. the NOT NULL is incorrect)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39284772
>>I think not

you are incorrect - as stated I had not seen your post at all
do you really believe I needed your post to arrive at this set of case expressions?

I have stated I did not use or leverage your code - that is factual. I did not.

can we stop with this now?
0
 
LVL 11

Accepted Solution

by:
Louis01 earned 500 total points
ID: 39285130
Thanks Paul for pointing out the JobRemoved bit - herewith an update on my initial post:
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'count(case when ServiceCode = ''',
      ServiceCode,
      ''' then 1 end) AS ',
      replace(ServiceCode, ' ', '')
    )
  ) INTO @sql
from `services`;

SET @sql = CONCAT('select u.userID, u.Username, u.PhoneNo, ', @sql, ' from `user` u
        left outer join `services` s
    on  u.phoneNo = s.phoneNo
  and `jobRemoved`is null
 group by u.userID, u.Username, u.PhoneNo');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Open in new window


Notes to Author:
-When you check for NULL, use "(`jobRemoved` is null)" not "="
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39285302
@Louis01 you still have the issue that you are doing a count(), while that's a pretty typical reason for these pivots, it isn't the case here. Perhaps try MAX() ?

feel free to use that sqlfiddle it has the data ready.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39286124
@Louis01, ID: 39285130 lines 5 & 7

      'MAX(case when ServiceCode = ''', /* change to MAX() */
      ServiceCode,
      ''' then jobAdded end) AS ',      /* change "1" to "jobAdded" */

@tonelm54 , please don't award me points

(I believe ID: 39285130 with changes above matches your expected results)


@edtechdba, whilst you may never fully believe me, I can understand your questioning.
My work habit is to open several questions, work on them, then post as I progress. The page I was working with for this question did not contain your post (i.e. it was not refreshed).
 If I were to tweak another's code I would always acknowledge the author.
Apologies if this has been an unpleasant experience particularly as I now see you are very new to E-E.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

757 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

19 Experts available now in Live!

Get 1:1 Help Now