0

I am new to SQL and I would like to get some insights for my problem I am using the following query,

  select id,
         pid 
    from assoc
   where id in (100422, 100414, 100421, 100419, 100423)

All these id need not have pid, some doesn't and some has pid. Currently it skips the records which doesn't have pid. I would like a way which would show the results as below.

         pid    id
         -----------
         703    100422
         313    100414
         465    100421
         null   100419
         null   100423

Any help would be greatly appreciated. Thanks!

Improve this question
8
  • your query looks OK, it must not skip any pid (there's no filter on the field). Try select id, pid from assoc where pid is null or select id, pid from assoc where pid is null and id = 100423 to examine the records with null pid. Do you have, say, null 100423 record Commented Nov 28, 2016 at 8:30
  • Hi, Thanks for your response. The trouble is, there is no entry at all when there is no pid for the id. when I execute the query, select id,pid from assoc where pid is null, it returns a blank row which states there is no record when there is no pid. Commented Nov 28, 2016 at 8:32
  • do you have null pid at all? Does select id, pid from assoc where pid is null returns anything but an empty cursor? Commented Nov 28, 2016 at 8:33
  • No null values, returns only empty cursor. Commented Nov 28, 2016 at 8:35
  • If there is 'no entry at all' for the pid, then the associated id must not exist in the table? In this case you need to add another piece using union all which returns those not existing. Do you have another different 'master id' table which contains all id's, including those without a pid? Commented Nov 28, 2016 at 8:36

2 Answers 2

Reset to default
2

Oh, I think I've got the idea: you have to enumerate all the ids and corresponding pids. If there's no corresponding pid, put null (kind of outer join). If it's your case, then Oracle solution can be: 

   with 
     -- dummy: required ids
     dummy as (
                 select 100422 as id from dual
       union all select 100414 as id from dual
       union all select 100421 as id from dual
       union all select 100419 as id from dual
       union all select 100423 as id from dual),
     -- main: actual data we have
     main as (
       select id,
              pid 
         from assoc
           -- you may put "id in (select d.id from dummy d)"
        where id in (100422, 100414, 100421, 100419, 100423))

   -- we want to print out either existing main.pid or null
   select main.pid as pid,
          dummy.id as id
     from dummy left join main on dummy.id = main.id
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you Dmitry, i vaguely understand this, but how are these chunks related with each other? The last select is kind of independent?
dummy returns required ids, main - actual data; left join combines them together
Well, I couldn't get the query to run in sql developer. How to connect the last part "select main.pid as pid, dummy.id as id from dummy left join main on dummy.id = main.id" with the other two? Please help.
with see oracle-base.com/articles/misc/with-clause allows me to factor the query into fragments dummy, main and combine them as a third query (please, notice from dummy left join main...).
@lal1990 everything in the code block is a single sql statement. You may need to remove the blank lines (or highlight the entire statement) in order to get it to run it to run on SQL Developer, though.
0

id is obtained from other table and assoc only has pid associated with id.

The assoc table seems to be the association table used to implement a many-to-many relationship between two entities in a relational database.

It contains entries only for the entities from one table that are in relationship with entities from the other table. It doesn't contain information about the entities that are not in a relationship and some of the results you want to get come from entities that are not in a relationship.

The solution for your problem is to RIGHT JOIN the table where the column id comes from and put the WHERE condition against the values retrieved from the original table (because it contains the rows you need). The RIGHT JOIN ensures all the matching rows from the right side table are included in the result set, even when they do not have matching rows in the left side table.

Assuming the table where the id column comes from is named table1, the query you need is:

SELECT assoc.id, assoc.pid 
FROM assoc
    RIGHT JOIN table1 ON assoc.id = table1.id
WHERE table1.id IN (100422, 100414, 100421, 100419, 100423)
Improve this answer

1 Comment

Thanks axiac, the result now i get is pid id ----------- 703 100422 313 100414 465 100421 null null null null I need the id along with null values, any way i can achieve that?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.