SQL accessing child table data-CodePudding

I have a task in which I'm using Northwind database to select only these products which category name begins with 'C'. Product details are in table Products while CategoryName is in table Categories and this is the only answer I came up with

SELECT P.*
FROM Products P, Categories C
WHERE C.CategoryName LIKE 'C%' AND P.CategoryID = C.CategoryID

I was curious if I can do it without putting Categories inside FROM clause and connecting the tables. For me it seemed logical that I can access child table and use the values inside it smh, can you explain why it's not possible?

CodePudding user response：

You can use a Correlated Subquery to achieve this:

   SELECT *
   FROM Products p
   WHERE EXISTS (SELECT id FROM Categories c WHERE c.id = p.id)

This subquery is "Correlated" in that the SQL in the subquery references the query in which it's contained. Very similar to a join, but we are down in the WHERE clause using the EXISTS condition.

EXISTS is nice since it doesn't care what is returned by the subquery. It only cares that ANYTHING was returned by the subquery. So that subquery could also be: SELECT 1 FROM Categories WHERE c.id = p.id and this query would still work.

CodePudding user response：

You should be using JOINS not that type of syntax (INNER, LEFT, RIGHT, ...) But you can do this

SELECT P.*
FROM dbo.Products P WITH (NOLOCK) 
INNER JOIN dbo.Categories C WITH (NOLOCK) 
    ON P.CategoryID = C.CategoryID
WHERE 
    SUBSTRING(LTRIM(C.CategoryName), 1, 1) = 'C'

Join Products with Categories using an INNER JOIN which will only return the records that match. Add WITH (NOLOCK) when selecting from a transactional database so it does not lock users when attempting to run a transaction. I try to avoid LIKE when possible so in your scenario you can use substring to get the first character. The first argument is the expression, then start index and then how many characters you need to grab. LTRIM will remove white spaces from the left so even if your CategoryName accidentally has a leading whitespace or spaces it will remove them before running the substring. RTRIM would be unnecessary for what you need