CODEWITHSUNDEEP
mysqlsql
Darko
Darko

Reputation: 375

Combine four queries into one

I have inherited a legacy code base, and a database with it (which I cannot modify), and I got stuck on these queries while refactoring the code. There are 4 separate queries which I am trying to put in one, if possible. I will supply the tables schema if needed. Also, if you think that it is not possible to solved in one query, please elaborate on that one.

These are the queries (it is about sports), the final purpose is to get all cups which the given user has created and/or joined

The first one is trying to get all cups which the user has created

SELECT cup_id FROM cups WHERE user_id = 'givenUser' AND cup_type <> 6

The second one is trying to get all cups which the user has joined

SELECT joined.cup_id, joined.cup_rank FROM joined LEFT JOIN cups USING (cup_id) WHERE joined.user_id = 'givenUser' AND cups.cup_type <> 6

Now, the results from the first one are put into a comma-separated string which is then supplied to the next queries. The third one is reading information for the selected cups

SELECT cup_id, user_id, status FROM joined WHERE cup_id IN (cupList)

And the last one gets the names of the cups, their edition, and orders them

SELECT name, cup_id, edition, cups.user_id FROM cups LEFT JOIN registered_cups USING(register_id) WHERE cup_id IN (cupList) ORDER BY name ASC, edition DESC

As you can see there is a lot of repetition, and not needed things, so this is what I came up with:

SELECT cups.cup_id, cups.edition, cups.user_id, joined.cup_rank, registered_cups.name
FROM cups, joined, registered_cups
WHERE cups.cup_id = joined.cup_id
AND cups.register_id = registered_cups.register_id
AND joined.user_id = '308288'
AND cups.cup_type <>6
ORDER BY registered_cups.name ASC , cups.edition DESC

The problem with my query is that I only get the cups which the user joined, but there is also a possibility that a user created a cup, but did not take part into it. That's why are the first two queries, but I don't know how to combine them successfully. I hope you understand what I'm trying to achieve.

Update:

Here is a fiddle of the schema with a little input. Ther user in question is user_id = 133, it should basically select all of the rows, which is 21, with the point that one of them should be a cup that is organized, but not joined by the user, all of the others are both.

Upvotes: 0

Views: 64

Answers (2)

Gervs
Gervs

Reputation: 1397

I assume you have an users table and cups are created before they can be joined (seems logic)

SELECT
    c.cup_id,
    u.user_name,
    c.edition,
    j.status,
    j.cup_rank,
    cr.user_id IS NOT NULL created,
    r.name
FROM
    (SELECT 133 user_id) u
/* semi cross join the cups table */
JOIN
    cups c
    ON u.user_id = 133 AND c.cup_type <> 6
JOIN
    registered_cups r
    ON c.register_id = r.register_id
LEFT JOIN
    joined j
    ON c.cup_id = j.cup_id AND u.user_id = j.user_id
LEFT JOIN
    cups cr
    ON c.cup_id = cr.cup_id AND u.user_id = cr.user_id
ORDER BY r.name, c.edition

Maybe I have some column names wrong or from the wrong table, but the idea stays the same

Edit: SQL Fiddle

Upvotes: 1

Jim Sosa
Jim Sosa

Reputation: 628

There's a couple ways you could do it. You could do an outer join like this:

SELECT cups.cup_id, cups.edition, cups.user_id, joined.cup_rank, registered_cups.name
  FROM cups INNER JOIN registered_cups ON cups.register_id = registered_cups.register_id
       LEFT OUT JOIN joined ON cups.cup_id = joined.cup_id and joined.user_id = '308288'
 WHERE cups.cup_type <>6
   AND (joined.user_id IS NOT NULL
    OR cups.user_id = '308288')
 ORDER BY registered_cups.name ASC , cups.edition DESC

or a union like this:

SELECT cups.cup_id, cups.edition, cups.user_id, joined.cup_rank, registered_cups.name
  FROM cups, joined, registered_cups
 WHERE cups.cup_id = joined.cup_id
   AND cups.register_id = registered_cups.register_id
   AND joined.user_id = '308288'
   AND cups.cup_type <>6
 UNION
SELECT cups.cup_id, cups.edition, cups.user_id, null, registered_cups.name
  FROM cups, registered_cups
 WHERE cups.register_id = registered_cups.register_id
   AND cups.user_id = '308288'
   AND cups.cup_type <>6
   AND NOT EXISTS 
       (SELECT 1 
          FROM joined 
         WHERE cups.user_id = joined.user_id and cups.cup_id = join.cup_id)
 ORDER BY registered_cups.name ASC , cups.edition DESC

Upvotes: 1

Related Questions