How to filter a table by a value in another table with sql?-CodePudding

Ive got two tables that look like this:

Customer Infomation:

Rent Infomation:

Rent Infomation

I want to show all the details of the customer table, but only display the customers that are currently renting. Ive tried but haven't had any success with the sql.

CodePudding user response：

There are multiple ways to achieve this

select c.* from customer_information c inner join rent_information r on r.customerId = c.customerId and r.endDate > now();

If you want only unique results then use this

select c.* from customer_information c inner join rent_information r on r.customerId = c.customerId and r.endDate > now() group by c.customerId;

If you want to run these queries on a large dataset, you might wanna see the use of subqueries

CodePudding user response：

I think you may be looking at possibly just a simple inner join and Where query, that is if you are able to use SQL/T-SQL.

SELECT c.CustomerID, c.CustomerName 
FROM CustomerInformation c 
INNER JOIN RentInformation r ON r.CustomerID = c.CustomerID
WHERE r.endDate > GetDate()