Combining a set of MySQL Queries [Solved]

A place to discuss the implementation and style of computer programs.

Moderators: phlip, Moderators General, Prelates

GMWeezel
Posts: 53
Joined: Sun Jun 10, 2007 3:00 am UTC

Combining a set of MySQL Queries [Solved]

Postby GMWeezel » Sun Mar 14, 2010 6:11 pm UTC

I am just starting on a MySQL project and still have boat loads to learn so sorry if there is some elementary solution to this I am overlooking and if you need me to clarify anything, let me know.

I have a procedure that returns a set of rows containing groups that the users of my application are in. In the following code, "username" is a variable.

Code: Select all

SELECT name FROM networks WHERE groupmember = username;

mysql> SELECT name FROM networks WHERE groupmember = "gmweezel";
+---------+
| name    |
+---------+
| mathsci |
| triad   |
+---------+


From another table, I want to do a SELECT conditional such that along with other conditions, "networkname" must be in the returned set above. Right now, the only way I can figure to do this is a for loop that iterates through each returned row in "name" that does something along the lines of the following (pseudo-code):

Code: Select all

FOR wanted_groupname IN (SELECT name FROM networks WHERE groupmember = "gmweezel";)
    SELECT * FROM anothertable WHERE groupname = wanted_groupname;
END FOR


Is there another way to do this using a single MySQL statement?

Another way of phrasing this would that I'm liking to do something like "SELECT * FROM sometable WHERE fieldvalue IS INSIDE OF (SOME SQL STATEMENT THAT CONTAINS ROWS)".
Last edited by GMWeezel on Sun Mar 14, 2010 11:00 pm UTC, edited 1 time in total.
Wanted to know if signatures were dynamic or static.

fazzone
Posts: 186
Joined: Wed Dec 10, 2008 9:38 pm UTC
Location: A boat

Re: Combining a set of MySQL Queries

Postby fazzone » Sun Mar 14, 2010 6:15 pm UTC

*/

GMWeezel
Posts: 53
Joined: Sun Jun 10, 2007 3:00 am UTC

Re: Combining a set of MySQL Queries

Postby GMWeezel » Sun Mar 14, 2010 6:36 pm UTC

Union won't do the trick -- the columns I want from the second table are not the same.
Wanted to know if signatures were dynamic or static.

RockHawk
Posts: 19
Joined: Sun Nov 11, 2007 6:30 pm UTC

Re: Combining a set of MySQL Queries

Postby RockHawk » Sun Mar 14, 2010 7:58 pm UTC

What's wrong with this (basically just a contraction of your pseudocode)

Code: Select all

SELECT * FROM anothertable WHERE groupname IN (SELECT name FROM networks WHERE groupmember = "gmweezel");

You can add other conditions to that with no problem.

User avatar
Berengal
Superabacus Mystic of the First Rank
Posts: 2707
Joined: Thu May 24, 2007 5:51 am UTC
Location: Bergen, Norway
Contact:

Re: Combining a set of MySQL Queries

Postby Berengal » Sun Mar 14, 2010 8:04 pm UTC

This is what joins are for.

Code: Select all

select
  a.*
from
  networks n
  inner join anothertable a
    on (n.name = a.groupname)
It is practically impossible to teach good programming to students who are motivated by money: As potential programmers they are mentally mutilated beyond hope of regeneration.

GMWeezel
Posts: 53
Joined: Sun Jun 10, 2007 3:00 am UTC

Re: Combining a set of MySQL Queries

Postby GMWeezel » Sun Mar 14, 2010 9:30 pm UTC

Okay, it seems like I need to use a combination of Union and Join but I'm still at a loss so I'm just posting the test database I'm working with as I try to figure out the queries I'll need for the project.

Code: Select all

mysql> select * from networks;
+-----+---------+-------------+
| uid | name    | groupmember |
+-----+---------+-------------+
|   1 | dallas  | userjohn    |
|   2 | dallas  | anotherguy  |
|   3 | mathsci | userjohn    |
|   4 | mathsci | gmweezel    |
|   5 | triad   | gmweezel    |
|   6 | triad   | userjohn    |
|   7 | triad   | anotherguy  |
+-----+---------+-------------+
7 rows in set (0.00 sec)

mysql> select * from hashes;
+-----+------------+--------------------------+---------------------+
| uid | username   | hash                     | viewtime            |
+-----+------------+--------------------------+---------------------+
|   1 | gmweezel   | 2305beaad290da62d0e092ef | 2010-03-14 00:21:02 |
|   2 | userjohn   | 2305beaad290da62d0e092ef | 2010-03-14 00:21:02 |
|   3 | observer   | 5a87a32933a94ae3be7d23e6 | 2010-03-14 00:21:02 |
|   4 | anotherguy | ab67a856a39f885416541393 | 2010-03-14 00:21:02 |
+-----+------------+--------------------------+---------------------+
4 rows in set (0.00 sec)

