Solved

Second table rows as columns in query

Posted on 2013-06-28
16
383 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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
 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 57
VB.net and sql server 4 45
error in my cursor 5 41
Select values in a row based on values in another row in sql 4 26
Creating and Managing Databases with phpMyAdmin in cPanel.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 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