unable to use "IN" clause

Question

Hi All ,

Trying to get the same county from customer and supplier , i am getting error as invalid supplier name from below query

select * from customer
where country IN (select country from Suppliers)

in Customer table 'customer id ' is primary key
in supplier table ' supplier id 'is primary key

what am i missing please let me know

Answer 1

Seems like you have one database for every table. Somewhat unusual design.

Anyway, try:

   select * from Customer.dbo.customer  
   where country IN (select country from Supplies.dbo.Suppliers);  

But probably you should have one single database for your tables.

Answer 2

What is your database collation? Is it case sensitive?
Please write your code with consistence for everything you can, like table, column names with the same spelling cases.

Try this:
select * from customer c
where c.country IN (select s.country from suppliers s)

Answer 3

Hi @Praveena K ,

I think you need to group by, because you need unique values to use with the “IN”?

— Query result
SELECT *
FROM dbo.customer AS c
WHERE c.country IN
(
SELECT s.country
FROM dbo.Suppliers AS s
GROUP BY s.country
)

I hope it works!

Otherwise, check if both tables are in de same db and schema. What sort of db do you use?