Write SQL database queries - tips

Tips for writing SQL database queries in Productsup.

This document will provide some tips about writing Structured Query Language (SQL) queries to databases. This can help you access product data from databases. It should in particular be useful for the data sources, see Import from a database.

In the following example, there are two tables that contain different data relating to some products.

Table 5. “product_data”

id

product_id

title

type

1

222

book

direct

2

333

table

dropship

3

444

lamp

dropship



Table 6. “product_pricing”

id

product_id

price

1

222

9.99

2

333

69.99

3

444

49.99



Merge the tables and get our product data

You can merge the previous tables together and then extract Productsup product data. To do so, you can write a query.

Selecting our product data: the select command

To select our data, we should use the select command, followed by a list of attribute names. This defines which attributes should be part of the result.

We also need to define which table we receive this data from, using them on command.

SELECT product_data.product_id,
       product_data.title
FROM product_data

In the above snippet, we are selecting the product_id and title columns from the product_data table.

Merging two tables: the join command

If we want to also add in more information (merge) for our products coming from another table, we need to use the join command.

We use the on command to specify what the id column in each table is. The query then looks for matches in the ids between the tables. The data in the first table is extended with data from the second if there is a match.

JOIN product_pricing ON product_data.product_id = product_pricing.product_id

In the above snippet, we are merging the product_pricing to the product_data table. Here the on example is simple, as the id column in both tables is product_id.

Adding conditions when selecting data: the where command

We can also specify conditions that have to be fulfilled, in order for our data to be exported.

WHERE product_data.type = 'dropship'

In the above snippet, we only want to extract products where the type from the product_data column is equal to dropship.

Our final query

Each SQL query should include at least a select and a from. The join and where commands are to be used if you need them.

If we create a full query using all we’ve learned, we may come out with an example such as the below:

SELECT product_data.product_id,
       product_data.title,
       product_pricing.price
FROM product_data
JOIN product_pricing ON product_data.product_id = product_pricing.product_id
WHERE product_data.type = 'dropship'

This would mean that we receive the following extracted data:

product_id

title

price

333

table

69.99

444

lamp

49.99

Learn more about SQL

The query explained here is one example. To learn more about SQL, see W3 Schools SQL Tutorial.

Have questions? Contact support@productsup.com.