This is the scenario. There are three tables, TableA, TableB and Table C

TableA

TableB

TableC

Expected Result

Table A's ProductId is foreign key to ProductId of TableB and Table C.

What would be a query to get this expected result by using where clause based on Code 'testcode'

CodePudding user response：

Inner join can help you I think.

SELECT column_name(s)
  FROM TableA
  INNER JOIN TableB
  ON TableA.ProductId = TableB.ProductId
  INNER JOIN TableC ON TableA.ProductId = 
  TableC.ProductId);

CodePudding user response：

You will want to use left join, as that returns null when there is no match on joining conditions. In the where clause for TableB and TableC you add the filtering for 'testcode'. Use table aliases for TableA (TA), TableB (TB), and TableC (TC) if you care about abbreviating the code.

Assuming SQL Server:

CREATE TABLE #tableA (id int, Name varchar(100), ProductId varchar(100))
CREATE TABLE #tableB (id int, Name varchar(100), ProductId varchar(100), Code varchar(100))
CREATE TABLE #tableC (id int, OfferName varchar(100), ProductId varchar(100), Code varchar(100))

INSERT INTO #tableA VALUES (1, 'product1', 'prod1'), (2, 'product2', 'prod2')
INSERT INTO #tableB VALUES (1, 'RandomName', 'prod2', 'testcode')
INSERT INTO #tableC VALUES (1, 'HappyOffer', 'prod1', 'testcode')
--Your DDL values above

SELECT TA.ProductId AS [TableA.ProductId]
      ,TA.Name AS [TableA.Name]
      ,TB.Name AS [TableB.Name]
      ,TC.OfferName AS [TableC.OfferName]
  FROM #tableA TA
    LEFT JOIN #tableB AS TB ON TA.ProductId = TB.ProductId
    LEFT JOIN #tableC AS TC ON TA.ProductId = TC.ProductId
 WHERE (TB.Code = 'testcode' OR TC.Code = 'testcode')

--put the ordering here as a bonus, since you wanted prod2 listed first in the ProductId
ORDER BY CASE WHEN TA.ProductId = 'prod2' THEN 1 ELSE 2 END

/* --to drop the temp tables afterwards
DROP TABLE #tableA
DROP TABLE #tableB
DROP TABLE #tableC
*/

Produces output:

TableA.ProductId TableA.Name TableB.Name TableC.OfferName
---------------- ----------- ----------- ----------------
prod2            product2    RandomName  NULL
prod1            product1    NULL        HappyOffer

While this probably answers the question a foreign key only references a unique value in a column, like a primary key or a unique column. The referenced value is optimally an integer, and primary keys tend to be integers since they are stored and joined more efficiently than strings.