nest query problem please help

[geroido]

Hi all

I'm trying to query the results of a select statement. The inner select is working perfectly and returning the result set. However, I want to query this result set and only get  distinct records (with a distinct 'Ordernum') as there are several records returned with the same Ordernum value and I expect this. For example, there are several rows retrieved with an Ordernum of 621 but I only want one of these rows displayed. Can you help. Is it a syntax problem?

 

$query = "select DISTINCT(orderdetails.Ordernum), orderdetails.MenuItemID, orderdetails.CustFName, 
orderdetails.CustSName, orderdetails.StreetAddr, orderdetails.Town, orderdetails.County, 
orderdetails.orddate from 
(select orderdetails.Ordernum, orderdetails.MenuItemID, orderdetails.CustFName, 
orderdetails.CustSName, orderdetails.StreetAddr, orderdetails.Town,  orderdetails.County, 
orderdetails.orddate FROM orderdetails join menuitemdetails on orderdetails.MenuItemID=menuitemdetails.MenuItemID 
where menuitemdetails.MenuItemID = orderdetails.MenuItemID ) order by orderdetails.CustSName asc"; 

[fenway]

DISTINCT is not a function -- you likely want GROUP BY... but you may need to join on that dervied table.

[geroido]

Hi fenway

Thanks for the help. I've tried GROUP BY - it's still not working. Any other thoughts?

 

$query = "select DISTINCT(Ordernum), MenuItemID, CustFName, 
CustSName, StreetAddr, Town, County, 
orddate from orderdetails where orderdetails.Ordernum =
(select orderdetails.Ordernum, orderdetails.MenuItemID, orderdetails.CustFName, 
orderdetails.CustSName, orderdetails.StreetAddr, orderdetails.Town,  orderdetails.County, 
orderdetails.orddate FROM orderdetails join menuitemdetails on orderdetails.MenuItemID=menuitemdetails.MenuItemID 
where menuitemdetails.MenuItemID = orderdetails.MenuItemID ) group by orderdetails.Ordernum "; 

[geroido]

Hi

Just to explain what I'm trying to do:

 

This is driving me crazy. Let me explain what I want a little better. I have two tables

Table 1 (orderdetails)

Ordernum, MenuItemID, CustFName, CustSName, StreetAddr, Town, County

 

Table 2 (menuitemdetails)

ClientID, MenuItemID, MenuItemName etc.

 

So heres the problem:

 

I have a client who logs in and I save there clientid in a session variable called $S_SESSION['userid'];. They can click a button and view all of the customers who have placed orders with them. So I want the query to scan through orderdetails table selecting all records that have a matching MenuItemID in menuitemdetails which has a ClienID = $S_SESSION['userid'];. This means that only orders for this particular client will be retrieved. Can you please help? I.ve been trying this:

 

$query = "select DISTINCT(orderdetails.Ordernum), orderdetails.MenuItemID, orderdetails.CustFName,
orderdetails.CustSName, orderdetails.StreetAddr, orderdetails.Town,  orderdetails.County,
orderdetails.orddate, menuitemdetails.ClientID FROM orderdetails  join menuitemdetails on orderdetails.MenuItemID=menuitemdetails.MenuItemID
where ClientID = '".$S_SESSION['userid']."' group by CustFName ";

[fenway]

Hi fenway

Thanks for the help. I've tried GROUP BY - it's still not working. Any other thoughts?

 

$query = "select DISTINCT(Ordernum), MenuItemID, CustFName, 
CustSName, StreetAddr, Town, County, 
orddate from orderdetails where orderdetails.Ordernum =
(select orderdetails.Ordernum, orderdetails.MenuItemID, orderdetails.CustFName, 
orderdetails.CustSName, orderdetails.StreetAddr, orderdetails.Town,  orderdetails.County, 
orderdetails.orddate FROM orderdetails join menuitemdetails on orderdetails.MenuItemID=menuitemdetails.MenuItemID 
where menuitemdetails.MenuItemID = orderdetails.MenuItemID ) group by orderdetails.Ordernum "; 

What does this produce?