CODEWITHSUNDEEP
sqldatabase
Eamonn O'Brien
Eamonn O'Brien

Reputation: 123

SQL Query trouble

I am trying to write an SQL query to retrieve all of a users pending events, however it is difficult with how my tables are structured.

My tables are as follows :

event {
event_id
name
group_id}

Pending {
GroupID
UserID
}

Users{
Username
UserID
}

Ever user is identified by a UserID, and every group by a GroupID. Events have in them a GroupID which points to a list of users. I need to retrieve all pending events for a certain user, so :

SELECT * FROM event
WHERE event.group_id = (SELECT GroupID FROM Pending)

But how do I then link this so only the Pending events for a user with a certain UserID are returned?

Upvotes: 1

Views: 68

Answers (5)

tereško
tereško

Reputation: 58444

SELECT
  event.event_id
FROM event
    LEFT JOIN Pending ON event.group_id = Pending.GroupID
    LEFT JOIN Users USING (UserID)
WHERE Users.Username = 'foobar'

USING() can be utilized when columns on both sides of selection have the same name. It makes for easier reading. That is one of main reason why i would recommend to have same name for same data throughout the database. For example, if you Documents table and primary key document_id, then in all the other tables, where you are referencing the ID of a document, you use the same name for the column.

To learn more about JOINs : in mysql or postgresql read the links. And for visual representation of what each join does: this article.

Also you should get some book about your preferred RDBMS and learn all the basics, then you can expand your knowledge by reading SQL Antipatterns book. Or you could just carefully look through slides, made by book's author.

Upvotes: 1

Teja
Teja

Reputation: 13524

SELECT * FROM event E,
              pending P,
              Users U
         WHERE E.group_id = P.GroupID
           AND P.UserID = U.UserID
           AND U.UserID = (Some XYZ id)

XYZ should be some Integer value.

Upvotes: 0

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47038

select e.* from event e
inner join pending p on
e.group_id = p.GroupID
inner join Users u
on p.UserID = u.UserID
where u.UserID = 123

actually you can skip the join with the Users-table if you already have the UserID:

select e.* from event e
inner join pending p on
e.group_id = p.GroupID
where p.UserID = 123

The typical way to write this is using inner join. Traditionally it has been better performing than sub selects, but modern DBMS:s optimize them into the same query. If you really want to write with a sub select you type like this

SELECT * FROM event
WHERE event.group_id in (SELECT GroupID FROM Pending WHERE UserID = 123)

Upvotes: 4

yumyum
yumyum

Reputation: 132

You can try :

select e.*, u.* from pending as p, users as u, event as e
WHERE
p.group_id = e.group_id
AND p.user_id = u.userID

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52645

You want to use the JOIN clause to link the tables together rather than using the WHERE clause to filter

SELECT u.username, 
       e.name 
FROM   users u 
       INNER JOIN pending p 
         ON u.userid = p.userid 
       INNER JOIN event e 
         ON p.groupid = e.groupid 
WHERE 
     u.UserID = SomeID

Upvotes: 1

Related Questions