SQL - Join 3 tables but limit the results based on the first table

Question

Here is the work-flow of my situation:

I have a website that allows registered users to register for prizes from local prize sponsors (ie: a business) For example, there may be a page that has Pizza Hut as a prize sponsor, and the registered/authenticated user simply clicks "Enter the Drawing" and once they do they can't click it again.

Here are the 3 tables:

1. BD_Listing (This table is for the prize sponsor, and includes basic columns such as ListingID, Title, ContactPerson, Email, Prize)

2. Users (This table is for the registered users on the site, and includes UserID, FirstName, LastName, Email, etc.)

3. PrizeEntry (This table is where the registrant data goes: EntryID, ListingID, User_ID, Date_Created)

Now all this works fine, as far as storing the data in the database. My problem is SELECT. I have a custom module that I'm building on the ADMIN side of the website, that I want to perform the following:

Here is my query that works:

SELECT ListingID, Title, ContactEmail, PrizeSponsor
FROM [BD_Listing]
WHERE PrizeSponsor = 'True'

If I display this data in a table on the site, it works fine. The problem is, I need more data, specifically the User_ID from the PrizeEntry table. This User_ID needs to be joined with the UserID from the Users table, because I need to pull their other info from it.

SELECT a.ListingID, a.Title, a.ContactEmail, a.PrizeSponsor
       b.ListingID, b.User_ID
       FROM BD_Listing a INNER JOIN PrizeEntry b ON a.ListingID = b.ListingID
       WHERE a.PrizeSponsor = 'True'

Now, the first problem arises. If 20 people register for Pizza Hut, then I'm going to get 21 rows of data, which isn't what I'm going for. Here is ultimately my code to join all the information and the reasoning for it, and then you can tell me how idiotic I am for doing it wrong :)

SELECT a.ListingID, a.Title, a.ContactEmail, a.PrizeSponsor
       b.ListingID, b.User_ID
       c.UserID, c.FirstName, c.LastName, c.Email
       ,(SELECT COUNT(ListingID) AS EntryCount FROM PrizeEntry WHERE (ListingID = a.ListingID)) AS EntryCount
       ,(SELECT TOP 1 User_ID AS RandomWinner FROM PrizeEntry WHERE (ListingID = a.ListingID)ORDER BY NEWID()) as RandomWinner

       FROM BD_Listing a INNER JOIN PrizeEntry B on a.ListingID = b.ListingID
                         INNER JOIN Users C on b.User_ID = c.UserID
       WHERE a.PrizeSponsor = 'True'

Okay, so in my table that displays this data I just want Pizza Hut to show up ONE time, but instead because it's joined with the PrizeEntry table, it shows up multiple times.

I want to see:

Business: Pizza Hut // comes from the BD_Listing table
Contact: John Doe  // comes from the BD_Listing table
Total Registrations: 20 // count from the PrizeEntry table
Random Winner: John Smith (UserID:10) // result from the subquery

But instead, I see multiple rows for each business for every time a prize is registered for.

I apologize for the length... I'm new here, and this is my second post. And going through the beginning learning curves of SQL.

Thank you so much for any advice.

Answer 1

First, if there are 20 users you should expect 20 rows, not 21.

I think you can simplify your query, just by removing the outer join to users, to get what you want:

SELECT a.ListingID, a.Title, a.ContactEmail, a.PrizeSponsor,
       (SELECT COUNT(ListingID) AS EntryCount FROM PrizeEntry pe WHERE (pe.ListingID = a.ListingID)) AS EntryCount,
       (SELECT TOP 1 User_ID AS RandomWinner FROM PrizeEntry pe WHERE (pe.ListingID = a.ListingID) ORDER BY NEWID()) as RandomWinner
FROM BD_Listing a
WHERE a.PrizeSponsor = 'True'

You want the information by prize sponsor. The correlated subqueries get the additional information. To get more information about the winner, just make this a subquery and join to the appropriate table, as below:

with t as (
         SELECT a.ListingID, a.Title, a.ContactEmail, a.PrizeSponsor,
                (SELECT COUNT(ListingID) AS EntryCount FROM PrizeEntry pe WHERE (pe.ListingID = a.ListingID)) AS EntryCount,
                (SELECT TOP 1 User_ID AS RandomWinner FROM PrizeEntry pe WHERE (pe.ListingID = a.ListingID) ORDER BY NEWID()) as RandomWinner
         FROM BD_Listing a
         WHERE a.PrizeSponsor = 'True'
        )
select t.*, u.Firstname, u.LastName, u.Email
from t join
     users u
     on t.RandomWinner = u.user_id

Answer 2

The simplest method is two do two queries. The first one gets the sponsor info, and the second query gets the entries.

Technically this is a case of N+1 (See: What is SELECT N+1? ) but I wouldn't worry about it here.

Theoretically you could get everything at once, then in the front end you loop through it and see if you have already printed the sponsor. The trouble is that that is so much work that doing N+1 is actually much better.

Now, if you wanted summary info about the sponsor that's different - you can do that with a GROUP BY statement.

Like this:

SELECT a.ListingID, a.Title, a.ContactEmail, a.PrizeSponsor, COUNT(*) AS EntryCount
   FROM BD_Listing a INNER JOIN PrizeEntry B on a.ListingID = b.ListingID
                     INNER JOIN Users C on b.User_ID = c.UserID
   WHERE a.PrizeSponsor = 'True'
   GROUP BY a.ListingID

Answer 3

You're going to get all matching combinations because of the structure of your joins : as things stand you cannot get anything else.

What you need to do is to get the entrants and work out your winner first, then correlate this back to the listing:

select a.ListingID, a.Title, a.ContactEmail, a.PrizeSponsor, d.userid, d.firstname, d.lastname, d.email
from BD_Listing a inner join (
    select top 1 listingid, userid, firstname, lastname, email from (
        select b.ListingID, c.UserID, c.FirstName, c.LastName, c.Email, newid() as ordering
        from PrizeEntry B INNER JOIN Users C on b.User_ID = c.UserID                    
    ) x order by ordering
) d on a.listingid = d.listingid