Second table rows as columns in query

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

tonelm54Asked:
Who is Participating?
 
Louis01Connect With a Mentor Commented:
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
 
Louis01Commented:
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
 
edtechdbaCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
PortletPaulfreelancerCommented:
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
 
edtechdbaCommented:
It looks like the tables should be joined on the userID (services.priID = user.userID), not on the phone number (PhoneNo).
0
 
PortletPaulfreelancerCommented:
I doubt it, here's a sample:

userid      15684311sdess
userid      65146512
priID      a1
priID      a2
priID      a3
0
 
edtechdbaCommented:
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
 
PortletPaulfreelancerCommented:
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
 
edtechdbaCommented:
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
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
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
 
edtechdbaCommented:
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
 
PortletPaulfreelancerCommented:
>>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
 
PortletPaulfreelancerCommented:
>>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
 
PortletPaulfreelancerCommented:
@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
 
PortletPaulfreelancerCommented:
@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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.