MySQL and Joins

How to Search Multiple Tables and Display the Results

[jimh009]

Hi,

I'm horribly confused by joins in MySQL. Was hoping someone here might be able to help me out - at least to understand the basics.

Basically, I have two tables containing similar products. Call these Tables A and Table B

I need to search through both Table A and Table B within select categories to find the products I need. Unfortunately, I need to make two different types of searches to find the information. In Table A, I can find the products by doing a search for a "Category_ID" number within the "Category_ID" column of Table A.

In Table B, however, in order to find the products I need, I have to do a LIKE search within the product category column.

Thus, each Table has a different header column that has to be searched.

Following that, another search has to be performed by Price across both tables. The price category has the same name in both tables.

In plain English, what I'm trying to do is to sort through two tables, looking for similar type of products that have a certain price point (in this case, equal to or less than $60), and then display the results all on one page.

I've read all sorts of things about joins and have so far come away more confused than when I began!

Anyone care to throw me a bone to chew on?

Thanks

Jim

[coopster]

You list the columns you would like returned in the first part of the SELECT list, if they are from different tables you specify them in

table.column

format which will clarify the ambiguity for the database engine (it will know which price value you want pulled). Next is to JOIN the two tables. Two of the most common joins are INNER JOIN and LEFT JOIN. Here is a thread that describes LEFT JOIN versus INNER JOIN [webmasterworld.com]. When you perform the JOIN you need to tell the database engine the relationship between the two tables, or how to join them, what to join them on. If you don't specify a type of JOIN it will join all rows from one table to every row in the other table for each and every row in the first table (often referred to as the left hand table or primary table in a join). Lastly, you can specify that only certain rows get returned, and that is performed using a WHERE clause.

In a nutshell, that is the basics. How about you give us an example of how you think it should be written, and we'll help you go from there?

[jimh009]

Hi Coopster,

I'm new to MySQL and Php, so don't laugh when you see how awful this query is.

<?php
$query = 'SELECT * FROM TABLE_1 INNER JOIN TABLE_2 WHERE TABLE_1.Category_ID="190210" AND TABLE_2.Category LIKE "Product%" AND TABLE_1.Price < "60" AND TABLE_2.Price < "60" ORDER BY TABLE_1.Price and TABLE__2.Price ASC Limit 20';

$results = mysql_query($query)
or die(mysql_error());
?>

Since the "Price" field is shared in both Table_1 and Table_2, I had to put in all the Table.Price in order to avoid getting an ambiguous error.

If you can't tell, I'm a bit lost right now!

Jim

[coopster]

That's OK, and no, there is no laughing going on here. We have all been down this road and that is why these forums exist -- to help each other. Let's have a look at the query ...

SELECT * 
 FROM TABLE_1 
 INNER JOIN TABLE_2 
 WHERE 
  TABLE_1.Category_ID="190210" AND 
  TABLE_2.Category LIKE "Product%" AND 
  TABLE_1.Price < "60" AND 
  TABLE_2.Price < "60" 
 ORDER BY TABLE_1.Price and TABLE__2.Price ASC Limit 20' 
;

The first line is saying that you want all columns returned from both tables. Are you sure? Perhaps you only want the product type, name and description? Since I don't know what you are looking for, I'm going to make up a couple of column names to demonstrate:

SELECT 
  TABLE_1.ProductType, 
  TABLE_2.ProductName, 
  TABLE_2.ProductDesc 
 FROM TABLE_1 
 INNER JOIN TABLE_2

Next you are performing your JOIN. You are doing an INNER JOIN but you are not telling your tables how to join to each other. This is going to result in every row being returned from the first table joined to every row in the second table. So, if you had 4 rows in the first table, and 7 in the second, you would actually return 28 rows! Probably not what you intended! You may want to specify a join condition:

INNER JOIN TABLE_2 ON (TABLE_1.Category_ID = TABLE_2.Category_ID)

Putting it all back together then:

SELECT 
  TABLE_1.ProductType, 
  TABLE_2.ProductName, 
  TABLE_2.ProductDesc 
 FROM TABLE_1 
 INNER JOIN TABLE_2 ON (TABLE_1.Category_ID = TABLE_2.Category_ID) 
 WHERE 
  TABLE_1.Category_ID="190210" AND 
  TABLE_2.Category LIKE "Product%" AND 
  TABLE_1.Price < "60" AND 
  TABLE_2.Price < "60" 
 ORDER BY TABLE_1.Price and TABLE__2.Price ASC Limit 20' 
;

The only question I have outside of all this is why two prices, one in each table? Perhaps you should show us your table CREATE statements so we can have a look at structure and relationship to see if we can offer any guidance there as well.

[jvmills]

Whilst you really should learn a little about joins and their syntax, you can always cheat a little and download a graphical interface such as Navicat.

A bit like the query/view builder in MSSQL or Access, it allows you to drop in your tables and joins.

[jimh009]

Hi Coopster,

I've been playing with this and think I'm at least getting there. Here's what I got so far. I'm not getting any MySQL errors - which is a darn good sign. Problem is, it's not returning any results either when there are plenty of products to meet the query. Not sure what I'm doing wrong.

Anyways, here the code so far.

<?php
$query = 'SELECT
Table_1.Category,
Table_1.ProductName,
Table_1.Brief_Description,
Table_1.Price,
Table_1.ImageURL,
Table_1.ProductURL,
Table_2.Category,
Table_2.ProductName,
Table_2.Brief_Description,
Table_2.ImageURL,
Table_2.ProductURL,
Table_2.Price
FROM Table_1
INNER JOIN Table_2 ON (Table_1.Category = Table_2.Category)
WHERE
Table_1.Category = "220203" AND
Table_2.Category LIKE "%product%" AND
Table_1.Price > "60" AND
Table_2.Price > "60"
ORDER BY Table_1.Price and Table_2.Price DESC Limit 20'
;
$results = mysql_query($query)
or die(mysql_error());
?>

I have to admit, I'm still a bit clueless on how this join is working. One thing I'm very confused about is in the select statement - we're picking up info off of 2 tables. Yet, in the FROM statement we only specifiy one table. Maybe I'm a bit confused on how MySQL does queries?

Also, I do have Navicat. It's a wonderful program indeed. I could have never got started doing this without it. It makes uploading Excel files and creating my own databases such a snap. I still use phpadmin, though, for testing queries.

Jim

[syber]

One thing I'm very confused about is in the select statement - we're picking up info off of 2 tables. Yet, in the FROM statement we only specifiy one table.

Not true, your FROM statement mentions 2 tables

FROM Table_1 INNER JOIN Table_2
ON (Table_1.Category = Table_2.Category)

[john_k]

I think this line from your WHERE clause needs correcting:

Table_2.Category LIKE "%product%" AND

[jimh009]

Hi,

>> I think this line from your WHERE clause needs correcting:

I'm still new to mysql and php, so can you suggest what it is wrong with it? Or a suggested fix?

Thanks

Jim

[john_k]

I'm not sure if you intend "product" here to be the literal string "product", or if that is the name of a variable.

As you have it written, that line of your WHERE clause is filtering your results so that you only get rows where table_2.Category has the word "product" in it. So that is at least part of the reason why you are not getting any results back.