将三个查询的结果放入一个表中

Here is the situation,

这是情况,

I have an interface with a dropdown(All, Category, Category type, Product) and search button. There are three database tables for each section as CATEGORY, CATEGORYTYPE and PRODUCT When user select the All option search, I am getting results from all three tables and display on a HTML table.

我有一个带有下拉菜单(All,Category,Category type,Product)和搜索按钮的界面。每个部分有三个数据库表作为CATEGORY,CATEGORYTYPE和PRODUCT当用户选择All选项搜索时,我将从所有三个表中获得结果并显示在HTML表上。

TO do pagination, I want to fetch results set by set from all the tables.

要进行分页,我想从所有表中获取由set设置的结果。

Here are the three queries I'm using:

以下是我正在使用的三个查询:

SELECT CATNAME,CATDESC,CATSTATUS FROM CATEGORY WHERE CATNAME like %a%

SELECT CATTYPENAME,CATTYPEDESC,CATTYPESTATUS FROM CATEGORYTYPE WHERE CATTYPENAME like %a%

SELECT PRODUCTNAME,PRODUCTDESC,PRODUCTSTATUS FROM PRODUCT WHERE PRODUCTNAME like %a%

From all the tables I'm only fetching the same set of column I want to combine the three queries. I hope my question is understandable. This is not about joining the three tables.. I want the results from all three queries to act as a one set or records and paginate them.

从所有表中我只获取同一组列我想要组合三个查询。我希望我的问题是可以理解的。这不是关于加入三个表。我希望所有三个查询的结果充当一组或记录并对它们进行分页。

Thanks in advance for any suggestions and please ask if the question is not clear.

提前感谢任何建议,请询问问题是否不明确。

The application is JAVA EE based. Struts used and DB is MySQL.

该应用程序基于JAVA EE。使用的Struts和DB是MySQL。

1 个解决方案

#1

Using view u can do what you want. in very first u have to change columns heading as same. like

使用视图你可以做你想要的。首先,你必须改变标题相同的列。喜欢

SELECT NAME,DESCRIPTION,STATUS FROM CATEGORY WHERE NAME like %a%
SELECT NAME,DESCRIPTION,STATUS FROM CATEGORYTYPE WHERE NAME like %a%
SELECT NAME,DESCRIPTION,STATUS FROM PRODUCT WHERE NAME like %a%

then you can create view like this

然后你可以创建这样的视图

CREATE VIEW V AS
SELECT *
FROM ((CATEGORY NATURAL FULL OUTER JOIN CATEGORYTYPE)
    NATURAL FULL OUTER JOIN PRODUCT);

P.S. if you can not change columns heading,you can have to change view according to that tables' columns

附:如果您无法更改列标题,则必须根据表的列更改视图

#1