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

Question

Ive got two tables that look like this:

Customer 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.

Answer 1

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()

Answer 2

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