Jump to content

New to SQL - A little help needed.


tonydm
 Share

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...