0

Let's say I have a TABLE a in which a COLUMN data is the one to join on for 2 other tables (TABLE b and TABLE c) because I want to get a COLUMN info in b or c.
The thing is a.data will match only with b.data or only with c.data.

  • How can I get b.info or c.info based on a ?


How far did I try:

After many attempts with obviously wrong syntax, the best I did was in 2 queries:

SELECT b.info FROM a INNER JOIN b ON a.data = b.data
SELECT c.info FROM a INNER JOIN c ON a.data = c.data

I would like to achieve something that could look like this:

SELECT alias.info FROM a INNER JOIN (b OR c) AS alias ON a.data = alias.data


Why do I want to do this

Actually I can get the data I want and work with it using PHP. But I am making a huge process (unrelated to my question) instead of a good query and the page takes too much time to load.

AymDev
  • 6,626
  • 4
  • 29
  • 52

3 Answers3

1

Try something like this:

SELECT IFNULL(b.info,c.info) AS info
FROM a
    LEFT JOIN b ON a.data = b.data
    LEFT JOIN c ON a.data = c.data
SunKnight0
  • 3,331
  • 1
  • 10
  • 8
1

You can left join to both of the b and c tables, then use whichever info field is not NULL:

select coalesce(b.info, c.info) info
from a
  left join b on a.data = b.data
  left join c on a.data = c.data
Hunter McMillen
  • 59,865
  • 24
  • 119
  • 170
  • Reading the answer of [this question](https://stackoverflow.com/questions/18528468/what-is-the-difference-bewteen-ifnull-and-coalesce-in-mysql) I will use your answer as I didn't mentionned `TABLE d` in my question. +1 for explaining your answer, thanks a lot ! – AymDev Apr 20 '18 at 15:36
1

If data is guaranteed to be UNIQUE in tables b and c

We can use outer join to attempt to get matching row from each of the tables, and then use expression in the SELECT list to test whether we got a matching row. For example:

SELECT a.data
     , IF(b.data IS NULL,IF(c.data IS NULL,NULL,'c'),'b') AS info_src
     , IF(b.data IS NULL,c.info,b.info) AS b_or_c_info
  FROM a
  LEFT
  JOIN b 
    ON b.data = a.data
  LEFT
  JOIN c
    ON c.data = a.data

If we find a matching row in b, then b.data is guaranteed to be non-NULL. We know the value is non-NULL because it satisfied an equality (=) comparison.

Note that IF is MySQL specific syntax. A more portable ANSI-standards compliant version 

       CASE
         WHEN b.data IS NULL
         THEN c.info
         ELSE b.info
       END  AS b_or_c_info
spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Thanks it works well too. I am aware that `IF` is MySQL specific but I don't completely understand the query. Is the `info_src` important ? Thank you for your answer – AymDev Apr 20 '18 at 15:41
  • The `info_src` column isn't required. It's just an additional column in the result. We frequently include a discriminator column, like `info_src` in this example. to tell us which row source the value of `info` is from... b, c or neither. In some cases, we want to know that, and including a discriminator column is a convenient way to communicate that information back If it's not important to know which table `b_or_c_info` value came from, then `info_src` column is not important. (I included it in the example because in some use cases, it is important to know the source.) – spencer7593 Apr 20 '18 at 16:13