Values from multiple tables on a row

Question

Hello, I have the following 3 tables

Table1

ID |   NAME
-----------
1  | X
2  | Y
3  | Z


Table2

ID |   NAME
-----------
1  | A
2  | B
3  | C

Table3
ID | P (Boolean field)  | Other cols
 1 | True ...
 2 | True....
 1 | False

now I need a query on table3 that has to do the following.

to display the name field of table1 and table2. But my problem is that

if field P on table3 is true i want it to display the name of table2's field where table2.id = table3.id but if it is false i need it to read the name of table1's name field where table1.id = table3.id.

The program which will display the results is a desktop application and i could do it with some procedure or smth to display them, but would be nicer if I had a sql query to do this for me.

Thanks

Answer 1

This:

SELECT  CASE WHEN p
        THEN
        (
        SELECT  name
        FROM    table2 t2
        WHERE   t2.id = t3.id
        ) 
        ELSE
        (
        SELECT  name
        FROM    table1 t1
        WHERE   t1.id = t3.id
        )
        END
FROM    table3 t3

, or this:

SELECT  CASE WHEN p THEN t2.name ELSE t1.name END
FROM    table3 t3
JOIN    table1 t1
ON      t1.id = t3.id
JOIN    table1 t2
ON      t2.id = t3.id

In systems capable of doing HASH JOIN (that is Oracle, SQL Server, PostgreSQL, but not MySQL), the second one is better if the boolean values are distributed evenly, i. e. there are lots of both TRUE's and FALSE's, and if table3 is quite large.

The first one is better if there is a skew in distribution, if there are much fewer rows in table3 then in table1 or table2, or if you are using MySQL.

Update:

If the majority of fields are false, the following query will probably be the best:

SELECT  CASE WHEN p THEN
        (
        SELECT  name
        FROM    table2 t2
        WHERE   t2.id = t3.id
        )
        ELSE t1.name
        END AS cname
FROM    table3 t3
JOIN    table1 t1
ON      t1.id = t3.id
ORDER BY
        cname

Subquery here will only be used as a fallback, and will be executed only for the rare TRUE values.

Update 2:

I can't check it on Firebird, but on most systems the ORDER BY syntax as in query above will work. If it does not, wrap the query into an inline view:

SELECT  cname
FROM    (
        SELECT  CASE WHEN p THEN
                (
                SELECT  name
                FROM    table2 t2
                WHERE   t2.id = t3.id
                )
                ELSE t1.name
                END AS cname
        FROM    table3 t3
        JOIN    table1 t1
        ON      t1.id = t3.id
        ) q
ORDER BY
        cname

, though it may hamper performance (at least in MySQL it does).

Answer 2

you can use

select if(P, t1.name, t2.name) ...

Answer 3

SELECT `id`, IF(`table3`.`P`, `table2`.`name`, `table1`.`name`) AS `name`
FROM `table3`
JOIN `table2` USING (`id`)
JOIN `table1` USING (`id`)

Answer 4

Possibly not an option but sometimes a schema redesign will solve a lot of problems. Any solution that executes per-row functions should be examined very carefully for performance problems (actually any query should be continuously monitored for performance problems but especially per-row function ones).

Consider combining tables 1 and 2 together thus:

Table1And2:
    Id | IsTable2 | Name
    ---+----------+-----
    1  | false    | X
    2  | false    | Y
    3  | false    | Z
    1  | true     | A
    2  | true     | B
    3  | true     | C
    Primary key (Id,IsTable2)
    Possible index on (IsTable2) as well, depending on DBMS.

Table3:
    Id | P (Boolean field)  | OtherCols
    ---+--------------------+----------
     1 | true               |
     2 | true               |
     1 | false              |

Then your query becomes a relatively simple (and almost certainly blindingly fast):

select a.Id, b.Name, a.OtherCols
from Table3 a, Table1And2 b
where a.Id = b.Id and a.P = b.isTable2

The performance issue usually only rears its head for large tables, mainframe sizes where 5 million rows is considered a configuration table :-) It may not be necessary for the sort of tables you're dealing with but it's a useful tool to have in the armory.