• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 859
  • Last Modified:

SQL join 2 queries

Table 1: members
Fields: "id", "ug_school_id", etc

Table 2: tags
Fields: "uid","ttype","tag"

Tthe following sql queries are generated based on user input (database query script) - so it is preferred that the solution works with the existing $sql1/$sql2 variables.

$sql1 = "SELECT id FROM members WHERE 1=1 AND alumni = '1' AND ug_school_id = '7'";
$sql2 = "SELECT uid FROM tags WHERE 1=0 OR (ttype = 'hometown' AND tag = 'Cambridge, MA')";

All I want to do, is generate a table of results which shows the member rows which match in both these tables.

And also generate an extra temporary column in that results set for the 'hometown' data

E.g. if a member is an alumni and school id is 7 and hometown is Cambridge, MA

Many thanks in advance
0
anonwig
Asked:
anonwig
  • 9
  • 8
1 Solution
 
VanHackmanCommented:
Something like:

SELECT id.m, uid.t FROM members AS m, tags AS t WHERE m.alumni = '1' AND m.ug_school_id = '7' AND ttype = 'hometown' AND tag = 'Cambridge, MA'

?
0
 
VanHackmanCommented:
Sorry, I make a mistake... xD

I mean something like:


SELECT m.id, t.uid FROM members AS m, tags AS t WHERE m.alumni = '1' AND m.ug_school_id = '7' AND t.ttype = 'hometown' AND t.tag = 'Cambridge, MA'

Open in new window

0
 
anonwigAuthor Commented:
Thanks - Is there any way of working with the existing queries? even if it isn't as efficient?
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
VanHackmanCommented:
What do you want to show in your table?

I mean, which columns would you display, and how data from query 1 is related with data of query 2?
0
 
anonwigAuthor Commented:
Let us assume that I would want to show all the columns in table 1, where the user id appears in the results of both queries

I would also want to add a temp column in the results set, for the hometown 'tag' from the second query.

The uid in table 2 will be equal to an id in table 1
0
 
anonwigAuthor Commented:
(The reason for the separate table is that there might be multiple rows in the tags table with the same uid)
0
 
VanHackmanCommented:
In this case, you cannot use the queries that you have provided as they are, because you are only pulling the "id" column from the table 1. not all the columns.

So, in order to do something like this, you query should be:


$sql1 = "SELECT * FROM members WHERE alumni = '1' AND ug_school_id = '7'";

And for showing the results in a table you code should look similar to:


<?php

// Set the query
$sql1 = "SELECT * FROM members WHERE alumni = '1' AND ug_school_id = '7'";

// Run the query
$result_query = @mysql_query ($sql);

//Open the table
echo "<table>";

// Show a row for each row's result
while ($row = mysql_fetch_assoc($result_query))
{
echo "<tr>";
echo "<td>".$row["id"]."</td>";
echo "<td>".$row["ug_school_id"]."</td>";
echo "</tr>";
}

//close the table
echo "</table>";

?>

Open in new window

0
 
VanHackmanCommented:
Sorry, line 7 should be:

$result_query = @mysql_query ($sql1);

also for the other table:

$sql2 = "SELECT uid FROM tags WHERE (ttype = 'hometown' AND tag = 'Cambridge, MA')";

and the script:
<?php

// Set the query
$sql2 = "SELECT uid FROM tags WHERE ttype = 'hometown' AND tag = 'Cambridge, MA'";

// Run the query
$result_query = @mysql_query ($sql2);

//Open the table
echo "<table>";


echo "<tr>";
echo "<td>uid for hometown = Cambridge, MA</td>";
echo "</tr>";

// Show a row for each row's result
while ($row = mysql_fetch_assoc($result_query))
{
echo "<tr>";
echo "<td>".$row["uid"]."</td>";
echo "</tr>";
}

//close the table
echo "</table>";

?>

Open in new window

0
 
anonwigAuthor Commented:
No this really wasn't what I meant, when I was referring to tables I meant database tables...

You had the right idea with the first (amended) response, just was hoping to achieve the same sql result using my two generated queries as the start point...
0
 
VanHackmanCommented:
Mmm...

Well honestly I'm not sure about what are you trying to do, but assuming that you want all the IDs, for members that match all the conditions for the first query and who also (assuming that uid = id from members table) and who also has a "hometown" tag equal to "Cambridge, MA" you could so something like:

$sql3 = $sql1." AND id IN(".$sql2.")";


0
 
anonwigAuthor Commented:
Will test it out tomorrow but that sounds perfect, sorry if I was unclear.

The reason for this seemingly bizarre query structure is the query is user generated and the generation is quite complex
0
 
VanHackmanCommented:
Ok, great!
Let me know if it works.
0
 
anonwigAuthor Commented:
One problem with that query, I need to have a hometown column in the db results...
0
 
VanHackmanCommented:
Mmm...

Probably:

$sql3 = $sql1." AND id IN(".$sql2.")";
$Sql4 = "SELECT uid as 'id',  tag as 'hometown' FROM tags where uid IN(".$sql3.")";

But that's too complex for my taste. I recommend you to write a whole new query or rewrite one of the 2 you have provided.

I will check for your reply tomorrow, to see if you get it to work.

bye!

0
 
anonwigAuthor Commented:
That's great - got me close enough to work with.

Will see if I can simplify it given the rest of the code.

Thanks.
0
 
anonwigAuthor Commented:
Thanks v much for persisting with this.
0
 
VanHackmanCommented:
You are welcome,

Also thank you for the points, I glad it help you.

Best regards,
VanHackman
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now