Simple Question About Php Mysql Join

[Stalingrad]

Hello there. =] I have a simple question, which doesn't need any posting of code. I would like to use a PHP MySQL JOIN statement, or whatever it is called. I know it is called a JOIN, and would like to know something about it. My question is: Do two of the rows that you want to combine have to be the same exact name.. or can they have different row names? I actually have THREE tables, but will try to find a way to do this if JOIN can only involve two tables. Thank you. =]

[White_Lily]

If i remember rightly you can have different names for each row.

[Mahngiel]

Do two of the rows that you want to combine have to be the same exact name.. or can they have different row names?

 

Not at all. Have you looked at the manual or any examples to see the syntax?

 

JOIN `table_2` on `table_1.columnA` = `table_2.columnB`

 

Doing multiple table joins is as simple as adding another JOIN statement. I would advise you to practice with aliases as well.

Edited October 3, 2012 by Mahngiel

[Stalingrad]

Thank you.

[Jessica]

Also, it has nothing to do with PHP. There is no such thing as a PHP MySQL Join.

[Christian F.]

Mahngiel managed to botch the example a bit, as he's lacking a couple of backticks before and after the periods. Should have read like this:

JOIN `table_2` on `table_1`.`columnA` = `table_2`.`columnB`

Otherwise you're instructing MySQL that the period, and table name, is a part of the column name.

 

That said, you really should have searched the Internet for "mysql join tutorial". LOTS and lots of examples, and other detailed information, available from a number of resources. Including the MySQL manual.

[berridgeab]

In the SQL statement below there are two tables, tableA and tableB which I have aliased to A and B.

 

tableB contains a reference to tableA stored in the tableAId field. This references the primary key of tableA which is called id.

 

LEFT JOIN will attempt to join two tables together on them two fields.

 

As long as a record exists with an Id of 1 in tableA, then that row will always be returned. If a row in tableB on field tableAId matches id, then that row from tableB will be returned and joined to the end of tableA.

 

However if tableAId from tableB doesn't match id from tableA then all the fields from both tables are still returned, however all of tableA fields will be filled with data while all of tableB fields will be NULL.

 

$query =
"SELECT * FROM tableA as 'A'
LEFT JOIN tableB AS 'B'
ON B.tableAId = A.Id
WHERE A.id = 1";

 

A JOIN works in a similar manner, however a record must exist in both tableA and tableB for a row to be returned.

 

$query =
"SELECT * FROM tableA as 'A'
JOIN tableB AS 'B'
ON B.tableAId = A.Id
WHERE A.id = 1";

[Barand]

Mahngiel managed to botch the example a bit, as he's lacking a couple of backticks before and after the periods. Should have read like this:

JOIN `table_2` on `table_1`.`columnA` = `table_2`.`columnB`

Otherwise you're instructing MySQL that the period, and table name, is a part of the column name.

 

Unless the identifier in question is a MySQL reserved word or contains spaces (or other special chars) the backticks are completely unecessary and just serve to clutter the query.

 

An identifier may be quoted or unquoted. If an identifier contains special characters or is a reserved word' date=' you [i']must[/i] quote it whenever you refer to it. (Exception: A reserved word that follows a period in a qualified name must be an identifier, so it need not be quoted.)