[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 154
  • 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
 
rdkliAuthor 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rdkliAuthor 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
 
rdkliAuthor 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
 
rdkliAuthor 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
 
rdkliAuthor Commented:
I Could do it by myself
0

Featured Post

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.

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