CODEWITHSUNDEEP
mysqlsqlsql-serverjoin
OBZ
OBZ

Reputation: 11

JOIN SQL Query by date

Thanks a lot...here is to my final question:

I have three tables with the following columns:

  1. Clients:

Client ID First name Last name

  1. Transactions:

Trans ID ClientID RepresentativeID OrderDate

  1. Representatives:

Representative ID First name Last name

I need to display all of the transactions information, with the representative name and client name that occurred in a specific date. Is this query right?

SELECT * 
FROM [Transactions], Clients.first name, Clients.last name,     Representatives.first name, Representatives. last name
 INNER JOIN [Clients]
 ON Transactions.ClientID= Clients.Client ID
 INNER JOIN [Representatives]
 ON Transactions.RepresntativeID = Representatives.Represntative ID
 WHERE Transactions.OrderDate BETWEEN '1996-09-18' AND '1996-11-27';

Is that right or did i get all wrong?

Upvotes: 0

Views: 78

Answers (2)

Kason
Kason

Reputation: 797

SELECT Clients.[first NAME] AS ClientFirstName
      ,Clients.[last NAME] AS ClientLastName
      ,Representatives.[first NAME] AS RepresentativesFirstName
      ,Representatives.[last NAME] AS RepresentativesLastName
      ,Transactions.*
FROM [Transactions]
INNER JOIN [Clients]
    ON Transactions.ClientID = Clients.Client ID
INNER JOIN [Representatives]
    ON Transactions.RepresntativeID = Representatives.Represntative ID
WHERE Transactions.OrderDate BETWEEN '1996-09-18'
        AND '1996-11-27';

What you expected outcome columns should put it to SELECT. From is your table or dataset.

Also, you can use alias to simplify the SQL like below.

SELECT c.[first NAME] AS ClientFirstName
      ,c.[last NAME] AS ClientLastName
      ,r.[first NAME] AS RepresentativesFirstName
      ,r.[last NAME] AS RepresentativesLastName
      ,t.*
FROM [Transactions] t
INNER JOIN [Clients] c
    ON t.ClientID = c.Client ID
INNER JOIN [Representatives] r
    ON t.RepresntativeID = r.Represntative ID
WHERE t.OrderDate BETWEEN '1996-09-18'
        AND '1996-11-27';

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

WHERE go after ON and before ORDER BY

also the ; go at the end not in the middle

SELECT * 
FROM [Orders] 
JOIN [Customers]
  ON Orders.CustomerID = Customers.CustomerID
WHERE Orders.OrderDate BETWEEN '1996-09-18' AND '1996-11-27'
ORDER BY Customers.CustomerName;

Upvotes: 3

Related Questions