How to bring only the records that have no association in the second table?

Asked

Viewed 4,533 times

1

Personal I have a query that returns the number of registered phones in each state,parents and also brings the number of customers who have no phone registered in each state,parents but I would like to bring only those customers who have no phone registered...a query is this:

SELECT s.cState,co.cCountry ,ISNULL(cTelephoneType,'NONE') AS 'Tipo Telefone', COUNT(*) AS 'TOTAL' FROM Customer c 
LEFT JOIN CustomerTelephone ct  on c.nIDCustomer = ct.nIDCustomer JOIN State s on
c.nIDState = s.nIDState JOIN Country co on co.nIDCountry = s.nIDCountry LEFT JOIN TelephoneType tt on tt.nIDTelephoneType = ct.nIDTelephoneType 
GROUP BY s.cState,co.cCountry,cTelephoneType

and the result is for now:

Florida United States   NONE    16
Nevada  United States   NONE    7
Nevada  United States   Cell Phone  7
Nevada  United States   Home    2
Nevada  United States   Work    1
Roma    Italia  NONE    96
Roma    Italia  Cell Phone  28
Roma    Italia  Home    4
Roma    Italia  Work    3
Rosário Argentina   NONE    7
São Paulo   Brazil  NONE    7
Yucatán Mexico  NONE    8

I wish only the 'NONE' records appeared'

1 answer

3


Something like that?

SELECT s.cState,co.cCountry, ISNULL(c.TelephoneType,'NONE') AS 'Tipo Telefone', 
    COUNT(*) AS 'TOTAL' 
FROM Customer c 
-- LEFT JOIN CustomerTelephone ct on c.nIDCustomer = ct.nIDCustomer 
JOIN State s on c.nIDState = s.nIDState 
JOIN Country co on co.nIDCountry = s.nIDCountry 
-- LEFT JOIN TelephoneType tt on tt.nIDTelephoneType = ct.nIDTelephoneType 
GROUP BY s.cState,co.cCountry,c.TelephoneType
WHERE c.TelephoneType is null;

EDIT

As per request by comment:

SELECT s.cState,co.cCountry, ISNULL(c.TelephoneType,'NONE') AS 'Tipo Telefone', 
    COUNT(*) AS 'TOTAL' 
FROM Customer c 
-- LEFT JOIN CustomerTelephone ct on c.nIDCustomer = ct.nIDCustomer 
JOIN State s on c.nIDState = s.nIDState 
JOIN Country co on co.nIDCountry = s.nIDCountry 
-- LEFT JOIN TelephoneType tt on tt.nIDTelephoneType = ct.nIDTelephoneType 
GROUP BY s.cState,co.cCountry,c.TelephoneType
WHERE c.TelephoneType is null;

UNION

SELECT s.cState,co.cCountry, c.TelephoneType AS 'Tipo Telefone', 
    COUNT(*) AS 'TOTAL' 
FROM Customer c 
INNER JOIN CustomerTelephone ct on c.nIDCustomer = ct.nIDCustomer 
INNER JOIN TelephoneType tt on tt.nIDTelephoneType = ct.nIDTelephoneType 
INNER JOIN State s on c.nIDState = s.nIDState 
INNER JOIN Country co on co.nIDCountry = s.nIDCountry 
GROUP BY s.cState, co.cCountry, c.TelephoneType
WHERE c.TelephoneType is not null;
  • That’s exactly what it’s worth... !

  • Gypsy now I need a query using UNION with this query that brings customers without a phone and with another that brings the number of registered phones per state, parents without using LEFT JOIN have any idea how ? the result would be the same from above...

  • @Daniel You talk, just unite the result?

  • is want to unite the result of the query that brings the states and parents with registered phone with the second query that you helped me...however I can not use LEFT JOIN...know how ?

  • @Daniel Editei the answer.

  • beauty vo forehead here

  • 1

    Thanks again Gypsy that’s right

  • the Gypsy in real I would need another way...I had to uncomment the LEFT JOINS there to bring the result I expected...I needed this result without using LEFT JOIN

  • @Daniel Why?

Show 5 more comments

Browser other questions tagged

You are not signed in. Login or sign up in order to post.