Joining/if 3 tables

[GuitarGod]

Hi all,

 

I'm not terribly good when it comes to SQLs, so forgive me if this seems like a dumb question. I have 3 tables (orders, non_customers and customers). Both the non_customers and customers table have a field in them called 'details'. In the orders table, I have a field called 'customer_type'. What I'm trying to convey is that if the 'customer_type' value is 0, then I would like the 'details' field pulled from the non_customers table, where as if the 'customer_type' value is 1, I'd like the 'details' field pulled from the customers table.

SELECT o.*, n.*, c.*

FROM orders o

LEFT JOIN non_customers n

ON o.customer_type = 0

LEFT JOIN customer c

ON o.customer_type = 1

The code above doesn't seem to 'collate' the tables, it seems to return all of the orders, then all of the customer details.

 

Any help is appreciated

 

Regards.

[Barand]

Does the order table have a customer id to relate to the other tables? If they are a non_customer is there still a customer ID.

 

How about sharing your table structures

[GuitarGod]

Hi Barand,

 

Yes both the customers and non_customers table have a customer ID.

 

Regards.

[Barand]

try

$sql = "SELECT o.ord_id, o.customer_type,
    CASE customer_type
        WHEN 1 THEN c.details
        WHEN 0 THEN nc.details
    END as details
FROM orders o
LEFT JOIN non_customer nc ON o.cust_id = nc.cust_id
LEFT JOIN customer c ON o.cust_id = c.cust_id";

 

[GuitarGod]

If memory serves, this isn't the first timed you've helped me and got the answer spot on. Many thanks, sincerely