Share via

unable to use "IN" clause

Praveena K 21 Reputation points
2022-12-10T16:05:30.08+00:00

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

SQL Server | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

Answer accepted by question author

Erland Sommarskog 134.4K Reputation points MVP Volunteer Moderator
2022-12-10T17:57:06.53+00:00

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.

Was this answer helpful?

1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Resh1309 1 Reputation point
    2022-12-12T01:32:54.02+00:00

    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?

    Was this answer helpful?

  2. Jingyang Li 5,901 Reputation points Volunteer Moderator
    2022-12-10T17:10:29.967+00:00

    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)

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.