New to SQL - A little help needed.

[tonydm]

Hello,I'm really very new to SQL (using MYSQL) and am trying to learn as much as I can as quickly as I can. I am working on a project that perhaps was a bit premature for my knowledge level of SQL, but that's my typical MO, jump in a work my way out. So here's my trouble. To keep it simple I have an Orders, Orders_Status, and Supers table. The relationship is this, for each individual "order" the Orders table will contain a key to a single record in the Order_Status table. The Orders table will also contain a key to a relevant record in the Supers table. Example[Tables] - Snip of fieldsOrders: OrderID (AutoInc/Unique Key) OrderStatusID SuperID InvoiceNumber LotNo ShipToAddr Open (TinyINT(1))Orders_Status: OrderStatus ID (AutoInc/Unique Key) Field1 Field2 Etc....Supers: SuperID (AutoInc/Unique Key) Supers_NameWhat I want to do is print a report on all the Orders that are open with the Order_Status info and also filter on SuperIDReport:SuperID=2SuperName=TonyOrders For Super = Tony-------------------------------------InvoiceNo, LotNo, ShipToAddr, Field1, Field2, Etc...Here's what I have so far.

SELECT o.InvoiceNumber, os.* FROM orders AS o, orders_status as os WHERE o.superid=2 and o.openclosed = 0;

I'm getting 70 records returned. There are only 14 records in the Orders table, 14 records on the Orders_Status table (1 record for each order), and 2 records in the Supers table. If I haven't provided enough info, more on request. Thank you all in advance for any help you may be able to give me.MySQL v4.1.11Server Debian Sarge 3.1MySQL Query Browser 1.1.19Windows XP SP2

[tonydm]

With a bit more playing around I came up with this that works!

SELECT o.InvoiceNumber, o.Job_Lot_Number, os.*, lst.*FROM orders as o inner join orders_status as os on o.orderstatusid=os.orderstatusid inner join lot_status_types as lst on os.lotstatusid=lst.lotstatusidwhere o.superid=2;

But know doubt I will have many more questions as I gain just enough knowledge to be dangerous.Thanks again. Any comments are very welcome.tonydm

[tonydm]

Ok, I knew I wasn't threw. The following SQL statement (minus) the GROUP BY clause returns the result set I'm looking for. However, when I add the GROUP BY clause, my result set goes from 9 records to 2.CustomerID is an INTEGER field in the Orders table which contains the Key value of the Customer record in the Customers table.So I have this query which I want to group by customer.

SELECT o.InvoiceNumber, o.Job_Lot_Number, o.ShipToAddr1, os.*, lst.*FROM orders as oinner join orders_status as os on o.orderstatusid=os.orderstatusidinner join lot_status_types as lst on os.lotstatusid=lst.lotstatusidwhere o.superid=2 and o.closedopen=0GROUP BY o.CustomerIDORDER BY LPAD(o.Job_Lot_Number,50," ");

What am I missing here?tonydm

[Rachit Shah]

Plz provide exact relationship between the tables.What you have mentioned in subsequent replies does not clarify the tables and relations.Like lot_status_types is not mentioned anywhere except in the code.I suppose U have to use outer join. Plz paste the code as well table description, I may help you.