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 sequel-talk+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/edbb6295-d0eb-4ffb-be45-3965bcafbeed%40googlegroups.com.
