Geek Answers Handbook

UNION or JOIN for SELECT from multiple tables

My question

I am trying to select one row from several tables based on parameters, but my limited knowledge of SQL joins holds me back. Can someone point me in the right direction?

Consider these table structures:

+-----------------------+     +---------------------+
| Customers             |     | Sellers             |
+-------------+---------+     +-----------+---------+
| Customer_ID | Warning |     | Seller_ID | Warning |
+-------------+---------+     +-----------+---------+
| 00001       | Test 1  |     | 00008     | Testing |
| 00002       | Test 2  |     | 00010     | Testing |
+-------------+---------+     +-----------+---------+

What I would like to do is one SELECTto retrieve only one row, and that row will have a “Warning” field for each of the tables based on the X_ID field.

Desired Results

So, if I provided the following information, I would get the following results:

Example 1:

Customer_ID = 00001
Seller_ID = 00008

Results:
+-----------------------------------+
| Customer_Warning | Seller_Warning |
+------------------+----------------+
| Test 1           | Testing        |
+------------------+----------------+

Example 2:

Customer_ID = 00001
Seller_ID = 00200

Results:
+-----------------------------------+
| Customer_Warning | Seller_Warning |
+------------------+----------------+
| Test 1           | NULL           |
+------------------+----------------+

What i tried

This is my current code (I get a lot of lines):

SELECT c.Warning 'Customer_Warning', s.Warning AS 'Seller_Warning'
FROM Customers c,Sellers s
WHERE c.Customer_ID = @Customer_ID
    OR s.Seller_ID = @Seller_ID

But I also played with UNION, UNION ALLand JOIN. Which method should I go for?

+4
4

, , :

SELECT 
    (SELECT Warning 
     FROM Customers 
     WHERE Customer_ID = @Customer_ID) AS Customer_Warning,
    (SELECT Warning 
     FROM Sellers 
     WHERE Seller_ID = @Seller_ID) AS Seller_Warning
+11

, , , .

, AND OR:

SELECT c.Warning 'Customer_Warning', s.Warning AS 'Seller_Warning'
FROM Customers c 
JOIN Sellers s
ON c.Customer_ID = @Customer_ID
    AND s.Seller_ID = @Seller_ID

, :

SELECT c.Warning 'Customer_Warning', s.Warning AS 'Seller_Warning'
FROM (SELECT Warnning FROM Customers WHERE c.Customer_ID = @Customer_ID) c,
     (SELECT Warning FROM Sellers s WHERE s.Seller_ID = @Seller_ID) s

, SQL .

, ID .

FULL OUTER JOIN:

SELECT c.Warning 'Customer_Warning', s.Warning AS 'Seller_Warning'
FROM Customers c 
FULL OUTER JOIN Sellers s
ON c.Customer_ID = @Customer_ID
    AND s.Seller_ID = @Seller_ID
+5

, , , , , .

full outer join:

SELECT c.Warning as Customer_Warning, s.Warning AS Seller_Warning
FROM Customers c FULL OUTER JOIN
     Sellers s
     ON c.Customer_ID = @Customer_ID AND s.Seller_ID = @Seller_ID;

, . . . .

+2

, , - . , . UNION :

SELECT 'C' AS Type, c.Warning
FROM Customers c
WHERE c.Customer_ID = @Customer_ID
UNION
SELECT 'S' AS Type, s.Warning
FROM Sellers s
WHERE s.Seller_ID = @Seller_ID

You can use the flag to distinguish between warnings in the code. This will be more efficient than joining or subqueries, and will be easy to understand later (when refactoring). I know that this is not 100% what you ask in your question, but why will I challenge the question :)

0
source

More articles:


All Articles