[SOLVED] Complex SELECT

[dustinnoe]

I have this SQL

<?php
$ap_query = "SELECT users.username, users.user_id, ap.ap1, ap.date, qt.date AS tsl FROM users, ap, qt WHERE ap.ap2='$user_id' AND users.user_id=ap.ap1 
			AND qt.date=(SELECT qt.date FROM qt WHERE qt.user_id=ap.ap1 ORDER BY qt.date DESC LIMIT 1)
	     UNION
	     SELECT users.username, users.user_id, ap.ap2, ap.date, qt.date AS tsl FROM users, ap, qt WHERE ap.ap1='$user_id' AND users.user_id=ap.ap2
			AND qt.date=(SELECT qt.date FROM qt WHERE qt.user_id=ap.ap2 ORDER BY qt.date DESC LIMIT 1)
	     ORDER BY date DESC";
?>

 

It does everything I want except when the embeded SELECT statement doesn't return a row, I get nothing.  How do I make qt.date equal NULL or zero if the embeded SELECT returns no rows? I still need all the other data selected.

[fenway]

Sounds like you want EXISTS... but you're already written a correlated subquery, which is probably not a good idea.

[dustinnoe]

I'm still learning how to write complex SQL.  I need the user information and the last row they put into the table qt, if they have not posted a row to that table I still need the rest of the user information.  How else could I achieve this without subqueries?

[fenway]

Explain how these tables are related, and what you're trying to retrieve, and I'll see what I can do.

[dustinnoe]

OK! Taking away one join and the union will simplify this down to my problem. 

 

I have a one-to-many (users-to-qt) relationship.  I need to know how for each user I can select the last row they put into the table 'qt'. I need the result set to have three columns (users.user_id, users.username, qt.date) with users.user_id being unique.  Now this is where I have my problem.  If there is no row for a user in the table 'qt' I need qt.date to equal NULL in the result set.

 

I hope this clarifies it a little.

[dustinnoe]

OR

 

I could just INSERT a NULL row except for user_id into the table 'qt' when a user registers for a membership.  This way there will always be a row to SELECT when running this query.  Crude, but it would work.

[fenway]

OK! Taking away one join and the union will simplify this down to my problem. 

 

I have a one-to-many (users-to-qt) relationship.  I need to know how for each user I can select the last row they put into the table 'qt'. I need the result set to have three columns (users.user_id, users.username, qt.date) with users.user_id being unique.  Now this is where I have my problem.  If there is no row for a user in the table 'qt' I need qt.date to equal NULL in the result set.

 

I hope this clarifies it a little.

 

Ok... the way to approach this is as follows:

 

1) Deal with getting the last qt row for each user first -- if you didn't care about the users table, it would be a joke, right?

 

SELECT user_id, MAX( date ) AS date FROM qt GROUP BY user_id

 

2) Treat this as a derived table, and JOIN it to your users table... since users will be your "non-joined" table, you'll necessarily get them all, and you'll have have a date if there's a matching user_id in this derived table (since we'll be LEFT JOIN-ing):

 

SELECT u.user_id, u.username, qt2.date 
FROM  users AS u 
LEFT JOIN 
( SELECT user_id, MAX( date ) AS date FROM qt GROUP BY user_id ) AS qt2 ON ( qt2.user_id = u.user_id )

 

This will automagically put a NULL for any non-matching qt2 row.

 

I haven't tested this, but barring a brain cramp, it should work rather nicely... does that make sense?

[dustinnoe]

Got it! Here is my new statement

 

SELECT u.user_id, u.username, qt2.tsl 
FROM  users AS u, ap
LEFT JOIN 
( SELECT user_id, MAX( date ) AS tsl FROM qt GROUP BY user_id ) AS qt2 ON ( qt2.user_id = u.user_id ) 
WHERE ap.ap1='$user_id' AND u.user_id=ap.ap2
UNION
SELECT u.user_id, u.username, qt2.tsl 
FROM  users AS u, ap
LEFT JOIN 
( SELECT user_id, MAX( date ) AS tsl FROM qt GROUP BY user_id ) AS qt2 ON ( qt2.user_id = u.user_id ) 
WHERE ap.ap2='$user_id' AND u.user_id=ap.ap1

 

quite a bit different from the previous.  I had suspected it may have something to do with INNER JOIN but I have never understood JOIN well enough to put it to good use.  Looks like I have some homework.

 

Thanks!

[fenway]

Glad you got it working... be weary of the comma operator, a proper JOIN is much better.