CODEWITHSUNDEEP
sqldatabasepostgresql
Roberto
Roberto

Reputation: 809

check at least a record exists

two tables:

== customers ==
cust_id

== attachments ==
att_id
cust_id

1 customer -> MANY attachments

i would retrieve all the custmers and, adding the boolean virtual field "has_attach" to select, know if a customer have got attachments.

without GROUP BY, if this is possibile :-)

Upvotes: 1

Views: 95

Answers (3)

MatBailie
MatBailie

Reputation: 86775

Depending on how many many really means, the COUNT(*) option can impose an un-necessary burden.

In which case, the following can sometimes yield benefits.

SELECT
  *,
  CASE WHEN EXISTS (SELECT *
                      FROM attachments
                     WHERE cust_id = customers.cust_id)
       THEN 1
       ELSE 0 END  AS has_attach
FROM
  customers

This is because EXISTS doesn't actually read all the records. It just checks that any record exists.

In fact, when using indexes this doesn't even read any records from the table. It just checks that the index points to any matching records and stops there.

Upvotes: 1

SRIRAM
SRIRAM

Reputation: 1888

try this

update customers set field='has attach' 
where cust_id in (select c.cust_id cusotmer c
inner join attachment a on c.cust_id=a.cust_id 
having count(a.id)>1

Upvotes: 0

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

SELECT 
    customers .cust_id,
    IFNULL(count , 0) as Total
FROM customers 
LEFT JOIN 
    (
        SELECT att_id , count(*) as count
        FROM attachments group by cust_id
    ) AS att on att.cust_id = customers.cust_id

This is something in MySQL

Upvotes: 0

Related Questions