Two tables with relationship how to query one record

[Waddy]

Hi,

 

I have two tables, clients and options. In MySQL I have created a relationship, primary key is ID in clients table, Foreign key = client_ID in table options.

 

I can insert using mysql_insert_id(); as the value for client_ID in options.

 

This all works fine. Client ID matches ID

 

My question is, how can i perform a select query to get the client details from table client and also the options values from the option table? Preferably using the matching ids... I have looked and tried many things and would appreciate a prod in the right direction, best method...

 

In a nutshell i want to look at the client details and also see the options they picked.

 

[fenway]

What's wrong with a JOIN?

[Waddy]

Thanks, thats what i was looking for, couldnt find and easy example to follow and wasnt sure what it was called.  Kate helped me in IRC. (thanks Kate)

 

I have made the query for the two tables and it works fine. Did a three table query and it worked also.

 

Example:

 

$query = "SELECT client.ID, order.client_ID ".

"FROM client, order ".

"WHERE client.ID = order.client_ID";

 

Edit: added query for others benefit.

[behz4d]

Hi Waddy,

I have exactly the same problem, can you explain this more since I haven't figured it out yet ?

I am newbie to MySql, and I even have questions about relationships between tables

see, we have two tables: Clients, Services

and we want to show a specific client services matching by ID !

how we should relate these to tables to each other ?

and then how to query ?

 

Thank you in advanced 

[Waddy]

Hi, I found it hard to find the information to achieve this, suprised me.

 

This is how i achieved the result.

 

On one (clients) table have an auto increment row (id), use this as your primary key. Create another table say 'orders', add a row called client_ID then you need to create a foreign key on this table linking to the client_id.

 

ALTER TABLE ORDERS

ADD FOREIGN KEY (client_id) REFERENCES CLIENTS(ID);

 

On your client insert grab the ID the client gets, use that to insert into order (client_ID), that will give both the same ID for when you use the above query.

 

"WHERE client.ID = order.client_ID";

 

The result gives you the client and order related to the client.

 

I hope that helps.