CODEWITHSUNDEEP
sqljoin
Arthur Pinhas
Arthur Pinhas

Reputation: 77

How to join two different selects from the same tables

I can't seem to manage to join both my selects that are coming from the same tables.

The query I got to so far is :

select 
    S.SaleID, C.CustomersID, S.HouseID, NumOfRooms, CuCity, SaleDate
from 
    sale S, Customers C, SaleToCustomers STC, House H 
where 
    S.SaleID = STC.SaleID 
    and C.CustomersID = STC.CustomersID 
    and S.HouseID = H.HouseID 
    and C.CuCity = 'Holon' 
    and year(SaleDate) <= 2016 
    and H.HouseID in (select H.HouseID AS NewHouseID, NumOfRooms AS NewNumOfRooms, C.CityName
                      from Sale S, House H, City C
                      where S.SaleDate is null 
                        and H.HouseID = S.HouseID 
                        and C.CityID = 4)

I expect to have both of the selects shown together, something like this maybe:

result

Only have it in one select rather than two.

Upvotes: 0

Views: 54

Answers (2)

ZorgoZ
ZorgoZ

Reputation: 3569

I don't know what you really expect this to return, but it will not work this way. These are not two selects, it is a select in select, which means that the result of the inner selection is used as a condition in the outer selects where clause. But the in syntax allows you only a single value set to be returned from the inner one (that means a single column, which can have values and nulls).

If you give a comprehenseve example of input data and expected output, the community might give you help you in finding a solution.

PS: your screenshot shows two result sets, which is not supported by all client data libraries. However, no single statement can return multiple result sets. You either need to run multiple statements (a script) or a stored procedure.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try using UNION ALL like below

select S.SaleID,C.CustomersID,S.HouseID,NumOfRooms,CuCity,SaleDate
    from sale S,Customers C,SaleToCustomers STC,House H 
    where S.SaleID = STC.SaleID and 
          C.CustomersID = STC.CustomersID and
          S.HouseID = H.HouseID and
          C.CuCity = 'Holon' and
     YEAR(SaleDate) <= 2016 
Union all
select H.HouseID AS NewHouseID,null,null,NumOfRooms AS NewNumOfRooms,C.CityName,null
     from Sale S,House H,City Cu
     where S.SaleDate is null and
     H.HouseID = S.HouseID and
     C.CityID = 4

Upvotes: 2

Related Questions