---
title: "Selecting one thing connected to several others in different MySQL table"  
description: "Selecting one thing connected to several others in different MySQL table"  
author: "Anonymous User"  
published: 2013-04-08  
updated: 2013-04-08  
canonical: https://www.mindstick.com/forum/728/selecting-one-thing-connected-to-several-others-in-different-mysql-table  
category: "mysql"  
tags: ["mysql"]  
reading_time: 2 minutes  

---

# Selecting one thing connected to several others in different MySQL table

Hi Everyone!\
Say I have an orders table consisting of id, customer_id, order_date and some other stuff. I also have an items table of things [people](https://www.mindstick.com/news/2295/more-people-need-to-monitor-this-crucial-metric-for-heart-health) can order consisting of item_id, item_name and price. And finally there is a table called c that connects order_id with item_id's.\
What I would like to do is to get some data from the orders table on all orders containing certain items that I [search](https://www.mindstick.com/articles/65368/best-smo-services-company-in-hyderabad-improve-search-rankings) for by name.\
I could do something like this, but that is obviously a bad solution even with just two items, not to mention five or so. But I think it illustrates what I [am looking](https://answers.mindstick.com/qa/115463/i-am-looking-for-to-joining-the-mindstick-internship-program-how-to-apply) for:\
SELECT o.id, customer_id, order_dateFROM orders AS o, c AS c1, items AS i1, c AS c2, item AS i2WHERE item_name = 'foo' AND c1.item_id = i1.id AND c1.order_id = o.idAND item_name = 'bar' AND c2.item_id = i2.id AND c2.order_id = o.idI have also seen a solution along the following lines, but it is somewhat slow too:\
SELECT o.idFROM orders as o[INNER JOIN](https://www.mindstick.com/forum/33572/sql-inner-join-keyword) cON o.id = c.order_idWHERE o.id IN (SELECT order_id FROM c WHERE item_id IN ( SELECT i.id FROM items as i WHERE item_name IN ( 'foo','bar' ) ))GROUP BY o.idHAVING COUNT(o.id) > 1Could someone suggest a [faster](https://yourviews.mindstick.com/story/1515/5-ways-to-get-in-shape-faster) working solution? I assume this type of query is quite [common](https://www.mindstick.com/articles/23170/10-most-common-accounting-mistakes-of-small-business), so I suppose there is at least one fast and clever solution. :)\
Edit: This [scenario](https://yourviews.mindstick.com/view/81221/sports-will-change-the-education-scenario) is a somewhat simplified [version](https://www.mindstick.com/articles/12845/things-to-remember-while-migrating-odoo-to-a-better-version) of a real situation. One thing to take into consideration is that item_names aren't necessarily unique, which means that any solution that employs HAVING COUNT(...) > 1 will [retrieve](https://www.mindstick.com/forum/34400/how-to-retrieve-form-values-in-controller-action) orders that contains 'foo' twice rather than at least one 'foo' and one 'bar'.\
Thanks in [Advance](https://www.mindstick.com/blog/33258/jee-mains-and-jee-advance-exams)!

## Replies

### Reply by AVADHESH PATEL

Hi Chintoo!\
try the help using joining as below

```
SELECT orders.*  FROM orders    JOIN c      ON orders.id = c.order_id    JOIN item      ON c.item_id = item.id  WHERE item_name IN ('foo', 'bar')  GROUP BY orders.id;
```

\
I hope it resolve your problem\


---

Original Source: https://www.mindstick.com/forum/728/selecting-one-thing-connected-to-several-others-in-different-mysql-table

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.