SQL Question - Need to find the names and counts of a certain region

[shyy]

Hi everyone,

I have these tables but having trouble getting the SQL commands to work.

1. What are the names of customers in NorthEast? I tried connecting the 3 tables but not dice. The program I am using doesn't return the error in english but an error code so I have no way of knowing what I am doing wrong.
2. How many customers is their in NorthEast?

Thanks

The schema are:
CUSTOMER (Customer_ID,Customer Name, CustomerAddress, CustomerCity,CustomerState, Postal Code)
DOES_BUSINESS_IN_T ( Customer_Id, Territory_Id)
TERRITORY_T (Territory_ID, Territory_Name )

 

[shyy]

I tried this code, the answer didn't show up. If I did them separate they would show something.
I also noticed if I changed the first criteria and put SELECT Employee_ID
With the Employee_ID it does not show, but if I put Employee_Address it does show. Both are typed correctly.

SELECT Employee_Name
FROM EMPLOYEE_T e,
EMPLOYEE_SKILLS_T s,
SKILL_T t
WHERE e.Employee_ID = s.Employee_Id
AND s.Skill_Id = t.Skill_Id
AND t.Skill_Description = '12in Band Saw'
(SELECT Employee_ID
FROM EMPLOYEE_T e,
EMPLOYEE_SKILLS_T s,
SKILL_T t
WHERE e.Employee_ID - s.Employee_ID
AND s.Skill_Id = t.Skill_Id
AND t.Skill_Description <> '12in Band Saw')

 

[SydneyGeek]

Sounds like there might be some issues with the data?
What do you get from:

  SELECT Employee_ID,
         COUNT(*)
    FROM EMPLOYEE_SKILLS_T s,
         SKILL_T t
   WHERE s.Skill_Id = t.Skill_Id
     AND t.Skill_Description = '12in Band Saw'
GROUP BY Employee_ID
  HAVING COUNT(*) = 1

Denis

 

[shyy]

I get this:

[TABLE="width: 1010"]
[TR]
[TD][TABLE="width: 222"]
[TR]
[TH="class: tabResultSetxxctl0xwebGrid-hc"]

[/TH]
[TH="class: tabResultSetxxctl0xwebGrid-hc"]Employee_Id[/TH]
[TH="class: tabResultSetxxctl0xwebGrid-hc"]Count(*)[/TH]
[/TR]
[TR]
[TD="class: tabResultSetxxctl0xwebGrid-rlc, align: center"]

[/TD]
[TD="class: tabResultSetxxctl0xwebGrid-ic"]123-44-345[/TD]
[TD="class: tabResultSetxxctl0xwebGrid-ic, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: tabResultSetxxctl0xwebGrid-rlc, align: center"]

[/TD]
[TD="class: tabResultSetxxctl0xwebGrid-aic, bgcolor: #F5F5F5"]454-56-768[/TD]
[TD="class: tabResultSetxxctl0xwebGrid-aic, bgcolor: #F5F5F5, align: right"]1[/TD]
[/TR]
[/TABLE]
[/TD]
[/TR]
[/TABLE]


My data looks like this (for some odd reason MrExcelHTML is not working):

1. What are the names of employees who can only use 12in Band Saw?

Uploaded with ImageShack.us

 

[SydneyGeek]

Those are your employees with only Bandsaw as a skill. Join that to the Employees table to get the names.
(Hint: put that entire SQL in parentheses and alias it, then add the Employees table as another source)

Denis

 

[shyy]

I'm at a lost, I tried joining this but not sure how the syntax should look

SELECT Employee_Name
   FROM EMPLOYEE_T e, 
        EMPLOYEE_SKILLS_T s, 
        SKILL_T t 
  WHERE e.Employee_Name = s.Employee_Name 
      AND   t.Skill_Description = '12in Band Saw' 
      JOIN  EMPLOYEE_T e, 
               EMPLOYEE_SKILLS_T s, 
               SKILL_T t 
      WHERE e.Employee_Name = s.Employee_Name 
       AND t.Skill_Description <> '12in Band Saw'

 

[xenou]

You got nothing joining SKILL_T (aliased as t) to any other table. What are the primary keys and foreign keys in all these tables? There's about two other things wrong here so one at a time...

 

[SydneyGeek]

That last SQL you tried gives the IDs of all employees whose only skill is '12 in Bandsaw'.
You need to join that to EMPLOYEE_T to get the names. Something like:

  SELECT e.Employee_ID,
         e.Employee_Name
 FROM EMPLOYEE_T e,
 (SELECT Employee_ID,
         COUNT(*)
    FROM EMPLOYEE_SKILLS_T s,
         SKILL_T t
   WHERE s.Skill_Id = t.Skill_Id
     AND t.Skill_Description = '12in Band Saw'
GROUP BY Employee_ID
  HAVING COUNT(*) = 1) f
 WHERE e.EMPLOYEE_ID = f.EMPLOYEE_ID

Denis

 

[shyy]

Tried that but got an error




Uploaded with ImageShack.us

 

[xenou]

What are the names of employees who can only use 12in Band Saw?

I would use a slightly different approach (not necessarily a better one):

  SELECT 
    Employee_Id, 
    Count(Skill_Id) AS CountOfSkillIDs
  
  FROM 
    EMPLOYEE_SKILLS_T
  
  WHERE 
    Employee_Id IN 
        (SELECT e.Employee_Id FROM EMPLOYEE_SKILLS_T WHERE Skill_Id = 'BS12')
  
  GROUP BY 
    Employee_Id
  
  HAVING 
    Count(Skill_Id) = 1

Are you using MS Access as your database?

 

[shyy]

Thanks xenou for putting it in easier context.

Unfortunately received an error 3990.

I am using the SQL server in http://tunweb.teradata.ws/ to run the queries.