如何通过单个查询从两个表中填充两个对象? [英] How do I (SELECT) populate two objects from two tables with a single query?

问题描述

这应该是程序员经常遇到的东西，但是我从来没有尝试过用这种方式来获取东西.

This should be something encountered by programmers often, but I never tried to get things this way.

也就是说，我会解释.说，我需要像这样从表Zoo中获取值:

That is, I'll explain. Say, I need to fetch values from table Zoo like this:

 @"SELECT z.predator, z.prey FROM Zoo AS z WHERE z.preyType=@carnivore"

现在，我可以将所有值都添加到列表中.我需要在网格中显示该查询的详细信息.现在已经有了z.predator和z.prey值(它们是时间上的整数，即它们各自的ID)，我需要填充其有意义的值以将其显示给最终用户(我不能仅仅显示其ID).所以现在我可能会做这样的事情:

Now I can get all the values to a List. I need to display the details of that querying in a grid. Now having got z.predator and z.prey values (which are for time sake integers, ie, their respective ids), I need to populate its meaningful values for displaying it to end user (I can't just display their ids). So now I might do something like this:

 foreach (Zoo z in lstZoo)
 {
       Animal predator = GetFromAnimalTable(z.Predator)
       Animal prey = GetFromAnimalTable(z.Prey)
 }

这会使程序变慢.我可以一口气查询所有详细信息吗?像这样:

This can make the program slower. Can I query all the details in one go? Something like this:

   SELECT (SELECT * FROM ANIMAL WHERE id=z.predator), 
          (SELECT * FROM ANIMAL WHERE id=z.prey) 
   FROM Zoo AS z WHERE z.preyType=@carnivore

提供我可以将值读取到一个更大的新对象吗?还是这被认为是不好的做法?

Provided I can read the values to a new bigger object? Or is this considered a bad practice?

更新:这是一种标准做法吗?还是建议像我首先说的那样单独填充?

UPDATE: Is it a standard practice to do this? Or is it recommended to individually populate as I stated first?

更新2 :我似乎犯了一个严重的错误，即没有完全按照需要发布查询.我以为我可以从这里调整答案以满足我的要求，而通过Access查询的括号结构可以避免这种情况.

UPDATE 2: I seem to have made a terrible mistake of not posting the query exactly as I needed. I thought I could tweak the answers from here to meet my requirement, alas no with the parenthesis construction of Access queries.

这是我的实际查询结果:

Here is how my actual query would be:

SELECT z.predator, p.lifeSpan, z.prey 
FROM Zoo AS z 
INNER JOIN Plants AS p ON p.parent_id=z.id 
WHERE z.preyType=@carnivore

实际上，我已经对另一个表进行了INNER JOIN查询.现在，我需要获取(SELECT)满足INNER JOIN和WHERE条件的z.predator值(及其来自Animals表的相应值)，p.lifeSpan，z.prey(及其对应的Animal表的值).

Actually I had an INNER JOIN query already with another table. Now I need to get (SELECT) values of z.predator (and its corresponding values from Animals table), p.lifeSpan, z.prey (and its corresponding values from Animal table) meeting the INNER JOIN and WHERE condition.

伪代码如下所示:

SELECT (SELECT * FROM ANIMAL WHERE id=z.predator), p.lifeSpan, (SELECT * FROM ANIMAL WHERE id=z.prey) 
FROM Zoo AS z INNER JOIN Plants AS p ON p.parent_id=z.id 
WHERE z.preyType=@carnivore

从这里的答案中扩展我的要求应该很容易，但是到目前为止没有成功.我试过了:

It should be pretty easy to extend my requirement from the answers here, but no success till now. I tried:

SELECT a1.*, p.lifeSpan, a2.* 
FROM Zoo AS z 
INNER JOIN Plants AS p ON p.parent_id=z.id 
INNER JOIN Animal AS a1 ON (a1.id=z.predator)
INNER JOIN Animal AS a2 ON (a2.id=z.prey)
WHERE z.preyType=@carnivore

此版本的许多变体(带括号和不带括号).上面的查询如何正确构建?

And many variants of this with and without brackets. How can the above query be properly structured?

推荐答案

这似乎是您最近的查询尝试:

It seems this your latest query attempt:

SELECT a1.*, p.lifeSpan, a2.* 
FROM Zoo AS z 
INNER JOIN Plants AS p ON p.parent_id=z.id 
INNER JOIN Animal AS a1 ON (a1.id=z.predator)
INNER JOIN Animal AS a2 ON (a2.id=z.prey)";
WHERE z.preyType=@carnivore

从语句内部丢弃分号.也放弃双引号.

Discard the semicolon from inside the statement. Also discard the double quote.

只是为了简化SQL，现在不包括WHERE子句.

Just to simplify the SQL, exclude the WHERE clause for now.

然后，您应该可以更好地解决Access的数据库引擎多次连接所需的括号问题.

Then you should be in a better position to address the issue of parentheses which Access' db engine requires for multiple joins.

SELECT a1.*, p.lifeSpan, a2.* 
FROM
    ((Zoo AS z 
    INNER JOIN Plants AS p ON p.parent_id=z.id) 
    INNER JOIN Animal AS a1 ON a1.id=z.predator)
    INNER JOIN Animal AS a2 ON a2.id=z.prey

注意，我丢弃了那些用ON表达式括起来的括号.简单的ON表达式不需要它们.如果您有一个用于ON的复合表达式，则需要这样的括号:

Notice I discarded those parentheses which enclosed the ON expressions. Simple ON expressions don't require them. If you had a compound expression for ON, then you would need parentheses like this:

ON (p.parent_id=z.id AND p.foo = z.bar)

我建议的示例查询对我来说看起来是正确的. (如果它对您有用，请再次添加您的WHERE子句.)但是，由于我使用Access的查询设计器来设置联接，因此我并没有特别注意括号的位置...并且它添加了db引擎所需的括号. .

The sample query I suggested looks correct to me. (If it works for you, add your WHERE clause back again.) However, I don't pay close attention to parentheses placement because I use Access' query designer to set up joins ... and it adds the parentheses the db engine requires.

我敦促您也这样做.如果您使用的是来自Dot.Net的Access数据库，而没有安装Access副本，那么您确实应该获得一个副本.尝试在没有该数据库的本机开发工具的情况下使用数据库是一个不合理的挑战……有点像在戴手套时尝试键入.

I urge you to do the same. If you're using an Access db from Dot.Net without having a copy of Access installed, you really should get a copy. Trying to use a database without that database's native development tools is an unreasonable challenge ... somewhat like trying to type while wearing mittens.

这篇关于如何通过单个查询从两个表中填充两个对象?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！