CODEWITHSUNDEEP
sqlsql-servert-sqlinner-join
Esi
Esi

Reputation: 45

SQL Inner join using where and order by

I have to write a complex searching query with TSQL with many filters and paged them as well as sort them at the same query. I have 2 tables with one to one relations to each other and try to use inner join.

The first table called dbo.Products with following columns: Id, Name, Price, Model and color.

The second table is called dbo.Items with following columns: Id, products_Id, Tel, Area, Category and Date.

I need to select Id, Category,Date and Area from the Items table and Name, Price and Model from Products table. Here is my shot:

SELECT Id,Category,Date,Name,Price,Model
FROM dbo.Items
INNER JOIN dbo.Products ON dbo.Items.Id=dbo.Products.Id
WHERE Category=1 AND Name LIKE '%is%' AND Price<1000 AND Area=2
ORDER BY Date DESC
ORDER BY [Id] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

My first question is if the order is correct and the second question is there is better way to write it for better performance, something like using Stored Procedures?

Upvotes: 2

Views: 8831

Answers (2)

Dom DaFonte
Dom DaFonte

Reputation: 1779

I updated your query based on assumptions on your table structure. I left the dbo against the tables, though I don't typically query a table with dbo. it's usually owned by the schema in question.

  • Question 1: You can't run two order by clauses, you would need to comma delimit them by the priority of sorting.

  • Question 2: I rewrote the query below.

  • Your query also has DEC, which I assume you meant descending (DESC).

  • I also merged the two order by clauses into one with a comma ranking them.
  • I aliased the tables. This helps ease confusion on what tables you are using and makes it more readable.

  • I moved the where clause "where p.Name like '%is%' to the bottom of your where clauses. This should help speed your query up as you query against indexable and faster filters and reduces the rows that the like will run against. 

    SELECT i.Id,i.Category,i.Date,p.Name,p.Price,p.Model
    FROM dbo.Items i
        INNER JOIN dbo.Products p ON i.Id=p.Id
    WHERE i.Category=1 
        AND p.Price<1000 
        AND i.Area=2
        AND p.Name LIKE '%is%' 
    ORDER BY i.Datum DESC, [i.Id] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Upvotes: 0

pcofre
pcofre

Reputation: 4066

The right syntax would be:

SELECT I.Id,Category,Date,Name,Price,Model
FROM dbo.Items as I
INNER JOIN dbo.Products as P ON I.Id=P.Id
WHERE Category=1 AND Name LIKE '%is%' AND Price<1000 AND Area=2
ORDER BY Date DESC, I.[Id] OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Upvotes: 1

Related Questions