Hard mySQL query problem

Question

Hey all, I am up against a big problem with trying to figure out how to go about this query I am needing.

I need to only display the companies that the user has in their account and also only display the company if they are on the calendar for that day.

My userinfo database is this (only showing needed table info):

ID | UserID     | idNumber  | theAccount
----------------------------------------
2  | 5556120012 | NA        | MAIN
6  | 5556120012 | 5680012   | SUB

The Company database is this (only showing needed table info):

ID | idNumber   | theDateStart | theDateEnd
-------------------------------------------
2  | 5680012    | 2010-06-02   | 2010-06-03
6  | 56057477   | 2010-06-01   | 2010-06-03

The SQL Query I have right now works fine at picking the dates that are today for each company.

SELECT clientinfo.id, clientinfo.theCompName, clientinfo.theURL, 
clientinfo.picURL, clientinfo.theOwnerFN, clientinfo.theOwnerLN, 
clientinfo.storeNum, clientinfo.compAddress, clientinfo.compAddressC, 
clientinfo.compAddressS, clientinfo.compAddressZ, clientinfo.accountStats,
clientinfo.idNumber, IFNULL(clientoffers.idNumber, 'NA'), 
IFNULL(clientoffers.theDateStart, 'NA'), IFNULL(clientoffers.theDateEnd, 'NA')
FROM clientinfo 
LEFT JOIN clientoffers ON (clientinfo.idNumber = clientoffers.idNumber AND '2010-06-03' 
BETWEEN clientoffers.theDateStart AND clientoffers.theDateEnd), userinfo 
WHERE clientinfo.accountStats = 'OPEN' 
AND IFNULL(clientoffers.idNumber, 'NA') <> "NA" 
GROUP BY clientinfo.idNumber 
ORDER BY clientinfo.theCompName ASC

That shows all that have current things on the calendar for each company. Where the problem comes into play is I don't know where to add the userinfo in that query to make sure that user has a company in their account (in order to see stuff on the calendar, the user has to add the company to their account).

So, as the example says above, the user only has company 5680012 in their account so therefore, only that company needs to be displayed in the query. If they had more than one then it should display that many.

On the userinfo db, the column "idNumber" is each company's id number. So in the example above, there is only one company in that user's account (the first one is just a general "MAIN" account not associated with any company.)

As you can see, it's a pretty hard query (at least for me) so I am hoping someone can help me out with it!

David

Answer 1

From what I can see there is only a one way relationship between userinfo and company. From the tables you provided there is no way to check if the dates are unique to a user or if they correspond to more than one person. Is this intentional or a design flaw?

Answer 2

I see you are using GROUP BY, without any aggregate functions. Most of the columns in the resultset are not mentioned in the group by clause, and according to mysql documentation, the values returned for these columns are indeterminate (http://dev.mysql.com/doc/refman/5.1/en/group-by-hidden-columns.html). Anyway, if the results are what you need, it's ok.

I've completed your join with userinfo table by adding "and userinfo.idNumber = clientinfo.idNumber" to the where clause. It should do, if I correctly understood your database scheme.

SELECT clientinfo.id, clientinfo.theCompName, clientinfo.theURL, 
clientinfo.picURL, clientinfo.theOwnerFN, clientinfo.theOwnerLN, 
clientinfo.storeNum, clientinfo.compAddress, clientinfo.compAddressC, 
clientinfo.compAddressS, clientinfo.compAddressZ, clientinfo.accountStats,
clientinfo.idNumber, IFNULL(clientoffers.idNumber, 'NA'), 
IFNULL(clientoffers.theDateStart, 'NA'), IFNULL(clientoffers.theDateEnd, 'NA')
FROM clientinfo 
LEFT JOIN clientoffers ON (clientinfo.idNumber = clientoffers.idNumber AND '2010-06-03' 
BETWEEN clientoffers.theDateStart AND clientoffers.theDateEnd), userinfo 
WHERE clientinfo.accountStats = 'OPEN' 
and userinfo.idNumber = clientinfo.idNumber
AND IFNULL(clientoffers.idNumber, 'NA') <> "NA" 
GROUP BY clientinfo.idNumber 
ORDER BY clientinfo.theCompName ASC