how to select data from 3 mysql tables?

[langenf]

Hi,

let us say that there are 3 tables clients, purchase, products.

The page displays the information about the client such as name, client number

and products purchased (if any) and also links to edit or delete client information.

purchase table has purchaseid, clientid , productid etc.

I  try to display all clients that are there along with the purchases they have made

with a query like

 SELECT  * FROM clients, purchase
where clients.clientid  = products.clientid  innerjoin (select * from products where productid=purchase.productid)

please say where the query is wrong.

[Psycho]

...purchase table has purchaseid, clientid , productid etc.

 

That doesn't make sense to me. I would think the purchase table would have a foreign key for clients, but not a foreign key for products. The products table should have a foreign key for purchases.

 

A single purchase may have multiple products, correct? how are you storing the product id's in a single purchase record?

 

I would think the query should look something like this

 

SELECT *
FROM clients
  LEFT JOIN purchases ON clients.client.id = purchases.clientid
  LEFT JOIN products ON purchases.purchaseid = products.purchaseid

[langenf]

hi mjdamato,

thanks you very much.

I have to admit its an application that deals with software licences so I wanted to say purchase to in order to be secretive. To spill the beans, there 3 tables clients, licences and (software)products.

The page displays information about the client such as name, client number

and licences purchased. license table has licenseid, clientid , productid etc.

each client may purchase licenses for several products. Apparently , the client

buys only one license for a particular product. You are right,the table design could be wrong...

The query you suggested repeats the client information for every licence. please allow me to draw a small illustration that shows you how the page would look like

client name  	client number	Email / URL                    licences 	                            edit information
------------------------------------------------------------------------------------------------

client1              2342                    client@company.com        licence1,productname etc             edit 
                                                                                     licence2,productname etc              


client2             62677                 client2@company2.com        licence1,productname etc             edit

as you can see, the  information for client1 here is not repeated eventhough he has purchased 2 licences. Please say how to achieve this?