Requesting a dumbed-down mysql join query, explained

badgoat

Hi!

I have needs to make a mysql query, pulling data from two different tables. I have Googled for the syntax, and I have found sites that do it, but none that I have found give a nuts-and-bolts, layman's term explanation, which I unfortunately need. Could someone please post one and explain it to this PHP/MySQL nooblet?

tomhath

There are lots of ways to get data from two tables. How do the rows in table 1 relate to the rows in table 2?

For example if you have a table of people and a second table of phone numbers, you need some way to match up the phone numbers that belong to each person. So suppose you have a column called "pid" in both tables; the pid of a person row is also in every phone row for that person:

select person.name, phone.number
from person
join phone
on person.pid = phone.pid

nemonoman

A join creates a 'virtual' table from 2 tables.

It does this by combining the rows from one table with the rows from another table.

For a join to work as expected, there needs to be a 1-1 relationship between the rows.

Example:
Customer, Order

Each order has a customer

SELECT order.id, customer.name
FROM order, customer
WHERE order.id=123
AND order.customerid=customer.id

In effect, this says:
Find order id 123
find its associated customer id
find the name of the customer with that ID

the select criteria
order.customerid=customer.id

is what creates the binding of these 2 tables.

(Another way of doing EXACTLY the same thing:

SELECT order.id, customer.name
FROM order
INNER JOIN customer
ON order.id=customer.id
WHERE order.id=123 )

You can keep adding tables to the join

SELECT order.id, customer.name, ordertype.name
FROM order, customer, ordertype
WHERE order.id=123
AND order.customerid=customer.id
AND order.typeid = ordertype.id

(Another way of doing EXACTLY the same thing:

SELECT order.id, customer.name, ordertype.name
FROM order
INNER JOIN customer
ON order.id=customer.id
INNER JOIN ordertype
ON order.typeid=ordertype.id
WHERE order.id=123 )

etc.

nemonoman

In the example above:
What if this order were for an unmadeup customer?

Then the join would return no rows.

WHAT???

No really: Think about why...
a virtual row is being created...
and if part of the virtual row isn't there...
then the whole row is useless.

Sometimes, however you WANT a 'useless' row.

In this case you use a 'LEFT JOIN', or 'OUTER JOIN'. You say, in effect...
if there is no data for part of this virtual row, I don't care...Show me the ORDER info, but if there's no customer, just leave the customer info blank....

SELECT order.id, customer.name
FROM order
LEFT JOIN customer
ON order.id=customer.id
WHERE order.id=123

Get it??

badgoat

Hi! Thanks very much for the answers above. The overview of my tables is such: One table holds salesperson information (name, phone, cell, email) The 2nd table contains some basic order parameters, and has the name of the salesperson in a dropdown menu.

What I would like, is when I select the name of the salesperson in the dropdown menu for all the other information (phone, cell, email) to be displayed when I submit the order.

Now, I will go ahead and read the above answers more closely and see if the answer I need lies within.. For those who are more knowledgeable than I with this, and my response has clarified my needs, please feel free to point me along the right way!

Thanks again!

Craig the nooblet.

nemonoman

Son, you are trying to move from the highly simple theoretical to the often much more complex practical. Are trying to stand on others shoulders to see your way? It would be best if you grew some yourself through actual study.

badgoat

No, I am doing this all on my own. I have taken this project from a sound idea to an actual page that works! I started small, grew on the idea until it was nearly complete. There are just minor things now, little tweaks which will make my project (temporarily) complete.

I wish I had the luxury to sit down and learn every nuance of mysql and php.. But I work in corporate America 60+ hours a week, plus have a family. This is a shotgun session 😉

badgoat

I should clarify a little..

Page 1 is where I enter a vew variables into text boxes, select a salesperson from a drop down menu, and click submit.

Page 2 displays the little order and salesperson name. The goal is to also display the other information which is saved into the salesperson table, such as phone, cell, and email address.

I know it is a rather simple thing, but I do not know it yet.

Reading the above now that I am at work and have all my code in front of me!