Question

i know there is heaps of this same question but im having trouble making it work on multiple table joins. i have 3 tables

用户

user_id | firstname | lastname

分数

subnumber | fkuserid

交易

transid | fksubnumber | expires | transdate

一、导言

SELECT *
FROM (SELECT *
FROM sub交易s
WHERE Expires < now()
ORDER BY NewSubTrans DESC) AS s
GROUP BY FKSubNum

What i am trying to do is select all users with a subscription that is expired. to determine if a user is expired i pull the last 交易 from the 交易 table with thier sub number, the fk fields are the links to the other tables.

Answer 1

从您的描述来看,我并不认为你需要任何群体。仅对这两者表示担忧:

SELECT user_id, firstname, lastname
FROM
 userinfo
  JOIN subnumbers ON userinfo.user_id = subnumbers.fkuserid
  JOIN transaction ON subnumbers.subnumber = transaction.fksubnumber
WHERE transaction.expires < NOW()

http://www.ohchr.org。如按下文的评论意见所示,使用<代码>DISTINCT在选定名单中填入多个行:

SELECT DISTINCT user_id, firstname, lastname
  -- etc...

Answer 2

这将使所有用户都受益。

select distinct(user_id) from userinfo
join subnumbers on userinfo.user_id = subnumbers.fkuserid
join transaction on transaction.fksubnumber = subnumbers.subnumber
where transaction.expires < NOW()

获取完整用户数据

select * from user where user_id in
(
select distinct(user_id) from userinfo
join subnumbers on userinfo.user_id = subnumbers.fkuserid
join transaction on transaction.fksubnumber = subnumbers.subnumber
where transaction.expires < NOW()
)

友情链接