Android的SQLite的选择表中的一行，其中预选赛在其他表。 [英] Android SQLite select one row from table where qualifier is in other table.

问题描述

大家试图建立一个更好的SQLite的SELECT查询。我已经通过在interweb SO等景点看了，但还没有解决这个问题运气好的话还没有，所以这里有云：

Hi all trying to put together a better SQLite SELECT query. I've looked through SO and other spots on the interweb but haven't had any luck with this problem yet, so here goes:

我在SQLite的两个表：tblMessage和tblCategory。用户可以禁用单独的消息，或禁用整个类别的消息。

I have two tables in SQLite: tblMessage and tblCategory. Users can disable individual messages, or disable whole categories of messages.

tblMessage看起来像这样（简体）：

tblMessage looks something like this (simplified):

INT _id，INT的categoryId，TEXT MessageText中，布尔messageHidden

INT _id, INT categoryId, TEXT messageText, BOOL messageHidden

tblCategory看起来像这样（简体）：

tblCategory looks something like this (simplified):

INT_id，TEXT类别名称，BOOL categoryHidden

INT_id, TEXT categoryName, BOOL categoryHidden

由于它现在代表，我捡的消息从第一表...

As it stands right now, I am picking a message from the first table with...

SELECT * FROM tblMessage ORDER BY RANDOM（）LIMIT 1 WHERE msgHidden =F

SELECT * FROM tblMessage ORDER by RANDOM() LIMIT 1 WHERE msgHidden = "F"

...然后打开另一个光标tblCategory抢匹配的categoryId的信息。

... and then opening another cursor to tblCategory to grab the info of the matching categoryId.

SELECT * FROM tblCategory WHERE _id =（从的categoryID先选择）

SELECT * FROM tblCategory WHERE _id = (categoryID from first select)

我然后进行检查categoryHidden为真（在code），在这种情况下，我回去再做整个消息的选择，直到我得到一个不是来自一个隐藏的类。

I then proceed to check if the categoryHidden is true (in code), in which case I go back and do the whole message selection again until I get one that is not from a hidden category.

这一切工作正常，但我怀疑，我可以消除在初始消息SELECT语句从这些隐藏类别的结果，并在code比较免除。我期待为连接和工会，但我是相当新的SQL，并没有发现很我后。

This all works properly, however I suspect I can eliminate results from these hidden categories in the initial message SELECT statement and dispense with the comparison in code. I'm looking into JOINS and UNIONS but I'm fairly new to SQL and haven't found quite what I'm after.

这是很简单的东西吧？

在此先感谢！

推荐答案

您可以做到在一个查询

SELECT tblMessage._id, tblMessage.categoryId, tblMessage.messageText,  
       tblMessage.messageHidden FROM tblMessage, tblCategory   
       WHERE tblMessage.categoryId = tblCategory._id AND tblMessage.messageHidden="F"
       AND tblCategory.categoryHidden = "F" ORDER by RANDOM() LIMIT 1;

这篇关于Android的SQLite的选择表中的一行，其中预选赛在其他表。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！