Another Query Question 1

[azzazzello]

I have a query which uses 2 tables in the following way.

SELECT records.phone as number, count(records.phone) as calls, customers.cust_name FROM records, customers WHERE ... AND records.phone = customers.phone GROUP by number;

basically, the records table contains the call records (including number) whereas customers table contains customer information, including customer name, and customer phone #. There are phone records that have phone numbers NOT listed in customers database. In the above query, these records will be IGNORED, as records.phone = customers.phone will fail for these records. What I need is a query that will, if possible, pull out cust_name for those numbers that an entry for them in customers database, but not ignore those that do not. Assigning NULL to cust_name field for these records will do just fine, as long as they are not ignored

 

[TonyGroves]

How about:
[tt]
SELECT
records.phone as number,
count(records.phone) as calls,
customers.cust_name
FROM records LEFT JOIN customers USING (phone)
WHERE ...
GROUP by number;
[/tt]
cust_name will be null for missing customer records.

 

[azzazzello]

Damn...I seem to have oversimplified this too much for my own good. Tony, your answer works, except that I actually have 3 tables, and they are as follows:

- records table contains the call records (including number)
- customers table contains customer information, including customer name (cust_name) and cust_id
- endpoints table, which has number field and cust_id to identify who it belongs to.

BAsically, a customer can have multiple endpoints, hence the endpoints table. With this in mind, the original query looks like this:

SELECT records.phone as number, count(records.phone) as calls, customers.cust_name FROM records,customers,endpoints WHERE ... AND customers.cust_id = endpoint.cust_id AND records.phone = endpoint.phone GROUP by number;

The goal is the same, but the above solution is no longer applicable as I have an intermediate table now . Any ideas?

 

[TonyGroves]

Just expand it a bit:
[tt]
SELECT
records.phone as number,
count(records.phone) as calls,
customers.cust_name
FROM
records
LEFT JOIN endpoints USING (phone)
LEFT JOIN customers USING (cust_id)
WHERE ...
GROUP by number;
[/tt]

 

[azzazzello]

Excellent! thank you!