jeudi 8 juin 2017

mysql select on two different tables depending on field-value

I have the following problem and my brain burns out :)

I have two tables with personal data (one for customer 'table_a' and one for interesting peoples 'table_b'). In a third table 'table_inv' there are entries with invited_persons from both tables (a nd b).

The table_inv has a field 'invited_id' in which the id from table a or table b is stored. Another field 'type' has the value 'cust' or 'interest' to separate from which table (a or b) the entry comes.

Now i have to get the full data from table_a / table_b depending on the value from field 'type'...

I tried it whith case and if but with no result.

I'm searching for something like this:

SELECT a.id, a.created, a.userid, a.type, a.inviteid, b.name_first, b.name_last, b.zip, b.city
FROM ext_event_invites a
CASE a.type
    WHEN 'CUST' THEN
        LEFT JOIN accounts b ON a.inviteid = b.id;
    ELSE
        LEFT JOIN ext_contacts b ON a.inviteid = b.id;
END CASE
WHERE a.eventid = :eventid AND a.userid = '4711' 
ORDER BY b.name_last ASC, b.name_first ASC

Can somebody help me?

Aucun commentaire:

Enregistrer un commentaire