Getting data from one table based on results of another SQL PHP

Question

I know this involves JOINS but I can't seem to find a working solution to what I'm trying to do.

I have 2 custom tables :

table1    |    table2
---------------------
id             id
uid            uid
track_id       track_id
date           date
art            active
info
blah
blah2

First I want to select everything WHERE uid=55 AND active=1 from table2 :

$tracks = $wpdb->get_results( "SELECT * FROM table2 WHERE uid = 55 AND active = 1");

And then match the track_id from table2 with results from table1 so I can traverse the table1 data.

I know I can do it like this :

foreach( $tracks as $track ) {

$this_track = $track->track_id;

$results = $wpdb->get_results( "SELECT * FROM table1 WHERE track_id = $this_track");

// Do stuff here

}

But this is the part where it gets tricky...

I then want to ORDER the $results from table1 by date DESC from table2

And this is where I'm lost...

Effectively I want (pseudo code) :

$results = $wpdb->get_results( "SELECT * FROM table1 WHERE track_id = $this_track" ORDER BY date DESC FROM table2);

As well as that last bit, I know I can do this entire routine with JOINS to keep this all in one query and make it way more efficient but I just don't know how.

So just to be clear, my overall routine should be like this :

Get all instances of track_id from table2 where user_id=55 and active=1, then use those results to match the track_id to every result in table1 with the same track_id and then sort the results by date back over from table2

Psuedo code, I know it contains nonsense :

$finalresults = $wpdb->get_results( "SELECT * FROM table2 where uid=55 AND active=1 THEN SELECT * FROM table1 WHERE track_id = "the track_id from the first query" THEN ORDER BY date DESC FROM table2);

Rafael Rafael · Accepted Answer · 2016-01-31T02:04:55

Try with this query

SELECT t1.* ,t2.date AS t2date, t2.active FROM table2 AS t2 INNER JOIN table1 AS t1 ON (t1.track_id = t2.track_id) WHERE t2.uid=55 AND t2.active=1 ORDER BY t2.date DESC;

Edit: Explanation of what this query is doing. and inverted the order of the tables retrieved in the query (this don't affect the final datatset, i did this to make to follow the logic of the explanation.

1.- Begin with retrieving all rows from table2 (theres is no specific reason because i used table2 over table1, I'm only following an logical order), using the criteria that you specified iud=55 and active=1

SELECT * FROM table2 WHERE uid=55 AND active=1;

2.- but as you said you need to expand the data retrieved in table2 with some information in table1, that's exactly what it is the directive JOIN made, and we are using INNER JOIN because this type of JOIN will show rows ONLY if data for the uid=55 is present on table1, if there is NO data for the uid=55 present on both TABLES then mysql wil show empty the recordset (0 Rows selected).

in the ON(...) part I specify which criteria mysql will use to compara both tables for match in this case will compare that track_id on table2 it is the same that the specified on table1, if this codition is met then mysql considers it as a match.

anly for convenience and because i'm adding a Second table i gave an Alias to each one t1 and t2.

then the query now seems like this

SELECT * FROM table2 AS t2 INNER JOIN table1 AS t1 ON(t1.track.id = t2.track_id) WHERE t2.uid=55 AND t2.active=1;

3.- but then raise a problem, both tables has rows with the same field names, and this is something that DBMS don't like in their queries, to avoid this situation in the query i only show the fields (id, uid and track_id) from one table in this case t1 (t1.*) and only show the fields that doesn't have this problem from t2 (t2.date AS t2date, t2.active). in this way mysql won't throw any error.

SELECT t1.* ,t2.date AS t2date, t2.active FROM table2 AS t2 INNER JOIN table1 AS t1 ON (t1.track_id = t2.track_id) WHERE t2.uid=55 AND t2.active=1;

4.- for the final step i specify to mysql that i want all found rows ordered descent by a field in the table2;

ORDER BY t2.date DESC;

then this criteria will be applied to the whole selected rows. and the final query has this form.

SELECT t1.* ,t2.date AS t2date, t2.active FROM table2 AS t2 INNER JOIN table1 AS t1 ON (t1.track_id = t2.track_id) WHERE t2.uid=55 AND t2.active=1 ORDER BY t2.date DESC;

if is not completely clear you can ask ...

Getting data from one table based on results of another SQL PHP

1 Answers