JOIN problems 1

[SBonfiglio]

Hello to the community.
I have a problem with joins.
Being a novice in SQL I found joins very useful, so I started to use them everywhere in my application since I discovered that I have a problems with multiple joind - and in simple joins too - that failed.

I have found that if I try to joins two tables to make a list and in the joined table there are no records the "master" record is not listed.

Why is this ?
Thanks for any help.

Sergio

 

[r937]

try a LEFT OUTER JOIN instead



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[SBonfiglio]

I did, but it doesn't work.
I have a COUNT(*) into my query that when fails (it returns NULL) it also fails the query and the row is rejected.

the query ismore olr less like this:

select E_name, E_surname, (count(orders.ID)) AS HowManyOrders
FROM employees left outer join orders on orders.ID = employees.ID
Where employees.department is 2
order by E_surname,E_name

If there are no orders for a certain ID,
the right part of the query fails and I get not
the rows in which HowManyOrders should
return 0 or null.

Is there any possibility to avoid this ?

Thanks in advance for your help.

Sergio

 

[r937]

your query is fine

where are you running it?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[SBonfiglio]

MySQL 5.0

 

[r937]

my mistake, your query is ~not~ fine, you seem to have forgotten the GROUP BY clause, and it should be = 2, not IS 2

SELECT employees.E_surname
     , employees.E_name
     , COUNT(orders.ID) AS HowManyOrders
  FROM employees 
LEFT OUTER 
  JOIN orders 
    ON orders.ID = employees.ID
 WHERE employees.department = 2
GROUP
    BY employees.E_surname
     , employees.E_name

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[SBonfiglio]

I have forgotten the GROUP BY statement, but it has be done on the ID as the COUNT object, that is the orders table.

SELECT employees.E_surname
, employees.E_name
, COUNT(orders.ID) AS HowManyOrders
FROM employees
LEFT OUTER JOIN orders ON
orders.ID = employees.ID
WHERE employees.department = 2
GROUP BY employees.ID

However it doesn't work.
If the table "orders" doesn't contain any order for a certain employee, that employee will not be listed because the JOIN fails.

 

[r937]

If the table "orders" doesn't contain any order for a certain employee, that employee will not be listed because the JOIN fails.

sorry, this isn't true


the LEFT OUTER JOIN will return ~all~ employees, whether they have orders or not

a LEFT OUTER JOIN cannot "fail"

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[SBonfiglio]

I have found it.
I was gourping with the wrong table.
I was grouping on orders instead of employees as I wrote here. This was because by the original query has different tablenames.

Thanks however for your help.
It helped me however writing and analyzing the query with you.
Stay so well.