Geo Geo - 5 months ago 15
SQL Question

Inner join returns too many rows

I a having an issue joining two tables in order to return just one column from the other.

SELECT om.*, cm.Sales_Stage
FROM dbo.OM_Table1 om
JOIN dbo.Criteria_Matters cm ON cm.clientCorporationID = om.ClientCorporationID
ORDER BY om.ClientCorporationID


I want to include the Sales_Stage from my CM table but the join causes the result set to return 14k+ rows instead of the ~7k that is returned without the join.

Is there anyway to just bring in this additional column without blowing up the query?

Answer

You can use a subquery... note that this may not correctly select the sales_stage you want as you have multiple entries in Criteria_Matters per ClientCorporationID. You probably need an order by on the subquery.

SELECT om.*, 
    (SELECT TOP 1 cm.Sales_Stage 
    FROM dbo.Criteria_Matters cm
    WHERE cm.clientCorporationID = om.ClientCorporationID)  AS Sales_Stage
ORDER BY om.ClientCorporationID

... I'm assuming the om.* was just for the example. it is typically best practice to NOT do that in production.

If you intended to see the differences you may want to do something like this instead...

SELECT om.*, cm.Sales_Stage, cm.Criteria_MatterID
FROM dbo.OM_Table1 om 
JOIN dbo.Criteria_Matters cm ON cm.clientCorporationID = om.ClientCorporationID
ORDER BY om.ClientCorporationID