2

Here are my tables(including only relevant columns)

Table: carts
address_id - integer

Table: addresses
name - varchar
phone - varchar

Table: orders
order_number - integer (this is the foreign key for cart table)

I want to fetch phone number of the customers who have ordered only once, so I constructed the following query

select addresses.phone 
from orders 
   inner join carts on orders.order_number = carts.id 
   inner join address on carts.address_id = addresses.id 
group by addresses.phone 
having count(orders.*) = 1;

This works great! But I do also need to select customer name & order number and I updated the select statement to

select addresses.phone, addresses.name, orders.order_number ...

Now, postgres urges me to include these columns in GROUP BY clause but this will not return me the desired result.

I tried using subquery as following which seems to get me the desired result

select addresses.phone, (select ad.name from addresses ad where ad.phone = addresses.phone) ...

But using subquery is the only way to go about this ? or is there any simpler/optimal way ?

Mudassir Ali
  • 7,913
  • 4
  • 32
  • 60
  • Could you provide some sample data in order to reproduce your trouble? Do you have different names for the same phone number or/and vice versa? In general, if the data is stringent, adding columns to both `SELECT` and `GROUP BY` simultaneously should be fine. All the other cases, that come to my mind, would not be cured by your subselect. However, you could put your initial statement into your from list: [SQL Fiddle](http://sqlfiddle.com/#!15/0966a/1) – Abecee Jun 19 '15 at 10:25
  • have you tried using DISTINCT? – Nandakumar V Jun 19 '15 at 10:35
  • @SQL Fiddle: Yes different names for the same phone number is possible – Mudassir Ali Jun 19 '15 at 10:44
  • @SQL Fiddle: Your question actually made me rethink about my database structure, the whole reason I was allowing multiple names on same phone number is to allow an user to have multiple addresses & I was including name inside address table. Perhaps I can have a users table which stores name, phone number and associate has_many belongs_to relationship between users & addresses table. I think I shouldn't face this issue when I do that – Mudassir Ali Jun 19 '15 at 10:51
  • @Mudassir Ali: Personally, I'd go for a three tables: user, address and phone - usually at least the first two with ids not taken from the problem domain. (To be honest: Even if phone number might look like a fair id candidate - I'd play safe...) – Abecee Jun 19 '15 at 18:30
  • 1
    @Abecee That's what I ended up doing eventually :) – Mudassir Ali Jun 27 '15 at 09:45

1 Answers1

2

You can achieve this with a window function which doesn't require everything to be grouped:

select *
from (
  select addresses.phone, addresses.name, orders.order_number, 
         count(orders.order_number) over (partition by addresses.phone) as cnt
  from orders 
     inner join carts on orders.order_number = carts.id 
     inner join address on carts.address_id = addresses.id 
) t 
where cnt = 1;