SQL Subquery not working?

Question

Im using MS SQL and trying out SubQuery in Query Analyzer

Table: TInvoice has Cols: Invoice, Customer
Entries: Inv001, C001 | Inv002, C002
Table: TCustomer has Cols: Customer, Country
Entries: C001, Japan | Inv002, USA

My goal is to show all invoices of customers only in USA so i did the following

Select * from TInvoice where Customer in (Select Customer from TCustomer where Country='USA')

The output given was all the invoices.. Did i make any mistakes?

Answer 1

Looks right to me.



Try running just the subquery to see what it returns.

Answer 2

I use oracle so this may not be right for ms sql, but you're probably better off using a join:



Select Invoice from TInvoice t INNER JOIN TCustomer c on t.Customer = c.Customer

where c.Country = 'USA'



although your subquery looks fine and should have worked, at least in oracle.

Answer 3

Do you have to use a subquery? A Join would be easier, no?



Select *

from TInvoice i JOIN TCustomer c

ON i.Customer = c.Customer where C.Country = "USA"



but didn't see anything wrong with your code.