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

user_12314558 0
Viewed 37

Ive got two tables that look like this:

Customer Infomation:

enter image description here

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.

ms-access sql
2 Answers

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

user_101147370

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