Select from

[rzff]

Can anybody help me out with this query finaly I want to see all bookings between US clients and non US stockexchanges. But first i want to see all information in one report to be sure everything is correct.

I have a tables one is named bookings

NR ID Quantity SEC EXCHANGE
1001 1 1000 abc 5
1002 2 2000 def 6
1003 3 3000 ghj 7
1004 4 4000 klm 8

and a table names

ID Type country
1 Client US
2 Client US
3 Client US
4 Client CAD
5 EXCHANGE SP
6 EXCHANGE SP
7 EXCHANGE CAD
8 EXCHANGE US

I want to create a query where I can see also the countrycode I expect something like:

NR ID CntrQuantity SEC EXCHANGE CNTR
1001 1 US 1000 abc 5 SP
1002 2 US 2000 def 6 SP
1003 3 US 3000 ghj 7 CAD
1004 4 US 4000 klm 8 US

 

[swampBoogie]

select b.nr, b.id, c.country as ccntr, b.quantity,
 b.sec, b.exchange, e.country as ecntr
  from bookings as b,
       names as c,
       names as e
 where b.id = c.id
   and b.exchange = e.id

1004 4 US 4000 klm 8 US

does fit with the sample, ought to be

1004 4 CAD 4000 klm 8 US

or?

 

[rzff]

Hi Swampboogie,

Thanks so much I works. Do you also know if it is possible to get only those bookings between US clients and non US exchanges?

regards

rzff

 

[swampBoogie]

select b.nr, b.id, c.country as ccntr, b.quantity,
 b.sec, b.exchange, e.country as ecntr
  from bookings as b,
       names as c,
       names as e
 where b.id = c.id
   and b.exchange = e.id
   and c.country = 'US'
   and e.country <> 'US'

 

[rzff]

It works perfectly.

Thank you very much for helping me.

Regards
rzff