Member Avatar for onsir

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
Thanks

Edited by masijade because: completed the first code tag
  • 2 Contributors
  • 1 Reply
  • 84 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by masijade
Member Avatar for 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 because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.