Home Populate values with subquery from same query
Reply: 0

Populate values with subquery from same query

user4322
1#
user4322 Published in June 19, 2018, 6:13 pm

Hopefully I'm explaining this correctly.

Have a query where I'm joining a few tables. The way the system is setup, when one column is populated, the other is blank, and vice versa. Here's kind of what my query results look like currently:

    CREATE TABLE mytable(
   mainID INTEGER  NOT NULL PRIMARY KEY 
  ,acctID VARCHAR(4) NOT NULL
  ,emailID INTEGER  NOT NULL
  ,type INTEGER  NOT NULL
  ,created DATE  NOT NULL
);
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ABC123,'1/1/2018',12345,NULL,'12345');
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ABC124,'1/1/2018',12345,NULL,'12345');
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ABC125,'1/1/2018',12345,NULL,'12345');
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ABC126,'1/1/2018',12345,NULL,'12345');
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ABC127,'1/1/2018',12345,NULL,'12345');
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ABC127,'1/1/2018',12345,NULL,'12345');
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ACB234,'1/2/2018',NULL,ABCDEFG,NULL);
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ACB234,'1/2/2018',NULL,ABCDEFG,NULL);
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ACB234,'1/2/2018',NULL,ABCDEFG,NULL);
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ACB234,'1/2/2018',NULL,ABCDEFG,NULL);
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ACB234,'1/2/2018',NULL,ABCDEFG,NULL);
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ACB234,'1/2/2018',NULL,ABCDEFG,NULL);
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ABC567,'1/3/2018',789432,NULL,'789432');
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ABC567,'1/3/2018',789432,NULL,'789432');
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ABC567,'1/3/2018',789432,NULL,'789432');
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ABC567,'1/3/2018',789432,NULL,'789432');
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ABC567,'1/3/2018',789432,NULL,'789432');
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ABC567,'1/3/2018',789432,NULL,'789432');
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ABC980,'1/4/2018',NULL,QRXS,NULL);
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ABC980,'1/4/2018',NULL,QRXS,NULL);
INSERT INTO mytable(mainID,acctID,emailID,type,created) VALUES (ABC980,'1/4/2018',NULL,QRXS,NULL);

Here's what I'm trying to make it look like:

id  date    pid pcid    lid
ABC123  1/1/2018    12345   XYZ 12345
ABC124  1/1/2018    12345   XYZ 12345
ABC125  1/1/2018    12345   XYZ 12345
ABC126  1/1/2018    12345   XYZ 12345
ABC127  1/1/2018    12345   XYZ 12345
ABC127  1/1/2018    12345   XYZ 12345
ACB234  1/2/2018    98765   ABCDEFG 98765
ACB234  1/2/2018    98765   ABCDEFG 98765
ACB234  1/2/2018    98765   ABCDEFG 98765
ACB234  1/2/2018    98765   ABCDEFG 98765
ACB234  1/2/2018    98765   ABCDEFG 98765
ACB234  1/2/2018    98765   ABCDEFG 98765
ABC567  1/3/2018    789432  ZF1 789432
ABC567  1/3/2018    789432  ZF2 789432
ABC567  1/3/2018    789432  ZF3 789432
ABC567  1/3/2018    789432  ZF4 789432
ABC567  1/3/2018    789432  ZF5 789432
ABC567  1/3/2018    789432  ZF6 789432
ABC980  1/4/2018    8675309 QRXS    8675309
ABC980  1/4/2018    8675309 QRXS    8675309
ABC980  1/4/2018    8675309 QRXS    8675309

Here's roughly what I was attempting: I feel like I'm overlooking something very obvious however and making this more difficult than it has to be...

 SELECT    a.id
           a.date
           b.pid
           b.pcid
           c.lid
    FROM table a
         LEFT JOIN table b
            on a.id = b.id(not bringing this column in)
         LEFT JOIN table c
            on b.pid = c.lid
         WHERE b.pcid IN ( SELECT lid from table c
                          WHERE b.id = c.id)

Basically, I'm trying to populate the missing columns from PID, PCID, LID. When PCID exists. In table, B, there are 3 Ids. If pcid is populated then pid isn't, etc. I want to populate both pid and pcid.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.322371 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO