I have Table1 that I want to combine with Table2 to create a query.
Table1 has the following column fields:
GroupID | MyName| Age | Animal
The table has the following fields:
AnimalID | Animal | Rating | Mood
I have a relationship set up linking `AnimalID`, `Animal`, and `Gender` from Table1 to Table2, which looks like this:
If I made a query out of the above, it would look something like this:
GroupID links the Name and Age together. Each Animal belongs in a Rating, which has a Mood, which belongs in each GroupID. Table2 does not have any records with the same Animal and Rating appearing twice.
However, what I would like to display in a query is a filtering where, for each GroupID, display the MyName, Age, Rating, Animal, and Mood where the Rating is unique for each Age for each GroupID, i.e. It's a One (Age) to Many (Rating) relationship. If all the records in that GroupID has Mood as Good, filter the first record. Otherwise, if any one record for that GroupID has Mood as Bad, display the first Bad record.
For example, this is how I would expect the query to look:
For the records where Cheetah and Hippo and `Good`, displaying the first record above as shown above is sufficient:
I am completely lost as to where I can even began to start handling this difficult filtering criteria. How can I achieve the above results in SQL?
Please see attached demo for reference and I apologize for the weird spacing.
EDIT: I posted in the wrong thread. MOD please move to Queries.
Table1 has the following column fields:
GroupID | MyName| Age | Animal
The table has the following fields:
AnimalID | Animal | Rating | Mood
I have a relationship set up linking `AnimalID`, `Animal`, and `Gender` from Table1 to Table2, which looks like this:
Code:
| Table1| |Table2|
GroupI
MyName
Age
Rating AnimalID
Animal -> Animal
Rating
Mode
If I made a query out of the above, it would look something like this:
Code:
GroupID MyName Age Rating Animal Mood
17 Kevin 1 1 Lion Good
17 Kevin 1 1 Leopard Bad
17 Kevin 1 2 Leopard Bad
17 Kevin 1 3 Tiger Good
17 Kevin 1 3 Lion Good
17 Kevin 1 3 Leopard Bad
17 Kevin 1 4 Giselle Good
17 Kevin 1 4 Tiger Bad
18 Kevin 2 2 Hippo Good
18 Kevin 2 2 Cheetah Good
18 Kevin 2 3 Cheetah Good
19 Kevin 3 1 Leopard Bad
19 Kevin 3 2 Leopard Bad
19 Kevin 3 3 Leopard Bad
20 David 1 1 Leopard Bad
20 David 1 2 Cheetah Good
20 David 1 2 Leopard Bad
20 David 1 3 Cheetah Good
20 David 1 3 Leopard Bad
21 David 3 3 Zebra Bad
23 John 2 1 Lion Good
23 John 2 3 Lion Good
24 Henry 1 3 Buffalo Good
24 Henry 1 5 Baboon Bad
GroupID links the Name and Age together. Each Animal belongs in a Rating, which has a Mood, which belongs in each GroupID. Table2 does not have any records with the same Animal and Rating appearing twice.
However, what I would like to display in a query is a filtering where, for each GroupID, display the MyName, Age, Rating, Animal, and Mood where the Rating is unique for each Age for each GroupID, i.e. It's a One (Age) to Many (Rating) relationship. If all the records in that GroupID has Mood as Good, filter the first record. Otherwise, if any one record for that GroupID has Mood as Bad, display the first Bad record.
For example, this is how I would expect the query to look:
Code:
GroupID MyName Age Rating Animal Mood
17 Kevin 1 1 Leopard Bad
17 Kevin 1 2 Leopard Bad
17 Kevin 1 3 Leopard Bad
17 Kevin 1 4 Tiger Bad
18 Kevin 2 2 Hippo Good
18 Kevin 2 3 Cheetah Good
19 Kevin 3 1 Leopard Bad
19 Kevin 3 2 Leopard Bad
19 Kevin 3 3 Leopard Bad
20 David 1 1 Leopard Bad
20 David 1 2 Leopard Bad
20 David 1 3 Leopard Bad
21 David 3 3 Zebra Bad
23 John 2 1 Lion Good
23 John 2 3 Lion Good
24 Henry 1 3 Buffalo Good
24 Henry 1 5 Baboon Bad
For the records where Cheetah and Hippo and `Good`, displaying the first record above as shown above is sufficient:
Code:
18 Kevin 2 2 Cheetah Good
18 Kevin 2 2 Hippo Good
I am completely lost as to where I can even began to start handling this difficult filtering criteria. How can I achieve the above results in SQL?
Please see attached demo for reference and I apologize for the weird spacing.
EDIT: I posted in the wrong thread. MOD please move to Queries.