SQL Query Where Assistance Required
1 hour ago, Eigenvektor said:You should really think about normalizing your database, as @minibois said.
You're doing things in your database that have no business being there. It'll make your queries a lot slower and require tons of workarounds in the end. How you present parents (e.g. "and" between them, female first, male second, etc.) is presentation logic. That has no business being in your data layer, since requirements for this can change in the future.
Have a table for persons, then have a separate table for relationships between them.
{ person_id, first_name, last_name, … }
{ relation_id, child_id, parent_id }
You could also add a relationship status. For example, what about people who have natural parents and foster parents? How would you present those in your combined column? This makes queries very awkward as you already discovered, require multiple queries and then additional processing in your code when you have a result.
With normalized data, querying e.g. only for the mother would become a lot easier.
select … from person child join relation r on r.child_id = child.person_id join person mother on mother.person_id = r.parent_id and mother.sex = 'f'
I understand, however that would be out of my hands. On top of that, the database belongs to the software TASS and we don't plan to go around modifying it. This is the only case at this stage where we are inserting data from the back end. Once this is done, it's up to HR to ensure, the data is put in correctly and we will most likely generate a report weekly to ensure that they are.
I have ended up getting it to do what we want now anyway. Just waiting the go ahead to actually run it.
Either way, Thank you for the info i will look back at this maybe at a later date if need be.
Also thanks to @Minibois for all the help you have provided.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now