[FIXED] how to inner join tables based on order_id if they have the same number of rows

Issue

I have two tables in the same database, one called "products" and the other called "customers", I need to get the customer.order_id, customer.name, customer.order_total, products.type But the thing is that I need matching result only if there is one product for this customer, so if the customer have more than one product on the other table ignore it and skip to the next one.

I have the following SQL that inner joins exactly what I need,but I don't know how to filter the results based on the product count (i don't even want to display the customer if he got more than one product.

For Example

Customers Table         

order_id    name    order_total 
13445       John    650 
28837       Steve   300 
20039       Craig   200 
39487       Matt    475 


Products Table          

order_id    product_sku   product_price    product_type
13445       12345         650              Toys
28837       34434         175              Pool
28837       54453         125              Food
20039       43546         200              Toys
39487       34256         475              Food

What i need from this two tables is:

order_id    name    order_total   product_type
13445       John    650           Toys
20039       Craig   200           Toys
39487       Matt    475           Food

I've tried something like that, But it gets me all the result including customers with more than one product

SELECT customer.order_id, customer.name, customer.order_total, products.type
FROM customer
INNER JOIN products
ON customer.order_id=products.order_id
WHERE customer.order_total != 0
ORDER BY customer.order_id DESC

Please help, Thank you

Solution

Both should work:

select c.*,p.product_type from Customers as c, Products as p where c.order_id = p.order_id and c.order_id in  
(select order_id from Products group by(order_id) having count(order_id) = 1);




select c.*, p.product_type from Products as p , Customers as c where  c.order_id = p.order_id  group by(p.order_id) having count(p.order_id) = 1;

Answered By - Shobhit_Geek