Retrieving data from a database with relationships

user_7276273 0
Viewed 190

I am trying to retrieve some data from my database, I have already made the right relationships inside the database but I just can't get my head around which query to use.

Here is a visual representation of the different relationships: phpmyadmin

What I want is to get all data from clanwars aswel as all usernames from the users who have applied for these wars.

Can you guys help me?

Thanks in advance!

mysqli sql php
2 Answers

How about:

select w.*, m.username
  from clanwars w
  join applications a on a.warid = w.id
  join members m on m.id = a.playerid
user_64361910

All data from clan wars:

select * from clanwars;

All users that have applied to participate in a certain war:

select playerid from applications where warid = <war_id>;

Make sure to replace war_id with the desired war id you want to pull playerid's from.

All users that have applied to a war at all:

select distinct playerid from applications;

All usernames that have applied to a war (using a subquery):

select username from members where id in (select distinct playerid from applications);

My go to SQL resource is www.w3schools.com/sql/. Check it out when you get a chance.

user_45046640
Related