Implementing Combined Queries

Create two queries, one showing the total list of products’ SKU numbers and SKU descriptions, the second showing the list of SKU numbers, SKU desc, and products that have had a promotion (Promotion y/n). Then, apply a count on the SKU number column for each query. You see that while the first query produces a list of 211 records, the second produces a list of only 8 records.

This result occurs because the eFashion database only has information on product promotions where a promotion has actually occurred.

In this case, out of the 211 records available, there are only eight records where an entry identifies that a promotion has taken place. If you apply a query filter to display only those products where the Promotion y/n value is not equal to Yes, a message states that there is no data to return. The same message appears if you apply a query filter to display only those products where the Promotion y/n value is null.

Due to a relational database limitation, when products are listed within a Promotion column, products that have a Promotion y/n value show up in the report.

This is a common data integrity issue. Use a combined query to generate the results you need.

In the query filter scenario, you are unable to generate a table listing only the products for which there were no promotions, because the Promotion y/n object only contains data about the products for which there were promotions. However, you can generate the table you need by creating a combined query using the SKU number and SKU desc objects in Combined Query 1 and adding the Promotion (y/n) equal to y query filter to Combined Query 2.

This table compares the results generated by the two standard queries and the combined query:

When using combined queries, remember that you can change the default behavior of Web Intelligence so that the combined query returns only unique rows.

You can change the default behavior of Web Intelligence so that the combined query returns only unique rows.