Way to display data of non entry items

[College Kid]

Good Afternoon,

I have a table called BKS which stands for Book Sales. Each week each person that sold a book will be entered into the BKS database table i.e. BKS.ID, BKS.EMPLOYEE_ID, BKS.title, BKS.publisher, BKS.SALESAMT)

The insert works just fine because only those who sold books will be inserted into the BKS table. where i'm havng a problem is i wish to call up a list of employees from another table called EMPLOYEE who did not sale any books. My syntax look like this:

<cfoutput query name="NoSales" datasource"Book_Transaction">

SELECT BKS.ID, BKS.employee_id, BKS.title, BKS.publisher, BKS.SALESAMT

FROM BKS

WHERE BKS.employee_id <> #url.id#

AND BKS.SALESAMT = '0'

AND BKS.SALESAMT =''

order by BKS.Fname

</cfquery>

Though the employee who did not sale anything is not on the BKS table, i am struggling to find a way to list those from my EMPLOYEE table who did not do anything.

[-__cfSearching__-]

If you are not familiar with joins, you may want to read these tutorials first.

http://www.w3schools.com/SQl/sql_join_left.asp

http://www.w3schools.com/SQl/sql_join_left.asp

i am struggling to find a way to list those from

my EMPLOYEE table who did not do anything.

What you need here is a JOIN. Specifically an OUTER JOIN, which returns all of the records from one table even when there are no matching records in the other. In your case employees with no sales records.

ie    FROM    EMPLOYEE e LEFT JOIN BKS b ON e.employee_id = b.employee_id

When there is no matching sales record, the BKS employee_id will be NULL.  So to find only employees without sales, use something like:

      SELECT  e.employee_id, OtherColumns ....

      FROM    EMPLOYEE e LEFT JOIN BKS b ON e.employee_id = b.employee_id

      WHERE   b.employee_id IS NULL

SELECT BKS.ID, BKS.employee_id, BKS.Fname, BKS.lname,

One would assume you are already storing the employee's name in the EMPLOYEE table. So good database design dictates the name should not be stored in the BKS table too. As long as you are storing the employee id, you can retrieve the information at any time using a JOIN.

-Leigh

Message was edited by: -==cfSearching==-

[College Kid]

cfSearching

Thanks alot for the answer. I was racking my brain and did not think of the Outer "Left" Join. The code really helped!!!

[-__cfSearching__-]

You are welcome.  Outer JOIN's are very powerful. But are hard to wrap your brain around the first time through.

SELECT BKS.ID, BKS.employee_id, BKS.Fname, BKS.lname,

Regarding my comment about the table design, that table structure is okay for a class 😉 But in a real application, you would not want to design your tables that way.

[kinjago1]

I had some problems  in that part.

thanks alot for explaining it well in detail.

Now i know what to do.

Regards

Kinjago1