CODEWITHSUNDEEP
mysqlperformancenested
aamirl
aamirl

Reputation: 1550

Properly Nest SQL Queries

So I have been coding for a few months now, and I think I am getting the hang of working with MySQL. I can CRUD fairly well now. However, there is one pretty fundamental concept that's eluding me and I can't seem to figure out the best solution. The concept is basically centered around what the best practice is when one has to grab data from different tables - when not all data has joins on other related tables.

For example, let's say my task is to get all products and images from two separate tables, one called products, and the other called images. While all images have an associated product, not all products have images.

I started out writing my query:

SELECT product_name, productId FROM products

To first grab the pertinent product information. This is where I am stumped. What would be the best way to get the images for the products that actually have images? I cannot just join the image table as such:

SELECT product_name, image FROM products, images 
       WHERE images.image_product_Id = products.productId

because that would not pull back any product that does not have a join on the image table.

Right now, my solution is to run the first query, pull back the results, and then while I iterate through the product data row by row, I run an image subquery that reads:

SELECT image FROM images WHERE images.image_product_Id = '$productId'

where the variable $productId is that row's particular productId. If the query gives me results, I add the image data to the row.

This to me feels like terrible programming practice. Intuitively, I think that it can and should all be done in one query - but I cannot seem to figure out what that query would be.

I hope I was clear in how I worded this - and yes, I realize that to many this will be a very basic question. Could someone point me in the right direction? Thank you very much in advance!

Upvotes: 0

Views: 192

Answers (1)

Barranka
Barranka

Reputation: 21047

There are two ways to acomplish what you need: The first is what you've already done. If you think that is inneficient, consider using joins:

  • table1 INNER JOIN table2 ON ... will return the rows of both tables that match each other (the issue you are facing with your where)
  • table1 LEFT JOIN table2 ON ... will return all the rows from table1, and the matching rows from table2, and if there are not matching rows in the second table, the columns will have NULL values
  • table2 RIGHT JOIN table2 ON ... well... it's the same as above, but backwards

So, in your example, I would write a query like this:

select product_name, image
from products left join images on products.productId = images.image_product_id;

This query will return all the products and all the matching images, but if there's no image associated with a given product, the column value will be NULL for that product.

Try it, it may help you.

Upvotes: 1

Related Questions