I have a select statement that is joining to tables.
In the ConsolidatedBOLReports Table there is 1 record per ConsolidatedProNumber. In the conbol table there is multiple records per consolidatedProNumber.
I am using this query to show the report from the consolidatedbolreports table but it is returning 8 rows when I only want it to return 1. I know it is returning multiple rows because the join is causing it to return a row for each record in the joining table which in this case is conbol. How can I get it to only return 1 row?
select r.id,r.consolidatedpronumber,r.PlantId,r.carrier, c.tocompany as ShipToCompany, c.toaddr1 as ShipToAdd1,
c.tostate as ShipToState,c.tocity as ShipToCity,c.tozip as ShipToZipCode,c.conbolid as ConBol
from consolidatedbolreports r join conbol c
on r.consolidatedpronumber = c.conproid and r.Carrier=c.Carrier
where consolidatedpronumber in (select conproid from conbol )
and r.consolidatedpronumber='123456' and r.PlantId='PLANT001' and r.carrier='Conway'