Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

PHP/MySql - INNER JOIN from another query

Posted on 2003-11-07
5
Medium Priority
?
2,145 Views
Last Modified: 2008-03-10
Hi!
  I would like to know how i could achieve a query with the result of another query.. here is my example:

$sql = "SELECT tblranking.IdMembre, Max(tblranking.myDate) AS MaxOfmydate FROM tblranking WHERE Type='DOUBLE' GROUP BY tblranking.IdMembre";
      $result = SQL_Query($sql);

      $sql2 = "SELECT tblranking.IdRanking, tblranking.IdMembre, tblranking.myDate, tblranking.Points FROM tblranking INNER JOIN $result ON (tblranking.IdMembre = $result["IdMembre"]) AND (tblranking.myDate = $result["MaxDemydate"]) ORDER BY Points DESC";
      $result2 = SQL_Query($sql2);

my first query get the ranking up-to-date and the second get the points each member get and i sort it to get the one who has the more points.

The problem is that i don't know how to make the INNER JOIN $result ON ... i know result is an array, but is there a way to make it work?

Oh and if you happen to know how i can test it in PhpMyAdmin, it would be awesome!

It's gonna help me alot for future joins using MySql.

Thanks in advance.
0
Comment
Question by:PUB_UL
  • 2
  • 2
5 Comments
 

Expert Comment

by:temporo
ID: 9704596
I have not done much php but could you get both the answers from the database into the arrays.

then use a function with loops to sort out the rank of each of the member depending on the number of points.
0
 

Expert Comment

by:temporo
ID: 9704620
here is an extract from a mysql manual:

Note that in versions before Version 3.23.17, the INNER JOIN didn't take a join_condition!

so are you using the version before that?

temporo
0
 
LVL 1

Author Comment

by:PUB_UL
ID: 9704630
I would prefer to know how i could do it using two queries because i'm gonna work on a stats thing and i know i'm gonna need to do similar queries...

I thought of your suggestion and it would work, but it ain't what i am looking for.

Thanks for you help, still a good suggestion :)
0
 
LVL 1

Author Comment

by:PUB_UL
ID: 9704651
I can do a INNER JOIN, but it's from the result of another query, not another table. That's the problem.

I could make a temporary table too using a SELECT... INTO, but i wanna know if i could make it work using the way i showed above.

Maybe it ain't possible, but if it is, i assume there is someone out there who could help me out with it.

Thanks again!
0
 

Accepted Solution

by:
wjdashwood earned 500 total points
ID: 9716050
I think the inner join isn't the way to go.
The only way I can see it working is to do it is by using a select statement within a where command. So you would have something like

 $sql2 = "SELECT tblranking.IdRanking, tblranking.IdMembre, tblranking.myDate, tblranking.Points FROM tblranking
WHERE tblranking.IdMembre=(SELECT  tblranking.IdMembre FROM tblranking WHERE Type='DOUBLE' GROUP BY tblranking.IdMembre)

but that will need a fair bit of work! Its just there to give you an idea of what I mean. I think with that method you can only get one value in the where command, so you will have to get max from the original select statement.

I'm certain it is possible. It may help to write out what you want in english and then try to work out how to do it in sql.

As for the testing in phpmyadmin any straight sql can be entered and run but if you are using variables or results of other commands then you will have to substitute the value to test it.

Hope that helps a bit. I'm a bit out of touch with my sql at the moment, otherwise I would have had a go at writing the code for you.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Australian government abolished Visa 457 earlier this April and this article describes how this decision might affect Australian IT scene and IT experts.
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

782 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