Solved

SQL join 2 queries

Posted on 2011-02-20
17
849 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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