Mysql – Join that returns always 1 record

join;MySQL

I have a table that holds tests. For every test you have a generic one (TYPE field is NULL) but for some tests you can have also more specific tests (TYPE field has a value).

I need a join to this table on the test and type but so that if no type is specified I get the generic tests (That is no problem as both sides have NULL) but if a type is specified I get the test of that type if there is one, or the generic test when there exist no specific one.

    TestTable
    ... Test   Grp  Type  Description ...
    ... TestA  G1   NULL  TestA_Gen    ...
    ... TestA  G1   M     TestA_M     ...
    ... TestB  G1   NULL  TestB_Gen   ...
    ... TestB  G1   X     TestB_X     ...

    BaseTable
    ... Pers  Type ...
    ... P_A   Null ...
    ... P_B   M    ...
    ... P_C   X    ...

Select B.PERS, T.DESCRIPTION
from BaseTable B
Join Testtable T on ??? and T.Grp = 'G1'

should give
P_A TestA_Gen
P_A TestB_Gen
P_B TestA_M
P_B TestB_Gen
P_C TestA_Gen
P_C TestB_X

How do I do that in MySQL?

Many thanks in advance,

Best Answer

According to given sample data there is "Type" which is common in both table

Select B.PERS, T.DESC
from BaseTable B
right outer Join Testtable T 
on ( T.type is null or B.Type=T.type or B.Type is null)
and T.Grp = 'G1' 
where T.DESC<>'' 
order by B.PERS asc

Related Solutions

Sql-server – Join Calender Table

I think you want (tested at SQL-Fiddle-1):

SELECT  the_id    = dd.the_id, 
        the_day   = ct.the_day,
        the_count = COALESCE(SUM(dt.the_count), 0)
FROM 
        calendartable AS ct
    CROSS JOIN
        ( SELECT DISTINCT the_id 
          FROM datatable
          WHERE the_date BETWEEN @mindate 
                             AND @maxdate
        ) AS dd
    LEFT JOIN 
        datatable AS dt
            ON  dt.the_date = ct.the_day
            AND dt.the_id = dd.the_id 
WHERE 
        ct.the_day BETWEEN @mindate 
                        AND @maxdate
GROUP BY 
        dd.the_id, ct.the_day
ORDER BY 
        the_id, the_day ;

It can also be written like this (SQL-Fiddle-2):

SELECT  the_id    = dd.the_id, 
        the_day   = ct.the_day,
        the_count = COALESCE(
            ( SELECT SUM(dt.the_count)
              FROM   datatable AS dt
              WHERE  dt.the_date = ct.the_day
                AND  dt.the_id = dd.the_id     
            )               
                            , 0)
FROM 
        calendartable AS ct
    CROSS JOIN
        ( SELECT DISTINCT the_id 
          FROM datatable
          WHERE the_date BETWEEN @mindate 
                             AND @maxdate
        ) AS dd
WHERE 
        ct.the_day BETWEEN @mindate 
                        AND @maxdate
ORDER BY 
        the_id, the_day ;

Postgresql – Join on different types

Not valid integer types

If the values in the field are not actually valid integer types (digits and leading - only), a JOIN like @mustaccio suggested would fail with an exception: you couldn't cast to integer at all.

It would have to be the other (even more expensive) way round:

SELECT *
FROM   tbl1 t1
JOIN   tbl2 t2 ON t1.taxonomy_id::varchar = t2.id;

Also, since @mustaccio misleadingly suggested to use int8: Don't. integer equals int4. int8 would be bigint.

You can support that with a functional index:

CREATE INDEX tbl1_taxonomy_id_idx ON tbl1 (cast(taxonomy_id AS varchar));

Valid integer types

If we are dealing with valid integer types, you could just convert your column id to integer - if your setup allows that.

ALTER TABLE tbl2 ALTER COLUMN id TYPE integer USING id::int;

Then your problem is gone for good:

SELECT *
FROM   tbl1 t1
JOIN   tbl2 t2 ON t1.taxonomy_id = t2.id;

Barring that (if you cannot convert the column for some reason), a functional index would help:

CREATE INDEX tbl2_id_idx ON tbl2 (cast(id AS int));

SELECT *
FROM   tbl1 t1
JOIN   tbl2 t2 ON t1.taxonomy_id = t2.id::int;