Joining Tables

karenn1 · October 11, 2007

Can someone please help me? I have five tables with several fields each and values stored within the fields. I want to have a page that displays a list with values taken from one field in each table. How do I join fields from different tables together and display it in one list? I'm assuming a while loop would be needed to display all fields in the table but how do I throw it all together?

Thanks,

Karen

yzerman · October 11, 2007

You would probably want to do something like this:

<?php
$query = "SELECT table1.fieldname, table2.fieldname, table3.fieldname, table4.fieldname, table5.fieldname FROM table1, table2, table3, table4, table5;";
$result = mysql_query($query);
if(!$result) {
echo mysql_error($query);
} else {
  print_r($result);
}
?>

Barand · October 11, 2007

Had yzerman got the output bit right then, if you have 10 records in each table, his query would return 10,000 rows.

To join tables you need key fields in each table so you can JOIN those with matching values in those fields

What is the structure of those 5 table?

yzerman · October 11, 2007

Had yzerman got the output bit right then, if you have 10 records in each table, his query would return 10,000 rows.

To join tables you need key fields in each table so you can JOIN those with matching values in those fields

What is the structure of those 5 table?

Yeah i'm a noob when it comes to joins. Usually when I use a query like the one above, I have a where clause. Is there actually a better way to do this?

Like say table1.field1 was a userid.

