SQL query for conditional select

Question

i have tables like this:

Table: Factors

FactorID   SellerID   ContractorID
---------------------------------
1            -1          2
2             1         -1
3            -1          1
4             2         -1

Table: Sellers

SellerID   SellerName
---------------------
1            SellerA      
2            SellerB      
3            SellerC      
4            SellerD

Table Contractors:

ContractorID   ContractorName
-------------------------------
1                    W      
2                    X      
3                    Y      
4                    Z

i want to have query that return some like this:

FactorID   SellerID   ContractorID   ContractorName     SellerName
------------------------------------------------------------------
1            -1          2                  X                NULL
2             1         -1                  NULL             SellerA
3            -1          1                  W                NULL   
4             2         -1                  NULL             SellerB

this is my query but not worked correctly:

SELECT  Factos.* ,
        Seller.sellerName AS sellerName ,
        Contractor.Contractor AS contractorName
FROM    Fctors ,
        Seller ,
        Contractor
WHERE   Seller.SellerID = CASE WHEN Factors.ContrarID = -1 THEN Factors.SellerID
                               ELSE ''
                          END
        AND Contractor.ContractorID = CASE WHEN Factors.SellerID = -1 THEN Factors.ContractorID
                                           ELSE ''
                                      END

Are you getting any errors? you are selecting from Fctors instead of from Factors - is that how you do it in the query? — Alexander
– Alexander, Commented Feb 26, 2014 at 12:32

Alexander · Accepted Answer · 2014-02-26 12:53:02Z

1

No need to use CASE. LEFT OUTER JOIN should be enough. That should do:

  SELECT f.FactorID,
         f.SellerID,
         f.ContractorID,
         c.ContractorName,
         s.SellerName
    FROM Factors f
         LEFT OUTER JOIN Sellers s 
                         ON f.SellerID = s.SellerID
         LEFT OUTER JOIN Contractor c
                         ON f.SellerID = c.SellerID
ORDER BY f.FactorID ASC;

edited Feb 26, 2014 at 12:53

answered Feb 26, 2014 at 12:38

Alexander

3,1992 gold badges21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ziouas · Accepted Answer · 2014-02-26 12:33:46Z

1

A simple JOIN statement should provide required result without any CASE statements

SELECT f.*, s.sellerName as sellerName, c.Contractor as contractorName
  FROM Factors f
  LEFT OUTER JOIN Sellers s ON(f.SellerId=s.SellerId)
  LEFT OUTER JOIN Contractors c ON(f.ContractorId=c.ContractorId)

answered Feb 26, 2014 at 12:33

Ziouas

5191 gold badge6 silver badges19 bronze badges

Comments

TechDo · Accepted Answer · 2014-02-26 12:34:13Z

1

Please try:

SELECT a.*, b.SellerName, c.ContractorName
FROM
Factors A LEFT JOIN Sellers b on a.SellerID=b.SellerID
    LEFT JOIN Contractors c on a.ContractorID=c.ContractorID

answered Feb 26, 2014 at 12:34

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

Comments

Madhawas · Accepted Answer · 2014-02-26 12:36:18Z

1

I think you can get that result just using a query, check below query

SELECT F.FactorID, S.SellerID, C.ContractorID, C.ContractorName, S.SellerName FROM FACTORS F LEFT JOIN SELLERS S ON F.SellerID = S.SellerID
          LEFT JOIN CONTRACTORS C ON F.ContractorID = C.ContractorID

answered Feb 26, 2014 at 12:36

Madhawas

3911 gold badge3 silver badges16 bronze badges

Comments

Chirag Sutariya · Accepted Answer · 2014-02-26 12:37:20Z

1

SELECT Factors.FactorID,Sellers.SellerID AS SellerID ,
Contractors.ContractorID AS ContractorID,
Contractors.ContractorName AS ContractorName,
Sellers.SellerName AS SellerName

FROM Factors LEFT JOIN Sellers ON Factors.FactorID=Sellers.SellerID
LEFT JOIN Contractors ON Factors.FactorID=Contractors.ContractorID

answered Feb 26, 2014 at 12:37

Chirag Sutariya

3621 silver badge11 bronze badges

Comments

Milen · Accepted Answer · 2014-02-26 12:37:58Z

1

Left Outer join is all you need

select f.FactorID, f.SellerID, f.ContractorID, c.ContractorName, s.SellerName
from Factors f left outer join Sellers s on f.SellerID = s.SellerID
    left outer join Contractors c on f.ContractorID = c.ContractorID

answered Feb 26, 2014 at 12:37

Milen

8,8977 gold badges47 silver badges59 bronze badges

Collectives™ on Stack Overflow

SQL query for conditional select

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related