I know I'm gonna get down votes, but I have to make sure if this is logical or not.
我知道我会得到投票,但我必须确定这是否合乎逻辑。
I have three tables A, B, C. B is a table used to make a many-many relationship between A and C. But the thing is that A and C are also related directly in a 1-many relationship
我有三个表A,B,C。B是一个用于在A和C之间建立多对多关系的表。但事实是A和C也直接在1-many关系中相关
A customer added the following requirement:
客户添加了以下要求:
Obtain the information from the Table B inner joining with A and C, and in the same query relate A and C in a one-many relationship
从表B内部加入A和C获取信息,并在同一查询中将A和C关联成一对多的关系
Something like:
alt text http://img247.imageshack.us/img247/7371/74492374sa4.png
替代文字http://img247.imageshack.us/img247/7371/74492374sa4.png
I tried doing the query but always got 0 rows back. The customer insists that I can accomplish the requirement, but I doubt it. Any comments?
我尝试了查询,但总是得到0行。客户坚持认为我可以完成要求,但我对此表示怀疑。任何意见?
PS. I didn't have a more descriptive title, any ideas?
PS。我没有更具描述性的标题,任何想法?
UPDATE: Thanks to rcar, In some cases this can be logical, in order to have a history of all the classes a student has taken (supposing the student can only take one class at a time)
更新:感谢rcar,在某些情况下,这可能是合乎逻辑的,以便记录学生已经学过的所有课程(假设学生一次只能上一节课)
UPDATE: There is a table for Contacts, a table with the Information of each Contact, and the Relationship table. To get the information of a Contact I have to make a 1:1 relationship with Information, and each contact can have like and an address book with; this is why the many-many relationship is implemented.
更新:有一个联系人表,一个包含每个联系人信息的表和关系表。要获取联系人的信息,我必须与信息保持1:1的关系,并且每个联系人都可以拥有相似的信息和地址簿;这就是实现许多关系的原因。
The full idea is to obtain the contact's name and his address book. Now that I got the customer's idea... I'm having trouble with the query, basically I am trying to use the query that jdecuyper wrote, but as he warns, I get no data back
完整的想法是获取联系人的姓名和他的地址簿。现在我得到了客户的想法......我遇到了查询问题,基本上我正在尝试使用jdecuyper编写的查询,但正如他警告的那样,我没有得到任何数据
6 个解决方案
#1
2
I'm supposing that s.id_class indicates the student's current class, as opposed to classes she has taken in the past.
我想s.id_class表示学生目前的班级,而不是她过去的班级。
The solution shown by rcar works, but it repeats the c1.className on every row.
rcar显示的解决方案可以工作,但它会在每一行重复c1.className。
Here's an alternative that doesn't repeat information and it uses one fewer join. You can use an expression to compare s.id_class to the current c.id_class matched via the mtm table.
这是一个不重复信息的替代方案,它使用少一个连接。您可以使用表达式将s.id_class与通过mtm表匹配的当前c.id_class进行比较。
SELECT s.name, c.className, (s.id_class = c.id_class) AS is_current
FROM s JOIN many_to_many AS mtm ON (s.id_student = mtm.id_student)
JOIN c ON (c.id_class = mtm.id_class);
So is_current will be 1 (true) on one row, and 0 (false) on all the other rows. Or you can output something more informative using a CASE construct:
因此,is_current在一行上为1(真),在所有其他行上为0(假)。或者您可以使用CASE构造输出更多信息:
SELECT s.name, c.className,
CASE WHEN s.id_class = c.id_class THEN 'current' ELSE 'past' END AS is_current
FROM s JOIN many_to_many AS mtm ON (s.id_student = mtm.id_student)
JOIN c ON (c.id_class = mtm.id_class);
#2
5
This is a doable scenario. You can join a table twice in a query, usually assigning it a different alias to keep things straight.
这是一个可行的方案。您可以在查询中将表连接两次,通常为其分配不同的别名以保持正确。
For example:
SELECT s.name AS "student name", c1.className AS "student class", c2.className as "class list"
FROM s
JOIN many_to_many mtm ON s.id_student = mtm.id_student
JOIN c c1 ON s.id_class = c1.id_class
JOIN c c2 ON mtm.id_class = c2.id_class
This will give you a list of all students' names and "hardcoded" classes with all their classes from the many_to_many table.
这将为您提供所有学生姓名和“hardcoded”类的列表,其中包含来自many_to_many表的所有类。
That said, this schema doesn't make logical sense. From what I can gather, you want students to be able to have multiple classes, so the many_to_many table should be where you'd want to find the classes associated with a student. If the id_class entries used in table s are distinct from those in many_to_many (e.g., if s.id_class refers to, say, homeroom class assignments that only appear in that table while many_to_many.id_class refers to classes for credit and excludes homeroom classes), you're going to be better off splitting c into two tables instead.
也就是说,这种架构没有逻辑意义。根据我的收集,您希望学生能够拥有多个班级,因此many_to_many表应该是您想要找到与学生相关的班级的地方。如果表s中使用的id_class条目与many_to_many中的id_class条目不同(例如,如果s.id_class指的是仅出现在该表中的homeroom类赋值,而many_to_many.id_class指的是用于信用的类并且不包括homeroom类),你最好把c分成两个表来代替。
If that's not the case, I have a hard time understanding why you'd want one class hardwired to the s table.
如果情况并非如此,我很难理解你为什么要将一个类硬连接到s表。
EDIT: Just saw your comment that this was a made-up schema to give an example. In other cases, this could be a sensible way to do things. For example, if you wanted to keep track of company locations, you might have a Company table, a Locations table, and a Countries table. The Company table might have a 1-many link to Countries where you would keep track of a company's headquarters country, but a many-to-many link through Locations where you keep track of every place the company has a store.
编辑:刚刚看到你的评论,这是一个虚构的架构举个例子。在其他情况下,这可能是一种明智的做事方式。例如,如果要跟踪公司位置,可能有Company表,Locations表和Countries表。公司表可能有1个链接到您可以跟踪公司总部国家/地区的国家/地区,但是通过位置的多对多链接可以跟踪公司拥有商店的每个地方。
If you can give real information as to what the schema really represents for your client, it might be easier for us to figure out whether it's logical in this case or not.
如果您可以提供有关架构为客户端实际代表什么的真实信息,那么我们可能更容易确定在这种情况下它是否合乎逻辑。
#3
3
Perhaps it's a lack of caffeine, but I can't conceive of a legitimate reason for wanting to do this. In the example you gave, you've got students, classes and a table which relates the two. If you think about what you want the query to do, in plain English, surely it has to be driven by either the student table or the class table. i.e.
也许这是缺乏咖啡因,但我无法想象想要这样做的正当理由。在你给出的例子中,你有学生,班级和一张与这两者相关的表格。如果你想用查询来做什么,用简单的英语,肯定它必须由学生表或类表驱动。即
- select all the classes which are attended by student 1245235
- select all the students which attend class 101
选择学生1245235参加的所有课程
选择所有参加101级课程的学生
Can you explain the requirement better? If not, tell your customer to suck it up. Having a relationship between Students and Classes directly (A and C), seems like pure madness, you've already got table B which does that...
你能更好地解释这个要求吗?如果没有,请告诉您的客户将其吸收。直接在学生和班级之间建立关系(A和C),看起来像纯粹的疯狂,你已经有了表B这样做......
#4
3
Bear in mind that the one-to-many relationship can be represented through the many-to-many, most simply by adding a field there to indicate the type of relationship. Then you could have one "current" record and any number of "history" ones.
请记住,一对多关系可以通过多对多关系来表示,最简单的方法是在那里添加一个字段来表示关系的类型。然后你可以有一个“当前”记录和任何数量的“历史”记录。
Was the customer "requirement" phrased as given, by the way? I think I'd be looking to redefine my relationship with them if so: they should be telling me "what" they want (ideally what, in business domain language, their problem is) and leaving the "how" to me. If they know exactly how the thing should be implemented, then I'd be inclined to open the source code in an editor and leave them to it!
顺便说一句,客户的“要求”是否被赋予了指示?如果是这样的话,我想我会重新定义与他们的关系:他们应该告诉我他们想要的“什么”(理想情况是,在商业领域的语言中,他们的问题是什么)并留下“如何”给我。如果他们确切地知道应该如何实现该东西,那么我倾向于在编辑器中打开源代码并留给它!
#5
1
It doesn't seem to make sense. A query like:
它似乎没有意义。像这样的查询:
SELECT * FROM relAC RAC
INNER JOIN tableA A ON A.id_class = RAC.id_class
INNER JOIN tableC C ON C.id_class = RAC.id_class
WHERE A.id_class = B.id_class
could generate a set of data but inconsistent. Or maybe we are missing some important part of the information about the content and the relationships of those 3 tables.
可以生成一组数据但不一致。或者也许我们错过了有关内容和这3个表的关系的信息的一些重要部分。
#6
0
I personally never heard a requirement from a customer that would sound like:
我个人从未听过客户的要求,听起来像是:
Obtain the information from the Table B inner joining with A and C, and in the same query relate A and C in a one-many relationship
从表B内部加入A和C获取信息,并在同一查询中将A和C关联成一对多的关系
It looks like that it is what you translated the requirement to. Could you specify the requirement in plain English, as what results your customer wants to get?
它看起来就像你将需求翻译成了。您能用简单的英语指定要求,因为您的客户希望得到什么结果?
#1
2
I'm supposing that s.id_class indicates the student's current class, as opposed to classes she has taken in the past.
我想s.id_class表示学生目前的班级,而不是她过去的班级。
The solution shown by rcar works, but it repeats the c1.className on every row.
rcar显示的解决方案可以工作,但它会在每一行重复c1.className。
Here's an alternative that doesn't repeat information and it uses one fewer join. You can use an expression to compare s.id_class to the current c.id_class matched via the mtm table.
这是一个不重复信息的替代方案,它使用少一个连接。您可以使用表达式将s.id_class与通过mtm表匹配的当前c.id_class进行比较。
SELECT s.name, c.className, (s.id_class = c.id_class) AS is_current
FROM s JOIN many_to_many AS mtm ON (s.id_student = mtm.id_student)
JOIN c ON (c.id_class = mtm.id_class);
So is_current will be 1 (true) on one row, and 0 (false) on all the other rows. Or you can output something more informative using a CASE construct:
因此,is_current在一行上为1(真),在所有其他行上为0(假)。或者您可以使用CASE构造输出更多信息:
SELECT s.name, c.className,
CASE WHEN s.id_class = c.id_class THEN 'current' ELSE 'past' END AS is_current
FROM s JOIN many_to_many AS mtm ON (s.id_student = mtm.id_student)
JOIN c ON (c.id_class = mtm.id_class);
#2
5
This is a doable scenario. You can join a table twice in a query, usually assigning it a different alias to keep things straight.
这是一个可行的方案。您可以在查询中将表连接两次,通常为其分配不同的别名以保持正确。
For example:
SELECT s.name AS "student name", c1.className AS "student class", c2.className as "class list"
FROM s
JOIN many_to_many mtm ON s.id_student = mtm.id_student
JOIN c c1 ON s.id_class = c1.id_class
JOIN c c2 ON mtm.id_class = c2.id_class
This will give you a list of all students' names and "hardcoded" classes with all their classes from the many_to_many table.
这将为您提供所有学生姓名和“hardcoded”类的列表,其中包含来自many_to_many表的所有类。
That said, this schema doesn't make logical sense. From what I can gather, you want students to be able to have multiple classes, so the many_to_many table should be where you'd want to find the classes associated with a student. If the id_class entries used in table s are distinct from those in many_to_many (e.g., if s.id_class refers to, say, homeroom class assignments that only appear in that table while many_to_many.id_class refers to classes for credit and excludes homeroom classes), you're going to be better off splitting c into two tables instead.
也就是说,这种架构没有逻辑意义。根据我的收集,您希望学生能够拥有多个班级,因此many_to_many表应该是您想要找到与学生相关的班级的地方。如果表s中使用的id_class条目与many_to_many中的id_class条目不同(例如,如果s.id_class指的是仅出现在该表中的homeroom类赋值,而many_to_many.id_class指的是用于信用的类并且不包括homeroom类),你最好把c分成两个表来代替。
If that's not the case, I have a hard time understanding why you'd want one class hardwired to the s table.
如果情况并非如此,我很难理解你为什么要将一个类硬连接到s表。
EDIT: Just saw your comment that this was a made-up schema to give an example. In other cases, this could be a sensible way to do things. For example, if you wanted to keep track of company locations, you might have a Company table, a Locations table, and a Countries table. The Company table might have a 1-many link to Countries where you would keep track of a company's headquarters country, but a many-to-many link through Locations where you keep track of every place the company has a store.
编辑:刚刚看到你的评论,这是一个虚构的架构举个例子。在其他情况下,这可能是一种明智的做事方式。例如,如果要跟踪公司位置,可能有Company表,Locations表和Countries表。公司表可能有1个链接到您可以跟踪公司总部国家/地区的国家/地区,但是通过位置的多对多链接可以跟踪公司拥有商店的每个地方。
If you can give real information as to what the schema really represents for your client, it might be easier for us to figure out whether it's logical in this case or not.
如果您可以提供有关架构为客户端实际代表什么的真实信息,那么我们可能更容易确定在这种情况下它是否合乎逻辑。
#3
3
Perhaps it's a lack of caffeine, but I can't conceive of a legitimate reason for wanting to do this. In the example you gave, you've got students, classes and a table which relates the two. If you think about what you want the query to do, in plain English, surely it has to be driven by either the student table or the class table. i.e.
也许这是缺乏咖啡因,但我无法想象想要这样做的正当理由。在你给出的例子中,你有学生,班级和一张与这两者相关的表格。如果你想用查询来做什么,用简单的英语,肯定它必须由学生表或类表驱动。即
- select all the classes which are attended by student 1245235
- select all the students which attend class 101
选择学生1245235参加的所有课程
选择所有参加101级课程的学生
Can you explain the requirement better? If not, tell your customer to suck it up. Having a relationship between Students and Classes directly (A and C), seems like pure madness, you've already got table B which does that...
你能更好地解释这个要求吗?如果没有,请告诉您的客户将其吸收。直接在学生和班级之间建立关系(A和C),看起来像纯粹的疯狂,你已经有了表B这样做......
#4
3
Bear in mind that the one-to-many relationship can be represented through the many-to-many, most simply by adding a field there to indicate the type of relationship. Then you could have one "current" record and any number of "history" ones.
请记住,一对多关系可以通过多对多关系来表示,最简单的方法是在那里添加一个字段来表示关系的类型。然后你可以有一个“当前”记录和任何数量的“历史”记录。
Was the customer "requirement" phrased as given, by the way? I think I'd be looking to redefine my relationship with them if so: they should be telling me "what" they want (ideally what, in business domain language, their problem is) and leaving the "how" to me. If they know exactly how the thing should be implemented, then I'd be inclined to open the source code in an editor and leave them to it!
顺便说一句,客户的“要求”是否被赋予了指示?如果是这样的话,我想我会重新定义与他们的关系:他们应该告诉我他们想要的“什么”(理想情况是,在商业领域的语言中,他们的问题是什么)并留下“如何”给我。如果他们确切地知道应该如何实现该东西,那么我倾向于在编辑器中打开源代码并留给它!
#5
1
It doesn't seem to make sense. A query like:
它似乎没有意义。像这样的查询:
SELECT * FROM relAC RAC
INNER JOIN tableA A ON A.id_class = RAC.id_class
INNER JOIN tableC C ON C.id_class = RAC.id_class
WHERE A.id_class = B.id_class
could generate a set of data but inconsistent. Or maybe we are missing some important part of the information about the content and the relationships of those 3 tables.
可以生成一组数据但不一致。或者也许我们错过了有关内容和这3个表的关系的信息的一些重要部分。
#6
0
I personally never heard a requirement from a customer that would sound like:
我个人从未听过客户的要求,听起来像是:
Obtain the information from the Table B inner joining with A and C, and in the same query relate A and C in a one-many relationship
从表B内部加入A和C获取信息,并在同一查询中将A和C关联成一对多的关系
It looks like that it is what you translated the requirement to. Could you specify the requirement in plain English, as what results your customer wants to get?
它看起来就像你将需求翻译成了。您能用简单的英语指定要求,因为您的客户希望得到什么结果?