[SOLVED] Cannot get the correct query results need help

[geroido]

Hi

This is further to a post I did yesterday but still not working. I'll just explain a little further.

 

I have two tables which look I represent below(simplified for this example). The important field is MenuItemID. What I want to do is search through the tables using a clientID and extract all orders from orderdetails that are for a particular clientID only. For example, OrderNum 1 has MenuItemID 69 and 67. These two can only belong to clientID CLS_carey4780bc7a6e1bc. So if I have the clientID, I want to get every order that relates to that clientID only. OrderNum 2 has MenuItemID 71 and 80 and these can only belong to CLS_owen4785f5b9ce484 etc. You can see that CLS_patrick3425dc4b9sd has no orders. I've tried the following sql statement but it is getting every record regardless of the clientID. Can you help?

 

 

$query = "SELECT orderdetails.Ordernum, SUM(orderdetails.itemcost * orderdetails.ItemQuantity) as mytotal, 
orderdetails.CustFName, orderdetails.CustSName, orderdetails.StreetAddr, orderdetails.Town, orderdetails.County, 
orderdetails.TelNum, orderdetails.orddate, ClientID  from orderdetails left join menuitemdetails on 
orderdetails.MenuItemID=menuitemdetails.MenuItemID  
and ClientID = '".$_SESSION['clsrevid']."' group by orderdetails.Ordernum ";

 

The clientID is held in $_SESSION['clsrevid'] and could be any clientID.

 

 

 

menuitemdetails

 

clientID                                      MenuItemID                              MenuItemName

CLS_carey4780bc7a6e1bc                  69                                            chicken pie

 

CLS_carey4780bc7a6e1bc                  67                                              Soup

 

CLS_owen4785f5b9ce484                    71                                              Pizza

 

CLS_owen4785f5b9ce484                    80                                              Apple pie

 

CLS_patrick3425dc4b9sd                    91                                              Vol au vents

 

 

 

orderdetails

 

OrderNum                                      MenuItemID                                  CustFName

 

    1                                                  69                                                  John

 

    1                                                  67                                                  John

 

    2                                                  71                                                  mary

 

    2                                                  80                                                  mary

 

[geroido]

Hi

 

I have managed to find the problem. It was mostly unrelated to the sql. Sorry, but it appears that the $_SESSION variable I was using to store the clientID had some white space in it so it couldn't find the record. I used the trim function to clean it up and hey presto alls well. The left join instead of inner join was also part of the problem.

 

 

 

Thanks