Hi,I am fairly new at SQL and I have been struggling for days now trying to find an answer to my problem and i have come to the point where i have run out of ideas and about to give up. I'm hoping someone can put me in the correct path. The problem
I have 3 table
Table 1 Department" has the following columns: REF, NAME
Table 2 "Department_Collection" has the following columns: REF, DEPARTMENT_REF, MANAGER_REF, STORE_REF, ACTIVE
Table 3 Store" has the following columns: REF, NAME, STORE_ID
What i am trying to do is to take all the rows in the Department table and get a matching row (DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF) from the Department_Collection table, if it does not match any then still display DEPARTMENT.NAME but mark DEPARTMENT_COLLECTION.REF as null.
I have tried the following select statement but it seem to remove all null values when supplied with a 'storename'
SELECT DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF FROM DEPARTMENT_COLLECTION
right outer join DEPARTMENT on DEPARTMENT_COLLECTION.DEPARTMENT_REF = DEPARTMENT.REF
left outer join STORE on DEPARTMENT_COLLECTION.STORE_REF = STORE.REF
where STORE.NAME = 'storename'
order by DEPARTMENT.NAME
Any help will be greatly appreciated. Thanks
View Complete Post