Solved

Select Statement over 3 tables

Posted on 2014-12-17
7
142 Views
Last Modified: 2014-12-23
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
Comment
Question by:rdkli
  • 5
  • 2
7 Comments
 
LVL 2

Assisted Solution

by:Pratik Makwana
Pratik Makwana earned 500 total points
ID: 40504475
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
 

Author Comment

by:rdkli
ID: 40504788
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
 
LVL 2

Assisted Solution

by:Pratik Makwana
Pratik Makwana earned 500 total points
ID: 40506360
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:rdkli
ID: 40506411
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
 

Assisted Solution

by:rdkli
rdkli earned 0 total points
ID: 40506610
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
 

Accepted Solution

by:
rdkli earned 0 total points
ID: 40506662
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
 

Author Closing Comment

by:rdkli
ID: 40514589
I Could do it by myself
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question