So I am working with a few tables in a database, and I'm wondering the best way to query it.
所以我正在使用数据库中的几个表,我想知道查询它的最佳方法。
Here's the setup:
这是设置:
EVENTS:
int event_id
varchar event_name
date event_date
ATTENDANCE:
int attendance_id
int event_id (foreign key for EVENTS)
int user_id (foreign key for USERS)
int status
USERS:
int user_id
varchar first_name
varchar last_name
varchar email
Pretty much what I was going to do, is have an event (the ID #) that I want to get the attendance for, and then query the attendance table for all records matching that event, then query the users table for all users referenced in attendance as part of that event.
几乎我要做的是,有一个我想要出席的事件(ID#),然后在出勤表中查询与该事件匹配的所有记录,然后查询用户表中所有用户参考出席作为该活动的一部分。
The first thought that came to mind was to first query the database for all attendance entries and get an array, then loop through each record to query the user information. However this seems pretty inefficient and there must be a better way with joins or something of the like. I don't have much experience with joins, so I was wondering if I could get some help.
首先想到的是首先在数据库中查询所有出勤条目并获取数组,然后遍历每条记录以查询用户信息。然而,这似乎效率很低,并且必须有更好的连接方式或类似方法。我没有太多关于加入的经验,所以我想知道我是否能得到一些帮助。
This is the pseudo code of what I was originally thinking:
这是我最初想到的伪代码:
SELECT * FROM attendance WHERE event_id = eventID
while (row exists):
SELECT FROM users WHERE user_id = attendanceUserID
get info export in xml...etc.
I don't think this is the best way to do this, so what would be the better way to do it?
我不认为这是最好的方法,那么更好的方法是什么呢?
1 个解决方案
#1
0
The question is "join or loop?" and the technical answer is use a join. What you are describing is what joins are meant to do, combine tables on conditions.
问题是“加入还是循环?”技术答案是使用连接。您所描述的是联接的意图,在条件上组合表。
"select from ... (select more)" isn't the way to go about it. Consider the idea really of "what do these two tables have that connects them in a totally reliable and identifiable way? The note in the above comment is spot on.
“从...中选择(选择更多)”并不是解决问题的方法。考虑一下这个想法,“这两个表有什么能够以完全可靠和可识别的方式连接它们?上面评论中的注释是正确的。
However, as an "old man", the question isn't quite as straightforward. Everything is a question of time, yours and the machine's. So ask yourself this: imagine method A is 100 times more efficient than method B. But, you already know how to do method B. AND... the difference is 0.02 milliseconds versus 2 milliseconds. (Let's just say you have a small data set, and a fast machine.) If you can code up method B in three minutes and get on with your project, that might be good. Especially if there's a deadline. Because everything is easier with a working example to start from, even if implemented in a different way. It gives you something to test the NEW method that you're just learning against. Lots of people chase efficiency before they even know if it would even matter or not.
然而,作为一个“老人”,问题并不那么简单。一切都是时间问题,你的和机器的问题。所以问问自己:想象方法A的效率是方法B的100倍。但是,你已经知道如何做方法B.并且...差异是0.02毫秒而不是2毫秒。 (我们假设您有一个小型数据集和一台快速机器。)如果您可以在三分钟内编写方法B并继续您的项目,那可能会很好。特别是如果有一个截止日期。因为一个工作示例开始时一切都更容易,即使以不同的方式实现。它为您提供了一些测试您正在学习的新方法的东西。许多人甚至在知道它是否重要之前追逐效率。
Get things working first, make them faster second. (Of course, don't paint yourself into a horrible design corner. You haven't though, the database is fine, you're just looking at different ways of getting info out of it.)
让事情先行,让它们更快。 (当然,不要把自己描绘成一个可怕的设计角落。你还没有,数据库很好,你只是在寻找不同的方法来获取信息。)
#1
0
The question is "join or loop?" and the technical answer is use a join. What you are describing is what joins are meant to do, combine tables on conditions.
问题是“加入还是循环?”技术答案是使用连接。您所描述的是联接的意图,在条件上组合表。
"select from ... (select more)" isn't the way to go about it. Consider the idea really of "what do these two tables have that connects them in a totally reliable and identifiable way? The note in the above comment is spot on.
“从...中选择(选择更多)”并不是解决问题的方法。考虑一下这个想法,“这两个表有什么能够以完全可靠和可识别的方式连接它们?上面评论中的注释是正确的。
However, as an "old man", the question isn't quite as straightforward. Everything is a question of time, yours and the machine's. So ask yourself this: imagine method A is 100 times more efficient than method B. But, you already know how to do method B. AND... the difference is 0.02 milliseconds versus 2 milliseconds. (Let's just say you have a small data set, and a fast machine.) If you can code up method B in three minutes and get on with your project, that might be good. Especially if there's a deadline. Because everything is easier with a working example to start from, even if implemented in a different way. It gives you something to test the NEW method that you're just learning against. Lots of people chase efficiency before they even know if it would even matter or not.
然而,作为一个“老人”,问题并不那么简单。一切都是时间问题,你的和机器的问题。所以问问自己:想象方法A的效率是方法B的100倍。但是,你已经知道如何做方法B.并且...差异是0.02毫秒而不是2毫秒。 (我们假设您有一个小型数据集和一台快速机器。)如果您可以在三分钟内编写方法B并继续您的项目,那可能会很好。特别是如果有一个截止日期。因为一个工作示例开始时一切都更容易,即使以不同的方式实现。它为您提供了一些测试您正在学习的新方法的东西。许多人甚至在知道它是否重要之前追逐效率。
Get things working first, make them faster second. (Of course, don't paint yourself into a horrible design corner. You haven't though, the database is fine, you're just looking at different ways of getting info out of it.)
让事情先行,让它们更快。 (当然,不要把自己描绘成一个可怕的设计角落。你还没有,数据库很好,你只是在寻找不同的方法来获取信息。)