How to Join three tables

I have 3 tables : Orders, Customers and Suppliers

Structure and Data:

Orders

================================
OrdNo, OrdDt,     OrdType, CSID
--------------------------------
1      01/04/2011    C     2
2      01/04/2011    S     1
--------------------------------

Customers

----------
CID, CName
----------
1    John
2    Boby
----------

Sup开发者_如何学编程pliers

=========
SID, SName
----------
1    Tony
2    Mohan
----------

If OrdTYpe = "C" then pick data from Customers table for CSID = CID

If OrdTYpe = "S" then pick data from Suppliers table for CSID = SID

How to list the records like

--------------------------------------
OrdNo, OrdDt,     OrdType, CSID  CSName
--------------------------------------
1      01/04/2011    C     2     Boby
2      01/04/2011    S     1     Tony
--------------------------------------

Does this get what you want?

SELECT ordno, orddt, ordtype, csid,
       COALESCE( c.name, s.name ) csname
  FROM orders o
    LEFT JOIN customer c ON o.ordtype='C' AND c.cid = o.csid
    LEFT JOIN suppliers s ON o.ordtype='S' AND c.sid = o.csid

Another possibility, at least in Oracle, would be:

SELECT ordno, orddt, ordtype, csid,
       CASE WHEN ordtype='C' THEN (SELECT name FROM customer WHERE cid = csid)
            WHEN ordtype='S' THEN (SELECT name FROM suppliers WHERE sid = csid)
       END csname
  FROM orders;

Martin has a good point. I'm no pro, but I would suggest making an intermediate table that will allow you to keep customers and suppliers separate, yet still pull from a common set of id's

Orders

========================
OrdNo, OrdDt,     CSID
------------------------
1      01/04/2011   2
2      01/04/2011   1
-------------------------

ClientIDs

---
ID
---
1
2
3
4
---

Customers

----------
CID, CName
----------
1    John
3    Boby
----------

Suppliers

=========
SID, SName
----------
2    Tony
4    Mohan
----------

That way you end up with something like this:

-------------------------------
OrdNo, OrdDt,      CSID  CSName
-------------------------------
1      01/04/2011    2     Tony
2      01/04/2011    1     John
-------------------------------

and by back tracking up through the (now) unique ID you will be able to tell if it is a customer, or supplier... also I'm pretty sure your SQL will run faster with this route (don't hold me to it though). If you like this idea, I could look into SQL to back it.

SELECT o.[OrdNo], o.[OrdDt], o.[OrdType], o.[CSID],
    CASE WHEN o.[OrdType] = 'C'
        THEN c.[CName]
        ELSE s.[SName]
    END as [CSName]
FROM Orders AS o
LEFT JOIN Customers AS c
    ON o.[CSID] = c.[CID]
LEFT JOIN Suppliers AS s
    ON o.[CSID] = s.[SID]