mysql> select * from credentials;
+-----+------------+------------+--------------------------+
| uid | username   | password   | postkey                  |
+-----+------------+------------+--------------------------+
|   1 | gmweezel   | gmweezel   | f363614531bc43a1b143c1b1 |
|   2 | anotherguy | anotherguy | 76e43b63f21167e22264821a |
|   3 | userjohn   | userjohn   | f2a35f41d19e44c1b44121ac |
|   4 | outsider   | outsider   | fb63704631b547a1cc4101a5 |
|   5 | observer   | observer   | f3f3694591c14311aa46a1c0 |
+-----+------------+------------+--------------------------+


What I want to do is given user "USERNAME," I want to get all the groups they are in and then get a list of all of the members in the groups they are in excluding USERNAME. My best guess thus far was "select name from networks where groupmember = "gmweezel" union all select * from networks;" but that failed.

EDIT: Another failed attempt: "select * from networks where name = (select name from networks where groupmember = "gmweezel");" ERROR 1242 (21000): Subquery returns more than 1 row

EDIT AGAIN: Well, I figured it out:

Code: Select all

mysql> select * from networks where name IN (select name from networks where groupmember = "gmweezel");

I just decided to see if "IN" was a valid key word to use with subqueries and lo and behold it was.

Code: Select all

mysql> select * from networks where name IN (select name from networks where groupmember = "gmweezel") and groupmember != "gmweezel";
+-----+---------+-------------+
| uid | name    | groupmember |
+-----+---------+-------------+
|   3 | mathsci | userjohn    |
|   6 | triad   | userjohn    |
|   7 | triad   | anotherguy  |
+-----+---------+-------------+
3 rows in set (0.00 sec)
Wanted to know if signatures were dynamic or static.

User avatar
Xanthir
My HERO!!!
Posts: 5413
Joined: Tue Feb 20, 2007 12:49 am UTC
Location: The Googleplex
Contact:

Re: Combining a set of MySQL Queries [Solved]

Postby Xanthir » Mon Mar 15, 2010 12:15 am UTC

Normally, when you want to select some rows from a table, and then use those rows to select rows from a second table, you do a join. This is no different, you're just selecting from the same table both times, so you use a self-join, joining the table to itself.

I believe this should work. If not, it should be easily tweakable into working. It should be *much* more efficient than using IN and a subquery.

Code: Select all

SELECT n2.*
FROM networks AS n1
INNER JOIN networks AS n2
  ON n1.name = n2.name
WHERE n1.groupmember = 'gmweezel'
AND n2.groupmember != 'gmweezel'


Since the size of the useful rows in n1 is much smaller than the number of tables, it would probably be more efficient to replace that with a subquery. That is, replace the "FROM networks AS n1" line with "FROM (SELECT * FROM networks WHERE groupmember = 'gmweezel') AS n1" (and remove the test for n1's groupmember in the outer query). It'll mean a significantly smaller join, and thus much less rows to scan.

Generally you do *not* want to use a subquery in a WHERE clause. While the query analyzer can *sometimes* recognize that the result will be constant and thus run the subquery only once, if it fails you'll be running the subquery on every single row that it scans, which can *kill* your performance.
(defun fibs (n &optional (a 1) (b 1)) (take n (unfold '+ a b)))

GMWeezel
Posts: 53
Joined: Sun Jun 10, 2007 3:00 am UTC

Re: Combining a set of MySQL Queries [Solved]

Postby GMWeezel » Mon Mar 15, 2010 12:27 am UTC

I will try that shortly. This is only a test database. The production database will have a lot more rows that tables even for then five to ten people I'll be testing it with; at least several hundred per user at all times so is there a better way to optimize the query than what you posted.

EDIT: You query works perfectly. Now I just need to figure out what it means.

EDIT 2: Would you mind breaking down the query and explaining how it works? I'm not making too much progress on my own, mainly the "n2" "n1" reference are throwing me off. It seems weird to access an undeclared table.
Wanted to know if signatures were dynamic or static.

User avatar
Xanthir
My HERO!!!
Posts: 5413
Joined: Tue Feb 20, 2007 12:49 am UTC
Location: The Googleplex
Contact:

Re: Combining a set of MySQL Queries [Solved]

Postby Xanthir » Mon Mar 15, 2010 12:33 am UTC

In that case you definitely want to go with the subquery in the FROM.

Code: Select all

SELECT n2.*
FROM ( SELECT * FROM networks WHERE groupmember = 'gmweezel' ) AS n1
INNER JOIN networks AS n2
  ON n1.name = n2.name
WHERE n2.groupmember != 'gmweezel'


That's a reasonably efficient query. You can't get it much better, given the problem statement. It should be *much* more efficient than your "WHERE foo IN (subquery)" one.
(defun fibs (n &optional (a 1) (b 1)) (take n (unfold '+ a b)))


Return to “Coding”

Who is online

Users browsing this forum: No registered users and 9 guests