Aid with SQL query for my JAVA application

Question

I'm not much of an SQL guy so forgive me if something similar has been asked before. I'm not even sure what I would need to search for in order to learn this. Since I only need to do something like this once I thought I could justify asking.

I'm writing one of my first android applications that needs to talk to an online database, and have successfully written a couple of SQL queries that work well with my application, but this one is slightly complicated for my basic knowledge.

Below I have provided a sample of what I need in what I feel is understandable by anyone with at least a basic knowledge of SQL. I am wondering if any kind soul would be able to help scratch up a query or give me a little insight for what I would need to do. Thanks in advance!

Pseudo Sample:

SELECT * 
FROM events 
WHERE user_has_event.user_user_id = user.user_id AND user_has_event.attendance = 1 OR 2
JOIN attendance

Here is a basic visual of my tables (Without user table):

Event Table User_has_event Table
----------------------------------- ---------------------------------------
|event_id|event_name|event_society| |user_user_id|event_event_id|attendance|
----------------------------------- ---------------------------------------
| | | | | | | |

Here is my desired outcome:

Outcome Table
----------------------------------------------
|event_id|event_name|event_society|attendance|
----------------------------------------------
| | | | | 

Answer 1

Since your knowledge of SQL is basic, I'll expand a bit (well, as it turns out, rather a lot) on Andy's answer. First, the t1 and t2 are not required, but are a convenience. You can refer to a table directly, and don't have to if the field names are unique. You could do this:

SELECT
events.event_id,
events.event_name,
events.event_society,
user_has_event.attendance
FROM
events
INNER JOIN user_has_event ON events.event_id = user_has_event.event_event_id

As you can see, that is rather long-winded and tedious. So you can, when you first reference a table, immediately follow it with an abbreviation as Andy has done, and indeed as it is generally considered best practice to do. Now, you could also do this:

SELECT
event_id,
event_name,
event_society,
attendance
FROM
events
INNER JOIN user_has_event ON event_id = event_event_id

You can get away with this because all of the field names are unique in the tables accessed by your SELECT statement. Since this is often not true, it's not a good idea, since it's too easy to miss an ambiguous reference. Andy's is the best way to do it. Now, you might have gone out of your way to use different field names because you didn't know that you could reference the table using Table.Field syntax. It's often clearer to use the same field name; different people feel differently about this. I generally just use "ID" for the primary key in each table. That works because you can resolve ambiguities by using Table.Field to refer to a field.

This leads to the next thing you will find it helpful to know, which is that you can assign whatever field name you want to the output with the AS keyword. Suppose I rename your fields thus:

Event
 ID
 Name
 Society
UserEvent
 ID
 EventID
 Attendance

Now, have a look at this:

SELECT
e.ID AS 'Event ID',
e.Name AS 'Event Name',
e.Society AS 'Event Society',
ue.Attendance
FROM
Events e
INNER JOIN UserEvent ue ON e.ID = ue.EventID

Now you have decoupled the name of the selected field from the name of the field in the outcome, which should save you headaches down the line. An important principle is that the way that you store the data and the way that you format data output should be loosely coupled. You don't want considerations of how you want your output data to look to dictate how you should name your fields, so you need to know this stuff.

Now, let's pretend that you also have a User table (you probably do). Let's say it looks like this (it probably doesn't):

User
 ID
 FirstName
 LastName
 OtherStuff

Now, we'll modify the UserEvent table thus, to include a foreign key to the User table:

UserEvent
 ID
 EventID
 UserID
 Attendance

Now, have a look at this:

SELECT
e.Name AS 'Event Name',
e.Society AS 'Event Society',
u.LastName + ', ' + u.FirstName AS 'User Name',
ue.Attendance
FROM
Events e
JOIN UserEvent ue ON e.ID = ue.EventID
JOIN User u ON u.ID = ue.UserID

This should give you the basics, except for the WHERE clause, the basics of which you can probably pick up on your own (feel free to ask questions about the WHERE clause as well).

One side note: a JOIN is the same as an INNER JOIN, the most common type of join, representing the intersection of two sets. There are also LEFT, RIGHT, and (sometimes) OUTER joins. I generally just say JOIN rather than INNER JOIN; again, different people feel differently about this. Consistency is the most important principle here.

• kmil: This is a very impressive answer! I'm just looking into it now.. so far successful up to the point where I try to get the results to show based on the user_id.
• kmil: Perfect, turns out I was putting the where clause in the wrong place, I'll get this implemented properly and accept your answer. Thanks for spending your time on this it has been very useful!
• BobRodes: You're very welcome. Glad you got the WHERE clause sorted on your own.

Answer 2

You can add in USER table similarly ... but for the basic output you requested see the following

SELECT
t1.event_id,
t1.event_name,
t1.event_society,
t2.attendance
FROM
events t1
INNER JOIN user_has_event t2 ON t1.event_id = t2.event_event_id