title: SQL combining two row results into one row
body: <p><a href="https://i.stack.imgur.com/ITEsT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ITEsT.png" alt="enter image description here"></a>My DDLs</p>
  
  <pre><code>create table Employee
  (employeeID varchar(10) not null primary key,
  fName varchar(20) not null,
  mName varchar(20) not null,
  lName varchar(20) not null,
  DOB date,
  position varchar(15)not null,
  addres varchar(40)not null,
  warehouseID varchar(10)foreign key references Warehouse(warehouseID),
  retailshopID varchar(10)foreign key references Retailshop(retailshopID),
  customerID varchar(10)foreign key references Customer(customerID),
  );
  
  create table Warehouse_Province
  (provinceID varchar(10) not null primary key,
  province varchar (25) not null,
  city varchar (25) not null,
  addresss varchar (40) not null,
  );
  
  
  create table Warehouse
  (warehouseID varchar(10) not null primary key,
  qtyofStocks int not null,
  reorderQty int not null,
  provinceID varchar(10)foreign key references     Warehouse_Province(provinceID)
  );
  
  create table Retailshop_Provice
  (retailprovinceID varchar(10) not null primary key,
  province varchar (25) not null,
  city varchar (25) not null,
  addresss varchar (40) not null,
  );
  
  create table Retailshop
  (retailshopID varchar(10) not null primary key,
  retailprovinceID varchar(10)foreign key references Retailshop_Provice(retailprovinceID));
  </code></pre>
  
  <p>I need to make a query to List all the employees that work in both retail shop and warehouses along with the city where they work.
  for this i made an SQL statement</p>
  
  <pre><code>SELECT  e.employeeID,e.fName, e.mName, e.lName,whp.city,rsp.city
  FROM Employee e,Warehouse w ,Warehouse_Province whp,Retailshop   r,Retailshop_Provice rsp 
  WHERE (e.warehouseID=w.warehouseID AND w.provinceID = whp.provinceID)OR(e.retailshopID=r.retailshopID AND r.retailprovinceID = rsp.retailprovinceID);
  </code></pre>
  
  <p>However the result i get has two rows for city,how could i combine them as the below sql result but i want it to show only one row for City without including it in two seperate SQL i want to combine it into one SQL with results showing employee names and the city they work at.</p>
  
  <pre><code>SELECT  e.employeeID,e.fName, e.mName, e.lName,whp.city
  FROM Employee e,Warehouse w ,Warehouse_Province whp 
  WHERE (e.warehouseID=w.warehouseID AND w.provinceID = whp.provinceID)
  
  SELECT e.employeeID, e.fName, e.mName, e.lName,rsp.city
  FROM Employee e,Retailshop r,Retailshop_Provice rsp 
  WHERE e.retailshopID=r.retailshopID  AND  r.retailprovinceID=rsp.retailprovinceID;
  </code></pre>
  
  <p>sample table should look like this</p>
  
  <pre><code>employee  fname lname  whpcity rspcity 
  
  a         mr    x      NULL  city2
  
  b         mr    y      city1   NULL
  
  a         mr    x      NULL  city1
  
  b         mr    y      city2   NULL
  </code></pre>