SQL Query Help - Joining Multiple Columns Based On

问题:

Possible Duplicate:
SQL query help - have two where conditons in join condition

I have the following tables with the columns as below. I have mentioned what I need from this. I already posted a link in here SQL query help - have two where conditons in join condition with what I have been trying but cannot get this through. Once again positing it plainly with what I need:

Book
BookId, BookName

Desk
DeskId, BookId ,DeskName

CounterParty
CPId, CpName

Trade
TradeId, Buyer, Seller

This is how the Buyer and Seller data would be :

Buyer Seller
B3232  B323
C32    B222
B323   C323

Based on the starting character B or C in these two columns, I need to join Book or CP table to check the ids.

I need **t.TradingDeskName, b.BookName, c.CpName, t.Buyer, t.Seller.**

Any help is very much appreciated.

Thanks, mani

p.s : I am trying to get this done through SQL or Linq to Sql.

---

The recent query but have more to fix :

SELECT DISTINCT desk.Name as TradingDeskName, b.Name as Book, t.Seller, t.Buyer, c.PartyName, FROM TradingDesk AS desk 

 RIGHT JOIN Book as b
    ON  b.TradingDeskId = d.Id
 RIGHT JOIN Trade as t  
    ON LEFT(t.Buyer, 1) = 'B' AND SUBSTRING(t.Buyer, 2, len(t.Buyer)) = b.Id
 LEFT JOIN Book as b1 
    ON LEFT(t.Seller, 1) = 'B' AND SUBSTRING(t.Seller, 2, len(t.Seller)) = b1.Id 
 LEFT JOIN CounterParty as c 
    ON LEFT(t.Buyer, 1) = 'C' AND SUBSTRING(t.Buyer, 2, len(t.Buyer)) = c.PartyId 
 LEFT JOIN CounterParty as c1 
    ON LEFT(t.Seller, 1) = 'C' AND SUBSTRING(t.Seller, 2, len(t.Seller)) = c1.PartyId

As I mentioned I need : Desk.Name - B.Name - T.Seller - T.Buyer- C.PartyName

The C.PartyName will have the value if T.Seller or T.Buyer value is starting with 'C' (from CounterParty Table) else will be null.

With the above query, I have null values coming in Desk.Name, B.Name and the logic of gettig C.PartyName is also not working.

---

回答1:

You could do it something like this (untested):

select 
  t.Buyer, 
  t.Seller, 
  case when t.Buyer like 'B%' THEN (select BookName from Book where BookId = t.Buyer)
                              ELSE (select CpName from Counterparty where CPId = t.Buyer)
  end BuyerName,
  case when t.Buyer like 'B%' THEN (select DeskName from Desk where BookId = t.Buyer)
                              ELSE NULL
  end BuyerDeskName,
  case when t.Seller like 'B%' THEN (select BookName from Book where BookId = t.Seller)
                               ELSE (select CpName from Counterparty where CPId = t.Seller)
  end SellerName,
  case when t.Seller like 'B%' THEN (select DeskName from Desk where BookId = t.Seller)
                               ELSE NULL
  end SellerDeskName,
from 
  Trade t

The problem you have is that, since the table you want to join to is data driven, you can't specify it in the FROM clause..

回答2:

There are a couple ways I could think of for achieving the desired results but because first things should come first, I'd suggest to modify the DB design if it is at all possible.

So, here are the 2 queries that I could work out:

Query 1

SELECT `t`.*,
(CASE 
    WHEN LEFT(`t`.`Buyer`, 1) = 'B' THEN
        (SELECT `b`.`BookName`
         FROM `Book` `b`
         WHERE `b`.`BookId` = SUBSTRING(`t`.`Buyer`, 2))
    ELSE (SELECT `c`.`CPName`
          FROM `CounterParty` `c`
          WHERE `c`.`CPId` = SUBSTRING(`t`.`Buyer`, 2))
END) AS `buyer_name`,
(CASE
    WHEN LEFT(`t`.`Seller`, 1) = 'B' THEN
        (SELECT `b`.`BookName`
         FROM `Book` `b`
         WHERE `b`.`BookId` = SUBSTRING(`t`.`Seller`, 2))
    ELSE (SELECT `c`.`CPName`
          FROM `CounterParty` `c`
          WHERE `c`.`CPId` = SUBSTRING(`t`.`Seller`, 2))
END) AS `seller_name`
FROM `Trade` `t`

Query 2

SELECT *
FROM `Trade` `t`
LEFT JOIN `Book` `b` ON LEFT(`t`.`Buyer`, 1) = 'B' AND SUBSTRING(`t`.`Buyer`, 2) = `b`.`BookId`
LEFT JOIN `Book` `b1` ON LEFT(`t`.`Seller`, 1) = 'B' AND SUBSTRING(`t`.`Seller`, 2) = `b1`.`BookId`
LEFT JOIN `CounterParty` `c` ON LEFT(`t`.`Buyer`, 1) = 'C' AND SUBSTRING(`t`.`Buyer`, 2) = `c`.`CPId`
LEFT JOIN `CounterParty` `c1` ON LEFT(`t`.`Seller`, 1) = 'C' AND SUBSTRING(`t`.`Seller`, 2) = `c1`.`CPId`;

Both the above queries return same results but in different formats. Please try and see which one works best for you.

Also, it isn't very clear from your question where does the table Desk fit in and what relations does it hold with other tables. Please feel free to add respective columns you'll need from Desk.

Please note that the suggested queries are in MySQL. It is not very clear what system are you running - you've mentioned in your post that you are trying using SQL or Linq SQL and in the tags you've mentioned everything + MySQL.