Results 1 to 2 of 2
  1. #1
    Dcalladi is offline Member
    Join Date
    Oct 2011
    Posts
    27
    Rep Power
    0

    Default MySQL Query Question

    i have three tables

    Employee
    empId int pk
    empname varchar

    customersite
    empid int pk fk
    custid int pk fk
    startdate date pk
    enddate date

    employeeassignment
    custid int pk
    sitename varchar

    my question is

    How to show the name of all our employees with the customer name of those who are on customer sites today. For those who are not, show 'N/A' for the name and address.

    i have so far

    SELECT
    e.EmpName,
    cs.SiteName
    FROM
    EmployeeAssignment ea
    INNER JOIN
    Employee e
    ON
    e.EmpId = ea.EmpId
    INNER JOIN
    CustomerSite cs
    ON
    cs.CustId = ea.CustId

    which give me all employee at work today, with their name and the site.

    any help you be very appreciated thanks

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,014
    Rep Power
    20

    Default Re: MySQL Query Question

    Your query and table definitions don't match.
    EmployeeAssignment does not have an empId.

    Anyway, assuming that's just a typo, you want to use outer joins.
    Inner join the EA and CS tables together, then OUTER JOIN the employee table.
    You'll then need to dig into the MySQL docs to find how you can do DECODE or CASE statements to turn the NULLs into 'N/A'.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

Similar Threads

  1. Eclipse MySQL query
    By kenmat514 in forum Eclipse
    Replies: 2
    Last Post: 06-23-2011, 10:39 PM
  2. MySQl query taking too much time
    By jessie in forum JDBC
    Replies: 16
    Last Post: 11-11-2010, 01:09 PM
  3. Query MySQL
    By boss in forum Advanced Java
    Replies: 3
    Last Post: 01-04-2010, 09:36 AM
  4. Query MySQL
    By boss in forum Advanced Java
    Replies: 3
    Last Post: 01-04-2010, 09:35 AM
  5. MySQL/JDBC Mysql query output
    By thelinuxguy in forum Advanced Java
    Replies: 4
    Last Post: 02-13-2009, 01:57 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •