Where do I put my WHERE clause?

[george]

I have this Select statement, and it does what I want.

SELECT p.propid, p.email, p.Dona, p.donationAmt, p.DonNote, p.numOfClasses, p.eLastDay, p.propDate, p.VenuName, p.instruc, CONCAT(p.POC_First, ' ',p.POC_Last) as cName, p.POC_Email, p.eventStage, p.eventStatus, p.OtherExp, p.venuInvReq, p.Notes, p.PS2Host, p.startDate, v.attendance FROM proposal p LEFT JOIN VenuName v ON p.VenuName = v.VenuName ORDER BY p.propDate DESC;

But now I want to add a WHERE clause. When I put ti before the ORDER BY clause, the query returns no results. If I put it after the first table mentioned, I get an error.

SELECT p.startDate, v.attendance FROM proposal p WHERE venuInvReq = '1' LEFT JOIN VenuName v ON p.VenuName = v.VenuName ORDER BY p.propDate DESC;

It is the first table that I need the where clause. Do I need a sub query for my WHERE clause?

[justsomeguy]

You can put a condition like that either as a join condition or in a WHERE clause before the ORDER BY. It might be slightly more efficient to have it as a join condition. I believe that would be like this:

SELECT p.propid, p.email, p.Dona, p.donationAmt, p.DonNote, p.numOfClasses, p.eLastDay, p.propDate, p.VenuName, p.instruc, CONCAT(p.POC_First, ' ',p.POC_Last) as cName, p.POC_Email, p.eventStage, p.eventStatus, p.OtherExp, p.venuInvReq, p.Notes, p.PS2Host, p.startDate, v.attendance FROM proposal p LEFT JOIN VenuName v ON p.VenuName = v.VenuName AND p.venuInvReq = '1' ORDER BY p.propDate DESC;

The WHERE clause would look like this:

SELECT p.propid, p.email, p.Dona, p.donationAmt, p.DonNote, p.numOfClasses, p.eLastDay, p.propDate, p.VenuName, p.instruc, CONCAT(p.POC_First, ' ',p.POC_Last) as cName, p.POC_Email, p.eventStage, p.eventStatus, p.OtherExp, p.venuInvReq, p.Notes, p.PS2Host, p.startDate, v.attendance FROM proposal p LEFT JOIN VenuName v ON p.VenuName = v.VenuName WHERE p.venuInvReq = '1' ORDER BY p.propDate DESC;

If it's not returning rows, make sure the data types match. Don't put the 1 in quotes if the field is a number field, a 1 in quotes is a string, not a number.

[george]

Well, it still wasn't working when I discovered that all my table values for the column I was placeing a condition on were all NULL! So, I put in value, and try again. Now it works! Thanks again

[george]

gRRR will not work inside the php script.

SELECT p.propid, p.email, p.Dona, p.donationAmt, p.DonNote, p.numOfClasses, p.eLastDay, p.propDate, p.VenuName, p.instruc, CONCAT(p.POC_First, ' ',p.POC_Last) as cName, p.POC_Email, p.eventStage, p.eventStatus, p.OtherExp, p.venuInvReq, p.Notes, p.PS2Host, p.startDate, v.attendance FROM proposal p LEFT JOIN VenuName v ON p.VenuName = v.VenuName AND p.venuInvReq = 1;

Regardless if the p.venuInvReq is set to 1 or '1', the query returns all items in set. This simple query will work.

SELECT * FROM proposal WHERE venuInvReq = '0';

And again, it works both with or without quotes around the number.

[justsomeguy]

Try to use the version with the WHERE clause instead of the version with the join condition. I thought they would be the same but the left join might be doing something else.

[george]

That did it. Thanks again.