找到你要的答案

Q:Return all items from MySQL table, but check if they are a favourite item based on second table

Q:返回从MySQL表所有项目的检查,但如果他们是一个基于二表最喜欢的项目

I have a table in MySQL called 'courses'. It has an ID field and a course name amongst other fields. I can return all courses with the following query

SELECT * FROM courses ORDER BY `name`

I then have a second table called 'favourites' which has two fields, courseid and userid. I am using it so users can favourite courses.

What I am trying to do is change my query so that it for each course aswell as returning the course name and other fields from the courses table, I would like it to return a boolean value of 0 or 1 if that course has been favourited by the user.

I know I have to pass the userID with the query but I am unsure how to construct the query.

Any help would be great.

I have a table in MySQL called 'courses'. It has an ID field and a course name amongst other fields. I can return all courses with the following query

SELECT * FROM courses ORDER BY `name`

然后我有了第二个表称为“热门”这两个领域,courseid和用户名。我使用它,所以用户可以喜欢的课程。

我所要做的是改变我的查询,每门课程以及返回课程名称和其他领域的课程表,我想如果这门课程已经被fave了由用户返回一个布尔值0或1。

我知道我必须与查询通过用户名,但我不知道如何构造查询。

任何帮助将是伟大的。

answer1: 回答1:

I have framed the query based on your description

a) For all the courses

SELECT c.*, IF(f.courseid IS NULL, '1', '0') FROM
courses c
LEFT JOIN favourites f ON(f.courseid = c.courseid)
GROUP BY c.courseid

b) For all courses against specific userid(here I used userid = 2)

SELECT c.*, IF(f.courseid IS NULL, '1', '0') FROM
courses c
LEFT JOIN favourites f ON(f.courseid = c.courseid)
WHERE f.userid = '2' 
GROUP BY c.courseid

根据你的描述,我已把这个问题编好了

为所有的课程

SELECT c.*, IF(f.courseid IS NULL, '1', '0') FROM
courses c
LEFT JOIN favourites f ON(f.courseid = c.courseid)
GROUP BY c.courseid

B)所有课程针对特定用户标识(这里我用UserID = 2)

SELECT c.*, IF(f.courseid IS NULL, '1', '0') FROM
courses c
LEFT JOIN favourites f ON(f.courseid = c.courseid)
WHERE f.userid = '2' 
GROUP BY c.courseid
answer2: 回答2:

If the favourites table can have at most one row per user, then this will do what you want:

SELECT c.*, (f.courseid IS NOT NULL) as FavouriteBy2Flag
FROM courses c LEFT JOIN
     favourites f
     ON f.courseid = c.courseid AND f.userid = 2 ;

如果收藏夹表最多可以有一行每用户,那么这将做你想要的:

SELECT c.*, (f.courseid IS NOT NULL) as FavouriteBy2Flag
FROM courses c LEFT JOIN
     favourites f
     ON f.courseid = c.courseid AND f.userid = 2 ;
mysql