MySQL Forums
Forum List  »  Newbie

gnarly SQL query &/or should I improve my db design?
Posted by: John Allsopp
Date: March 29, 2017 02:35AM

Hi

I thought this was pretty simple, but I've spent a few hours on it and not solved it.

I've a table of restaurants like
RESTAURANT_id
RESTAURANT_name
RESTAURANT_postcode

a table of actions like
ACTION_restaurantId
ACTION_action (string)

a table of postcode districts that are 'taken' along with the restaurant id that took it
TAKEN_restaurantId
TAKEN_districtId
TAKEN_fromDate
TAKEN_toDate

and a table of districts
DISTRICT_id

What I'm looking for is the SQL that provides (ultimately) a list of the first restaurant (lowest id number) in each non-taken district where the restaurant has an associated action 'checked' but doesn't have an associated action 'mailed'

I'm usually pretty good at SQL but I think maybe my database design is forcing me into a gnarly sql query, so if you think that, feel free to suggest an improvement :-)

I've spent hours with subqueries and so on but none of them worked.

I can post-process the results, so we could start with a simplification: a list of the restaurants in each district where the restaurant has an associated action 'checked' but doesn't have an associated action 'mailed', sorted by DISTRICT_id, then RESTAURANT_id asc

All help appreciated.

All the best
J

Options: ReplyQuote


Subject
Written By
Posted
gnarly SQL query &/or should I improve my db design?
March 29, 2017 02:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.