CODEWITHSUNDEEP
mysql
wklumpen
wklumpen

Reputation: 91

Extending Values in Joined Tables with MYSQL

I currently have a table of concentrations, which are linked to a table of elements. There is a concentration table

|conc_id|element_id|conc|

and and element table

|element_id|symbol|

The concentration table only has element_ids for each conc_id that have non-zero concentrations. What I am trying to do is create a query which will, for each concentration_id, list all the elements in order, with their concentration, regardless of whether they're non-zero or not. I have tried this in a number of increasingly complicated ways (starting with a RIGHT JOIN) but this always outputs a NULL conc_id when there is none of that element in it. The output I am looking for is something like this:

|conc_id|element_id|symbol|conc|
|1      |1         |H     |1.2 |
|1      |2         |He    |NULL|
|1      |3         |Li    |2.3 |
              ...
|3      |1         |H     |4.5 |
|3      |2         |He    |NULL|
|3      |3         |Li    |NULL|
              ...

And so on. Is there a way I can do this without having NULL conc_id?

Thanks for any help in advance...

Upvotes: 1

Views: 73

Answers (4)

Erenor Paz
Erenor Paz

Reputation: 3171

select conc_id, conc.element_id, symbol, concentration from concentration_table as conc 
join element_table as elem on conc.element_id = elem.element_id
order by conc.conc_id asc

This gives me this table:

conc_id     element_id  concentration   element_id  symbol 
1           1                  1.2           1       H
1           2                  0             2       He
1           3                  2.3           3       Li
3           1                  4.5           1       H
3           2                  0             2       He
3           3                  0             3       Li

And these are the starting tables:

element_table:

element_id    symbol 
1               H
2               He
3               Li

concentration table:

conc_id        element_id        concentration 

1                1                1.2
1                2                0
1                3                2.3
3                1                4.5
3                2                0
3                3                0

EDIT: edited the query to obtain correct table values requested

Upvotes: 0

ESG
ESG

Reputation: 9425

Assuming you do not have a simple list of all the conc_id somewhere, you could try something like:

SELECT c.conc_id, e.element_id, e.symbol, c2.conc

FROM
(SELECT DISTINCT conc_id FROM concentration c) c
INNER JOIN element e
LEFT JOIN concentration c2 ON c2.conc_id = c.conc_id AND c2.element_id = e.element_id

ORDER BY c.conc_id, e.element_id

The logic is:

  1. Get a list of all unique conc_id derived table c
  2. Do a cross join against element (for each row of c, list all row of element)
  3. Left join against the full concentration table.

If you have another table with all the conc_id, you could avoid the derived table entirely.

Upvotes: 0

tera
tera

Reputation: 46

First, you have to build a table that contains all combinations between conc_id and element_id:

SELECT DISTINCT conc_id, elements.element_id FROM concentrations, elements;

Which gives you the following table:

| conc_id | element_id |
|       1 |          1 |
|       1 |          2 |
|       1 |          3 |
            ...
|       3 |          1 |
|       3 |          2 |
|       3 |          3 |
            ...

Once you have such a table, joining it with concentrations and elements should be fairly simple. For example:

SELECT combinations.conc_id, combinations.element_id, symbol, conc
FROM 
  (SELECT DISTINCT conc_id, elements.element_id FROM concentrations, elements) AS combinations
  LEFT JOIN concentrations ON (combinations.conc_id = concentrations.conc_id AND combinations.element_id = concentrations.element_id)
  LEFT JOIN elements ON (combinations.element_id = elements.element_id);

Result:

| conc_id | element_id | symbol | conc |
|       1 |          1 | H      |  1.2 |
|       1 |          2 | He     | NULL |
|       1 |          3 | Li     |  2.3 |
                  ...
|       3 |          1 | H      |  4.5 |
|       3 |          2 | He     | NULL |
|       3 |          3 | Li     | NULL |
                  ...

Upvotes: 3

Ant P
Ant P

Reputation: 25221

To be honest, I think the best way would be to actually fill in the missing data with zero values in the conc column. Otherwise, you're trying to hack it to display data that you don't actually have and it would be much more tangible than keeping null values anyway...

Assuming there are no performance consideration that this would impede, of course.

Upvotes: 0

Related Questions