Solved

SQL join 2 queries

Posted on 2011-02-20
17
834 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
  • 9
  • 8
17 Comments
 
LVL 11

Expert Comment

by:VanHackman
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks - Is there any way of working with the existing queries? even if it isn't as efficient?
0
 
LVL 11

Expert Comment

by:VanHackman
Comment Utility
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
Comment Utility
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
Comment Utility
(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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:anonwig
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Ok, great!
Let me know if it works.
0
 

Author Comment

by:anonwig
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks v much for persisting with this.
0
 
LVL 11

Expert Comment

by:VanHackman
Comment Utility
You are welcome,

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

Best regards,
VanHackman
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Read about achieving the basic levels of HRIS security in the workplace.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now