Solved

SQL join 2 queries

Posted on 2011-02-20
17
846 Views
Last Modified: 2012-05-11
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
Comment
Question by:anonwig
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 
LVL 11

Expert Comment

by:VanHackman
ID: 34940344
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
 
LVL 11

Expert Comment

by:VanHackman
ID: 34940350
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
 

Author Comment

by:anonwig
ID: 34940379
Thanks - Is there any way of working with the existing queries? even if it isn't as efficient?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 11

Expert Comment

by:VanHackman
ID: 34940398
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
 

Author Comment

by:anonwig
ID: 34940410
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
 

Author Comment

by:anonwig
ID: 34940411
(The reason for the separate table is that there might be multiple rows in the tags table with the same uid)
0
 
LVL 11

Expert Comment

by:VanHackman
ID: 34940438
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
 
LVL 11

Expert Comment

by:VanHackman
ID: 34940453
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
 

Author Comment

by:anonwig
ID: 34940461
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
 
LVL 11

Expert Comment

by:VanHackman
ID: 34940489
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
 

Author Comment

by:anonwig
ID: 34940499
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
 
LVL 11

Expert Comment

by:VanHackman
ID: 34940536
Ok, great!
Let me know if it works.
0
 

Author Comment

by:anonwig
ID: 34940565
One problem with that query, I need to have a hometown column in the db results...
0
 
LVL 11

Accepted Solution

by:
VanHackman earned 500 total points
ID: 34940597
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
 

Author Comment

by:anonwig
ID: 34942162
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
 

Author Closing Comment

by:anonwig
ID: 34942171
Thanks v much for persisting with this.
0
 
LVL 11

Expert Comment

by:VanHackman
ID: 34945597
You are welcome,

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

Best regards,
VanHackman
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question