Thanks for that Jeremy.
I thought I had tried 'min' somewhere along the way but I hadn't used
select_group so I couldn't get it to work.
The query you suggested gave me the error -
SQLite3::SQLException: only a single result allowed for a SELECT that is
part of an expression
I solved it by changing the query to -
DB[:users].join(DB[:addresses].where(:id=>DB[:addresses].select_group(:user_id).select_append{min(:id)}),
:user_id=>:id)
Thanks again for your help.
On Monday, September 30, 2019 at 8:00:32 PM UTC+5:30, Jeremy Evans wrote:
>
> On Monday, September 30, 2019 at 7:18:04 AM UTC-7, abhijit wrote:
>>
>> Hi,
>>
>> I've been breaking my head over this one.
>>
>> I have a 'users' table and an 'addresses' table with the foreign key
>> 'user_id'
>> I also have a 'claims' table where users make claims with the foreign key
>> of 'user_id'
>>
>> Now, I want a list of all claims made by users along with their first
>> addresses.
>>
>> The join I tried was to start from the claims dataset joined with the
>> users dataset. Till here everything worked fine. But as soon as I join with
>> the addresses table I end up getting multiple records for the specific user
>> according to the number of addresses they have.
>>
>> I understand what's happening but I've not been able find a solution for
>> this. Grouping by the address id would get rid of the wrong duplicates.
>>
>> I'm guessing I have to write some kind of a nested query here.
>>
>> My database is currently SQLite, but we'll soon be moving to Postgres (in
>> another couple of months). It would be good if I could write a database
>> agnostic query.
>>
>> Any help would be very much appreciated.
>>
>
> In terms of joining, you probably want to join to a subset of the
> addresses table that only has one address per user:
>
>
> DB[:users].join(DB[:addresses].where(:id=>DB[:addresses].select_group(:user_id).select_append{min(:id)}),
>
> :user_id=>:id)
>
> If you were just using PostgreSQL, you could use DISTINCT ON (in Sequel:
> dataset.distinct(Sequel[:users][:id]))
>
> Thanks,
> Jeremy
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/f331f363-9a66-433a-8bf1-e77546d66551%40googlegroups.com.
