How to select columns from main table and conditionally select columns from second table with SQL-CodePudding

I'm trying to build a query that pulls data from a main table, and possibly from a second table depending on the values. Currently I have

SELECT    TOP (50) A.MessageID, 
          A.UserID (CASE IF A.MessageID = B.MessageID 
                         THEN SELECT B.MessageData ELSE NULL
                   ) 
FROM Message as A, Market as B

Any ideas? To be more specific, if A.MessageID is found in table B, I want MessageData from table B, otherwise I just want MessageData column to be NULL.

CodePudding user response：

You should to use LEFT JOIN

SELECT TOP (50)
    A.MessageID, 
    A.UserID,
    B.MessageData 
FROM Message as A
LEFT JOIN Market as B ON A.MessageID = B.MessageID

MSSQL LEFT JOIN online

MySQL solution:

SELECT
    A.MessageID, 
    A.UserID,
    B.MessageData 
FROM Message as A
LEFT JOIN Market as B ON A.MessageID = B.MessageID 
ORDER BY A.MessageID DESC
LIMIT 50;

MySQL LEFT JOIN LIMIT