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

Question

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

Navigation

#1 by (3 votes)

1

I have a query that returns the number of phones registered in each state, country and also brings the number of clients that do not have a telephone number registered in each state, but parents would like to bring only those clients that do not have a telephone number. .a query eh essa:

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 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 wanted only the 'NONE' records to appear

sql sql-server

asked by anonymous 22.09.2014 / 21:06

1 answer

how to send the CPF number without the dots and dash (Input Mask) Load 3 boxes at a time by clicking the button

score 3 · Accepted Answer

Something like this?

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 requested 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;