Topic: [SOLVED] Several WHERE statements not returning data

[drranch]

I have a WHERE statement that pulls data from 4 different tables based on a date range... When I removed the "vc.follow_up_date I get information for the other follow_up_dates, but when I leave vc.follow_up_date in the query nothing is returned.  The vc.follow_up_date doesn't have any data in its table for this date range either.  When I put in data for this date range I get back the information.  Does any one know why this is?

Code: [Select]

WHERE hc.follow_up_date
BETWEEN DATE_SUB(CURDATE() , INTERVAL 7 DAY)
AND DATE_ADD(CURDATE() , INTERVAL 7 DAY)
AND hm.follow_up_date
BETWEEN DATE_SUB(CURDATE() , INTERVAL 7 DAY)
AND DATE_ADD(CURDATE() , INTERVAL 7 DAY)
AND ip.follow_up_date
BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY)
AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
AND vc.follow_up_date
BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY)
AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)";

[ManOnScooter]

I suggest if u mail the table structure-it would be easier to know wots wrong where

[fenway]

I suggest if u mail the table structure-it would be easier to know wots wrong where

We don't *mail* things on a forum, we post them in the original thread!

As for the OP, I would guess it a precendence issue... try this:

Code: [Select]

WHERE
( hc.follow_up_date BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() + INTERVAL 7 DAY )
AND ( hm.follow_up_date BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() + INTERVAL 7 DAY )
AND ( ip.follow_up_date BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() + INTERVAL 7 DAY )
AND ( vc.follow_up_date BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() + INTERVAL 7 DAY )

Of course, you should be using a sql string, not curdate() so that the query cache is utilized.

[drranch]

Hello Fenway,

When I use your query structure I get the same results that I got with my query structure.  I added each curdate() for each item one at a time and I get back information for each step but when I add the vc.follw_up_date I get blank; no information.  I know that the table/column vc.follow_up_date is blank and since its blank the query sends nothing back even though there is data for the other follow_up_dates.

I tried using "OR" instead of "AND" but I get back all follow-updates on all tables regardless of the "BETWEEN" structure.

[fenway]

What do you mean "is blank"? Like there's no row? Then you need a left join...

[drranch]

I don't think I'm on the right track...

I tried using the join statment, but it doesn't work. I think my table configuration is preventing me from using a single MYSQL query, so I broke down each of these queries and used a != if php statement to display when there are no appointments.

Thank you all for your assistance.

hc table
hcusername
hmnickname
hmfollow_up_date

hm table
hmusername
hmnickname
hmfollow_up_date
---------------
ip table
ipusername
ipnickname
ipfollow_up_date
----------------
vc table
vcusername
vcpnickname
vcfollow_up_date

All tables have data in their fields except the vc table where there is no data within the date range the mysql query is searching for.  When I leave the vc table in the query no data is returned, but the ip and hm tables do have data.  When I remove the vc table from the mysql query the data from hc table, hm table, and ip table is returned.  I'd like to see all data returned even when there isn't any data in either one of the tables for the queried date. 

*Solution seperated the query to individual queries and then displayed results on web page using != if php statment to state when there is no result show "there are no current appointments at this time" and if there are current appointments show the appointments from which ever table has an appointment for that time frame.

[fenway]

Well, I'm not sure why you're using AND... how are these tables related?

[drranch]

Each table represents a certain "type" of appointment, hence different tables.  On the users home page of the signed on user I want to display any appointment they may have on any of these four tables that is with in 7 days out from the current date.  So there may be times that they have an appointment for only one of these four tables or for all tables or multiple appointments for one of these tables.

Originally I was hoping for a single MYSQL query of all tables and then build a single PHP script to display it on the users home page.

[fenway]

Then you want to UNION each inidividual select.