CODEWITHSUNDEEP
mysqlsubquery
GossetStudent
GossetStudent

Reputation: 47

Using subquery in MySQL

I need some help with a specific problem about subqueries in MySQL. This is a shortversion of the problem I have:

I have table 1 and 2 where table 1 have a column called "SupplyID" which corresponds with table 2's "ID". I need to get a list with stuff from one single supplier.

I tried with this but didn't work:

select name
from item
where SupplyID exists (
   select *
   from SupplyID
   where SupplyID = ID
);

Upvotes: 0

Views: 56

Answers (3)

ICE
ICE

Reputation: 1737

My answer is more like to what scaisEdge answered here but I strongly recommend to do this with LEFT JOIN. and If you want to select just one ID, for example ID=10

SELECT T1.name
FROM item AS T1
LEFT JOIN SupplyID AS T2 ON T2.SupplyID=T1.ID
WHERE T1.ID = 10

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

Try this:

select name from item i where exists (select * from table2 t where i.SupplyID = t.ID);

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

Assuming your tables are named table1 and table2 you You could use a inner join 

select distinct t1.name
from ybale  as t1 
inner join table2 as t2 on t1.ID = t2.SupplyID

Upvotes: 2

Related Questions