Page 1 of 1

Complicated query

Posted: Tue Jul 06, 2004 5:20 pm
by dstefani
Hello,

My goal is to take the results of a query and dump it into a temporary table for use and abuse.

My query starts off as a user defined search, looking for an ID (index).

Code: Select all

SELECT * FROM t1 WHERE t1_id = 'search_criteria'
THen what I was doing was during the 'while' loop with the recordset I was querying the other two table that has some (not all) records that match foriegn keys from t1

Code: Select all

SELECT * FROM t1 WHERE t1_id = 'search_criteria'
while(looping through t1 recordset) {
      SELECT * FROM t2 WHERE t2_id = t1_t2_id
       while(){};

       SELECT * FROM t3 WHERE t3_id = t1_t3_id
        WHILE(){};
}
While looping through t1's recordset the next 2 SELECTS are on the same level so to speak, in that I don't do my third SELECT inside the second SELECTS while loop.

Can I do this as a JOIN?

The reason I want to do it in one query is to be able to easily make a temp table from the resulting recordset.

Any suggestions?

Thanks in advance!

-dstefani

Posted: Tue Jul 06, 2004 5:28 pm
by Weirdan

Code: Select all

SELECT 
  * 
FROM 
  t1 
LEFT JOIN
  t2
ON
  t2.t2_id = t1.t1_t2_id
LEFT JOIN
  t3
ON
  t3.t3_id = t1.t1_t3_id
WHERE 
  t1.t1_id = 'search_criteria'
I think it's what you're looking for.

Posted: Tue Jul 06, 2004 6:23 pm
by dstefani
This is really close. Except that t1 has 70 records that match and the resulting mysql_num_rows gives me 1. I appreciate you help.

Here is the actual query I'm using:

Code: Select all

$sql = "SELECT * FROM futures_transactions 
	    LEFT JOIN wine_list ON wine_list.wl_id = futures_transactions.ft_wn_id 
	    LEFT JOIN customer_info ON customer_info.cs_id = futures_transactions.ft_cs_id 
	    WHERE futures_transactions.ft_id = '$search_wn_id'";
I should have 70 records returned for a search on $search_wn_id '18' each of which is a transaction that has the ID of a wine, who's details I need from the wine_list table. It also has a customer ID the details of which I need from the customer_info table. I'm putting this detail here in case I didn't explain myself clear enough last time. Again, I really appreciate your help..
-dstefani

Posted: Tue Jul 06, 2004 6:28 pm
by dstefani
My Fault!

The last WHERE clause: wrong column futures_transactions.ft_id should be futures_transactions.ft_wn_id

It works great.

Thank you very much.

-dstefani