Solved

SQL join 2 queries

Posted on 2011-02-20
17
840 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
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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…

815 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

12 Experts available now in Live!

Get 1:1 Help Now