I want to get data sales item, its with compare table item with table sale

although item in table sale nothing, data will show
according with data item

i have SQL like this

select i.code,s.date_sale
from Item i 
left join(select * from Sale where date_sale <='2009-01-31') s
       on i.code=s.code;
table Item
Id         Code         Description
1          I001          TV  
2          I002          Radio 
3          I003          Ball
4          I004          Cycle

table Sale

Id_Sale      Code     date_sale      Qty    Amount
87             I001      2009-1-3        4     7000
88             I002      2009-1-4        5     4000

i want show like this

Code      date_sale      Qty    Amount
I001      2009-1-3        4     7000
I002      2009-1-4        5     4000
I003      null                null  null
I004      null                null  null

how convert to HQL

Edited by masijade: completed the first code tag

7 Years
Discussion Span
Last Post by masijade

Try "outer" joins

select i.code,s.date_sale
from Item i, Sale s
where i.code = s.code (+)
  and s.date_sale <='2009-01-31' (+)

Edit: Although I don't know HQL, at all, I am assuming it is one of the in Java written DBs and so should still understand basic SQL. I'll probably look into HQL, to find out what it really is, but I've had no reason to, yet.

Edited by masijade: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.