CODEWITHSUNDEEP
mysql
Sabbir Hasan
Sabbir Hasan

Reputation: 23

Mysql query for two tables

Consider I have 2 tables. Items and bids.

id ranges from 1 to 8 and they are unique. In Category, it is 1 and 2 as example id: 1 to 4 is in category 1 and 5 to 8 is in category 2.

Now my query is, if I select a item from bids.item_id, I want to see what are id's in the same category.

As example: if I say in a query WHERE item_id FROM bids is '2', I want to see other id's of same category that means it should return '1' '3' '4', since 1 to 4 items.id is in category=1

Upvotes: 1

Views: 68

Answers (3)

Barmar
Barmar

Reputation: 781004

I don't see why you need to reference the bids table at all, since bids.item_id is the same as items.id. You just want all items that are in the same category as some other item. You can do this with a simple self-JOIN.

SELECT i1.id
FROM items AS i1
JOIN items AS i2 ON i1.category = i2.category
WHERE i2.id = 2

If you actually have a bid.id and you want to find all the items in the same category as the item in the bid, you just add another join:

SELECT i1.id
FROM items AS i1
JOIN items AS i2 ON i1.category = i2.category
JOIN bids AS b ON b.item_id = i2.id
WHERE b.id = :bidID

Upvotes: 2

Wim
Wim

Reputation: 547

In my opinion, there are two possible solutions:

1. your categories never change

In your question, you mention that there are only 2 possible categories: 1 and 2, and they are fully dependent on the item id. Will this always be like this? Then the answer is quite simple:

SELECT id FROM items WHERE items.category = {1 or 2 depending on which item was selected} AND items.id <> {id from selected item}

2. your categories might change one day in the future

SELECT id FROM items WHERE items.category = (SELECT items.category FROM items, bids WHERE items.id = bids.item_id)

Does this help?

Upvotes: 0

Astrogat
Astrogat

Reputation: 1625

select i.* from
items i where i.category = 
(select i2.category from items i2 join bids b on b.item_id = i2.id and b.id = :INPUTBIDID)

this will find all items that share a category with the item from the bid you select. It does this with a subselect. So it does the select in the paranthesis (which finds the correct category) and it uses that in the where clause of the outer query.

Upvotes: 1

Related Questions