CODEWITHSUNDEEP
mysql
Jonnny
Jonnny

Reputation: 5039

Mysql: Use part of returned row of data to get more data

I'm passing an id of an item to my model and I want to use this item id in a query to find it's area_id and from there return any other items that have the same area_id. I'm not sure how do that though, I looked at sub queries but it didn't seem right to me. This is my structure.

Item:

ID -- Title -- Area_id(FK) -- Description -- Date_added

Area:

ID -- Place -- Date_Added

Thanks for any help

Jonny

Upvotes: 0

Views: 40

Answers (2)

David Marx
David Marx

Reputation: 8558

Try this:

SELECT i2.*
FROM item i1, item i2
WHERE i1.id = :input_item_id
AND i1.area_id = i2.area_id

This is what's called a "self-join" and should be much more efficient than a solution using subqueries.

Upvotes: 0

Atrakeur
Atrakeur

Reputation: 4234

Firstly, select the are of you item:

SELECT Area_id FROM Item i WHERE ...

So you get an area ID

Now whe have to get all item in a given area:

SELECT * From Item WHERE Area_id IN (...)

Get the two queries together:

SELECT * From Item WHERE Area_id IN (SELECT Area_id FROM Item i WHERE ...)

That's done!

Please note that subqueries arn't good for performance, so I recommend you to benchmark and keep an eye on this query as you database grow, then optimize it as you experience performance problems.

Upvotes: 1

Related Questions