如何为该多对多表建立SQL查询? [英] How do I establish a SQL query for this many-to-many table?

问题描述

我正在使用PHP/MySQL在此站点中构建此以物易物类型的函数，并且我试图创建一个查询并使用以下字段进行响应: 所有者的用户名，标题，要约人的用户名，要约人的项目

I'm building this bartering-type function in this site using PHP/MySQL and I'm trying to create a query that responds with the following fields: owner's username, title, offerer's username, offerer's item

基本上，我在这里有三个带有以下字段的表:

Basically I have three tables here with the following fields:

用户

user_id, username, email 

items_available

item_number, owner_id (foreign key that links to user_id), title

offers_open

trade_id, offerers_id (fk of user_id), offerers_item(fk with item_number), receivers_id (fk from user_id)

我该怎么做?我已经看到了对多对多SQL查询的一些引用，但是它们似乎并不特别适合我要查找的内容(例如，offerers_id和owner_ids在Users表中引用了不同的users_id，但该怎么做)我在sql查询中使它们与众不同吗?)

How do I do this? I've seen some references to many-to-many SQL queries but they don't seem to particularly fit what I'm looking for (for example, the offerers_id and the owner_ids refer to different users_id in the Users table, but how do I make them distinguishable in the sql query?)

推荐答案

如果我正确理解，这就是您要寻找的东西:

If I understand correctly, this is what you are looking for:

SELECT owner.username, oferrers.username, ia.title
FROM offers_open o
  INNER JOIN users AS offerers
    ON o.offerers_id = offerers.user_id
  INNER JOIN items_available AS ia
    ON o.offerers_item= ia.item_number
  INNER JOIN users AS owner
    ON ia.owner_id = owner.user_id

我没有在users表格上看到标题，因此没有包含标题.

I don't see a title on the users table, so didn't include one.

这篇关于如何为该多对多表建立SQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！