CODEWITHSUNDEEP
sqlcoalesce
awags1
awags1

Reputation: 322

Use SQL to collapse to one row by matching ids found scattered across columns

I have a SQL query that looks something like this:

SELECT *
FROM
 ((SELECT * FROM schema.table1) AS a
     FULL OUTER JOIN
  (SELECT * FROM schema.table2) AS b ON a.id1 = b.id2
     FULL OUTER JOIN
  (SELECT * FROM schema.table3) AS c on b.id2 = c.id3
  );

Which returns a table that looks like this:

    id1       id2       id3       mean1       mean2        mean3
"apple"      NULL      NULL       100.0        NULL         NULL
   NULL   "apple"   "apple"        NULL       105.4        116.8
   NULL    "pear"      NULL        NULL       441.3         NULL
   NULL     NULL     "pear"        NULL        NULL        444.9

I would like to alter (or add to) the query to get a result with one row for each fruit, while keeping all of the columns. So my desired result will look like this: 

    id1       id2       id3       mean1       mean2        mean3
"apple"   "apple"   "apple"       100.0       105.4        116.8
   NULL    "pear"    "pear"        NULL       441.3        444.9

How would I alter the SQL query to achieve what I want?

Thanks a lot!

Upvotes: 0

Views: 194

Answers (2)

KRM
KRM

Reputation: 1435

Without knowing what's the data in your table1, table2, and table3, I think possibly it could be a data issue with double quotes, you may want to give it a try to copy and paste the data in notepad++ and see the difference in double quotes.

enter image description here

If the data (double quotes, spelling, spaces etc.) is all good, then your query should give you expected results. I tried it with some sample data and seems to be working.

enter image description here

To clarify, if table1 had ''apple'' and not "apple" (notice the difference in double quotes by pasting the data in notepad++). And table2 had ''pear'' and not "pear" (notice the difference in double quotes by pasting the data in notepad++) then the result of the SAME query would return the query result like what you are getting. Check below screenshot. I tried it with that data as well and in that case your query does not seem to be working as expected.

enter image description here

Upvotes: 3

Gauravsa
Gauravsa

Reputation: 6528

You can simply use this:

SELECT *
FROM
 ((SELECT * FROM schema.table1) AS a
     FULL OUTER JOIN
  (SELECT * FROM schema.table2) AS b ON a.id1 = b.id2
     FULL OUTER JOIN
  (SELECT * FROM schema.table3) AS c on b.id2 = c.id3
  )
 group by id1, id2, id3, mean1, mean2, mean3;

Upvotes: 0

Related Questions