• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 157
  • Last Modified:

Select Statement over 3 tables

Hello There

I need help to solve this question: "Which members organise which event in a specific place?"
I've got 3 Tables:

member_table
| MNr | Name | Surname |

organises_table
| MNr | ENr |

Event_table
| ENr | Place |

*MNr = Member ID, ENr = Event ID

how can i manage that? I need in the end a list of all members, which organises a event at a specific place. Can someone help out?

best wishes
0
rdkli
Asked:
rdkli
  • 5
  • 2
4 Solutions
 
Pratik MakwanaData AnalystCommented:
try this....

Select m.Mnr, m.Name, m.Surname, o.Enr, e.Place
from member_table m, organises_table o, Event_table e
Where m.Mnr=o.Mnr
And o.Enr=e.Enr

or


Select m.Mnr, m.Name, m.Surname, o.Enr, e.Place
from member_table m
Join organises_table o  ON m.Mnr=o.Mnr
Join Event_table e ON o.Enr=e.Enr
0
 
rdkliApplication ManagerAuthor Commented:
heh, Thanks.. That's kinda embarrassing how simple it is.. Works Fine!

Can you help me out with these example too?

I need to answer this Question: "On which Date does the financier miss which Place?"
So I need the Place and Date on which the financier is missing. I've got this Data's:

function_table
| MNr | Function |
     3        financier

member_table
| MNr | Name | Surname |

participation_table
| Mnr | ENr |
      3        1
      3        2
Event_table
| ENr | Place | Date |
     1         A         2015-02-17
     2         B         2015-10-21
     3         C         2015-05-11


So I already know he's missing at the Event with the ID/Nr3. But i just dont get it to work with your example. Is these even possible?

Best Wishes
0
 
Pratik MakwanaData AnalystCommented:
Select m.Mnr, m.Name, m.Surname, p.Enr, f.Function, e.Place, e.Date
from function_table f, member_table m, participation_table p, Event_table e
Where f.Mnr=m.Mnr
And m.Mnr=p.Mnr
And p.Enr=e.Enr

you can add your where clause in this query as per your requirements like .e.date='2015-05-11' etc.....
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
rdkliApplication ManagerAuthor Commented:
Thanks for the answer, but isn't it possible to make it more logical?

That the statement automaticly checks where the person's missing?

Because this way i have to check all events manually?
0
 
rdkliApplication ManagerAuthor Commented:
Something like
select e.Place, e.Date From Event_table e
MINUS
Select e.Place, e.Date From Event_table e, participation_table p, function_table f
Where
p.ENr = e.ENr
f.MNr = p.Mnr
f.Function = 'financier'

But Minus doesnt work in Mysql.. any other options for Mysql?
0
 
rdkliApplication ManagerAuthor Commented:
SELECT DISTINCT e.Date, e.Place FROM Event_table e
WHERE (e.Date, e.Place) NOT IN (Select e.Date, e.Place From participate p, Function f
Where p.VNr = e.VNR AND f.MNR = p.MNR AND f.Function = 'financier');

That works!! :-D thx for the help!! @Pratik, I learned alot =)
0
 
rdkliApplication ManagerAuthor Commented:
I Could do it by myself
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now