Conditions on outer joins

[TimSki]

Hi,

I have a simple query thus...

SELECT c.ID, count(s.id)
FROM t_customers c
LEFT OUTER JOIN t_sales s ON c.ID = s.id_t_customer s
GROUP BY s.id

As i would expect this returns me a list of ALL the customers i have ever had and shows the sales count against each one - even if it is 0.

But what if i don't want all the customers. if i do this...

SELECT c.ID, count(s.id)
FROM t_customers c
LEFT OUTER JOIN t_sales s ON c.ID = s.id_t_customer s
WHERE c.ID < 100
GROUP BY s.id

to try and get only the list of customrs with id < 100 it basically ignores it and gives me the full list of customers as in the first query - it sems the inner join
overrides this.

I still want t mintain the inner jooin but how can i limit the returnd records in the left hand table - ie t_customers ??

Thanks in advance

[OraMaster]

Hello,

Did your SQL query you posted execute correct?
Please always try to post your table structure, sample data and expected data while posting your questions. In your case below query may work.

SELECT c.ID, count(s.id)
FROM t_customers c
LEFT OUTER JOIN t_sales s ON c.ID = s.id_t_customer s
WHERE c.ID < 100
GROUP BY c.ID

Regards,
Bhushan

[TimSki]

aplogies for the typo. you are quite right. however, the problem remains...

[OraMaster]

Why dont you post some sample data and expected output. That would help anyone to reply your post.