How would I go about getting email from table 2, the friends list from table 3, the school info from table 4, and the user info from table 1, where all tables have the userid (in table#.field1) as the common link? (BTW Barand, thats what I use EXPLAIN for )

Barand · October 11, 2007

here's an example

[pre]

customer order orderitem product category

--------- ------- ---------- --------- ----------

cust_id --+ ono --+ id +---- prod_id +--- cat_id

cust_name | ord_date +-- ono | prod_name | category

+-- cust_id prod_id ---+ price | tax_rate

qty cat_id --+

SELECT c.cust_name, o.ord_date, oi.prod_id, p.prod_name, p.price, g.tax_rate

FROM order_item oi

INNER JOIN order o ON oi.ono = o.ono

INNER JOIN customer c ON o.cust_id = c.cust_id

INNER JOIN product p ON oi.prod_id = p.prod_id

INNER JOIN category g ON p.cat_id = g.cat_id

WHERE c.cust_id = '$theCustomer'

[/pre]

karenn1 · October 11, 2007

The code yzerman gave works great but you're right, the output is incorrect. It's showing close to 10'000 records at the moment. Here's the structure of one of my tables:

-- 
-- Table structure for table `am`
-- 

CREATE TABLE `am` (
  `AM_Code` varchar(10) NOT NULL default '',
  `AM_Name` varchar(50) NOT NULL default '',
  `AM_Surname` varchar(50) NOT NULL default '',
  `AM_Email` varchar(50) default NULL,
  `AM_Phone_Code` varchar(5) default NULL,
  `AM_Phone` varchar(15) default NULL,
  `AM_Cell` varchar(15) default NULL,
  `AM_Date_Assigned` date default NULL,
  PRIMARY KEY  (`AM_Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- 
-- Dumping data for table `am`
-- 

INSERT INTO `am` VALUES ('BB', 'BB', 'BB', NULL, NULL, NULL, NULL, 0x323030372d30372d3131);
INSERT INTO `am` VALUES ('CvdW', 'CvdW', 'CvdW', NULL, NULL, NULL, NULL, 0x323030372d30372d3131);
INSERT INTO `am` VALUES ('JK', 'JK', 'JK', NULL, NULL, NULL, NULL, 0x323030372d30372d3131);
INSERT INTO `am` VALUES ('MM', 'MM', 'MM', NULL, NULL, NULL, NULL, 0x323030372d30372d3131);
INSERT INTO `am` VALUES ('RR', 'RR', 'RR', NULL, NULL, NULL, NULL, 0x323030372d30372d3131);

All 5 tables don't have one field in common though. How do I change it not to duplicate the records so much?

Thanks,

Karen

karenn1 · October 11, 2007

Seems to be cross-posting happening here. The structure you laid out makes a lot of sense. Looking at my tables, I can actually join it that way. Just a question though:

SELECT c.cust_name, o.ord_date, oi.prod_id, p.prod_name, p.price, g.tax_rateFROM order_item oi    INNER JOIN order o ON oi.ono = o.ono    INNER JOIN customer c ON o.cust_id = c.cust_id    INNER JOIN product p ON oi.prod_id = p.prod_id    INNER JOIN category g ON p.cat_id = g.cat_idWHERE c.cust_id = '$theCustomer'

What is "c", "o", etc refer to?

Barand · October 11, 2007

Aliases for the tables to save typing out the tablename with every column name.

SELECT c.cust_name, o.ord_date, oi.prod_id, p.prod_name, p.price, g.tax_rate

FROM order_item oi

INNER JOIN order o ON oi.ono = o.ono

INNER JOIN customer c ON o.cust_id = c.cust_id

INNER JOIN product p ON oi.prod_id = p.prod_id

INNER JOIN category g ON p.cat_id = g.cat_id

WHERE c.cust_id = '$theCustomer'

karenn1 · October 11, 2007

Is this bit necessary:

WHERE c.cust_id = '$theCustomer'

if I just want a list of all the items and not just one relevant to a specific customer or ID?

Barand · October 11, 2007

The WHERE clause is optional. Without it you get all the items with their matching customers, product descriptions etc.

karenn1 · October 11, 2007

That's exactly what I want. The next question:

FROM order_item oi

Why did you user this table to select it from?

Barand · October 11, 2007

That is table that is central the the query - the other provide auxilliary information for the items.

However, if using INNER JOIN, it doesn't matter which order you pick them so long as there keys to join on.

For example, you can't select customer and then category next as they don't have direct join.

It could be

SELECT c.cust_name, o.ord_date, oi.prod_id, p.prod_name, p.price, g.tax_rate

FROM customer c

INNER JOIN order o ON o.cust_id = c.cust_id

INNER JOIN order_item oi ON oi.ono = o.ono

INNER JOIN product p ON oi.prod_id = p.prod_id

INNER JOIN category g ON p.cat_id = g.cat_id

karenn1 · October 11, 2007

Is this correct:

$sql_prop = "	SELECT am.AM_Code, 
				am_assigned.Venue_ID, 
				stock_trigger_summary.High_Stock, 
				stock_trigger_summary.Low_Stock, 					
				product.Product_Code, 					
				stock_take.Stock_Total,

FROM stock_trigger_summary


				INNER JOIN am_assigned ON stock_trigger_summary.Venue_ID = am_assigned.Venue_ID
				INNER JOIN am ON am_assigned.AM_Code = am.AM_Code
				INNER JOIN product ON stock_trigger_summary.Product_Code = product.Product_Code
				INNER JOIN stock_take ON product.Product_ID = stock_take.Product_ID			


			";

$result_prop = mysql_query($sql_prop);

I want to use this:

= $rs_prop["AM_Code"];

to actually get the value but it's not working at the moment. I also want this to display all the values in a list. I tried using this:

<?php
$i = 0;
while ($rs_prop = mysql_fetch_array($result_prop))	{
?>
tables here....
<?php $i++; } ?>

and that gives the following error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /usr/www/users/automat/alerts.php on line 73

Can you help please??

Barand · October 11, 2007

$sql_prop = "	SELECT am.AM_Code, 
				am_assigned.Venue_ID, 
				stock_trigger_summary.High_Stock, 
				stock_trigger_summary.Low_Stock, 					
				product.Product_Code, 					
				stock_take.Stock_Total,

FROM stock_trigger_summary


				INNER JOIN am_assigned ON stock_trigger_summary.Venue_ID = am_assigned.Venue_ID
				INNER JOIN am ON am_assigned.AM_Code = am.AM_Code
				INNER JOIN product ON stock_trigger_summary.Product_Code = product.Product_Code
				INNER JOIN stock_take ON product.Product_ID = stock_take.Product_ID			


			";

$res = mysql_query($sql_prop) or die (mysql_error()."<pre>$sql_prop</pre>");

while ($rs_prop = mysql_fetch_assoc($res))
{
    echo $rs_prop['AM_Code'], ' ', $rs_prop['High_Stock'], '<br />';
}
?>

karenn1 · October 11, 2007

I get an error with this line:

$res = mysql_query($sql_prop) or die (mysql_error()."<pre>$sql_prop</pre>");

Parse error: parse error, unexpected T_VARIABLE in /usr/www/users/automat/alerts.php on line 26

Barand · October 11, 2007

You usually get that one when the ";" is missing from previous line end;

karenn1 · October 11, 2007

Ok, fixed that. Next error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM stock_trigger_summary INNER JOIN am_assi
SELECT am.AM_Code, 
				am_assigned.Venue_ID, 
				stock_trigger_summary.High_Stock, 
				stock_trigger_summary.Low_Stock, 					
				brand.Brand_Code, 					
				stock_take.Stock_Total,

FROM stock_trigger_summary


				INNER JOIN am_assigned ON stock_trigger_summary.Venue_ID = am_assigned.Venue_ID
				INNER JOIN am ON am_assigned.AM_Code = am.AM_Code
				INNER JOIN product ON stock_take.Product_Code = product.Product_Code
				INNER JOIN stock_take ON stock_trigger_summary.Product_ID = stock_take.Product_ID			
				INNER JOIN brand ON product.Brand_Code = brand.Brand_Code

karenn1 · October 11, 2007

Sorry, I see it's because of the extra comma before the FROM. Now the page loads fine but no data is being displayed. I used the while loop you gave plus the echo statements as well but nothing is being echoed anywhere.

karenn1 · October 11, 2007

I've tried this:

$sql_prop = "	SELECT am.AM_Code, 
				am_assigned.Venue_ID, 
				stock_trigger_summary.High_Stock, 
				stock_trigger_summary.Low_Stock, 					
				brand.Brand_Code, 					
				stock_take.Stock_Total

FROM stock_trigger_summary


				INNER JOIN am_assigned ON stock_trigger_summary.Venue_ID = am_assigned.Venue_ID
				INNER JOIN am ON am_assigned.AM_Code = am.AM_Code
				INNER JOIN product ON stock_take.Product_Code = product.Product_Code
				INNER JOIN stock_take ON stock_trigger_summary.Product_Code = stock_take.Product_Code			
				INNER JOIN brand ON product.Brand_Code = brand.Brand_Code";




$result_prop = mysql_query($sql_prop));
$rs_prop = mysql_fetch_array($result_prop);


echo $rs_prop['Product_Code'];

But it echoes nothing. I'm thinking that the SQL query is incorrect perhaps. Can anyone have a look for me at the coding. Thanks!

karenn1 · October 11, 2007

Is there anybody who can help with with the SQL code above?? I'm not sure if the coding is right. Currently it's not echoeing anything.

Please help!!

Thanks,

Karen

Barand · October 11, 2007

Are you sure you have data that matches ALL the join criteria?

karenn1 · October 12, 2007

This database was actually given to me by the client so I'm not 100% sure if ALL the data complies. I got your email, though. I'll send you a dump of the required tables.

Thanks!

Karen

Barand · October 12, 2007

[pre]

stock_take product stock_trigger_summary am_assigned am

--------- ----------- --------------------- ------------ ----------

Product_ID ---- Product_ID Venue_ID --------- Venue_ID +---- AM_Code

Stock_Total Product_Code ---+ High_Stock AM_Code -----+

| Low_Stock

+---- Product_Code

[/pre]

You don't need the AM table - you only pull the AM_Code from it an that is in am_assigned, unless the purpose is to pull records only where an AM record exists

One approach would be to query the counts in each of the tables so you know how many of each.

Join them in pairs

stocktake - product

product - stock_trigger_summary

etc

and see where there are no matches

If you find,say, that there are some products without matching stocktake records then you need

products LEFT JOIN stock_take ON product.Product_ID = stock_take.Product_ID

Barand · October 12, 2007

It's your data.

product.product_code values don't match

am_assigned.venue_id values don't match

LS brand code missing too

karenn1 · October 12, 2007

I think the guy that set up this database might have gotten it a bit wrong. If all the data matches up, would the INNER JOIN coding that I used work?

Sign In

Joining Tables

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information