I normally work with ColdFusion and SQL Server, but at the moment I'm doing
some PHP and mySQL work.
It's a simple internal order tracking system. I have a table for orders, and
a table for clients. Clients are in two levels: the parent company and the
particular branch. All client information is stored in the client table,
with a parentID linking parents and children. Client records with a parentID
of 0 are parents themselves. Pretty standard stuff.
I'm trying to display a list of orders, each with the names of the ordering
client branch and parent company. In SQL Server I would write the following
subquery:
SELECT order.orderID, client.clientname,
(SELECT B.clientname FROM client B WHERE B.clientID =
client.parentID) as parentname
FROM order, client
WHERE client.clientID = order.clientID
But we're using mySQL 3.23.49 which doesn't support subqueries. I found this
page, which says helpfully that "For MySQL versions prior to 4.1, most
subqueries can be successfully rewritten using joins and other methods":
http://www.mysql.com/doc/en/Subqueries.html. But my poor feeble brain can't
work it out :(
My simplified table structure is like this:
ORDER
orderID
clientID
CLIENT
clientID
parentID
clientname
Is this relationship possible to express without a subquery?
Thanks,
Kay.
_____________________________________________________________
Kay Smoljak Senior Developer/QA PerthWeb Pty Ltd
Level 9/105 St George's Terrace - Perth - Western Australia
Ph: (08) 9226 1366 Fax: (08) 9226 1375
http://www.perthweb.com.au http://developer.perthweb.com.au
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
