Page 1 of 1
MySQL Inner Join - how do you make them?
Posted: Tue Jun 21, 2011 6:02 am
by simonmlewis
Code: Select all
$accessories = mysql_query ("SELECT * FROM accessories LEFT JOIN products on accessories.id = products.id WHERE accessories.catid = '$row->catid' AND products.rcstock = 'in stock' ORDER BY RAND() LIMIT 0, 4");
while ($accslist = mysql_fetch_object($accessories))
{
I'm trying to get 3 random entries from the Accessories db table, while only showing those where the 'rcstock' field in Products says 'in stock'.
This query does produce three results, however it even produces those where rcstock says 'out of stock'. So it's not quite working!
$row->catid is taken from the query on the overall page.
HELP.

Re: MySQL Inner Join - how do you make them?
Posted: Tue Jun 21, 2011 1:14 pm
by McInfo
That seems to imply that accessories and products have a one-to-one relationship. Do they?
There are some inconsistencies in your post:
- Subject says "inner join"; query says "left join".
- Query says "LIMIT 0, 4"; you say "3 random entries".
- Query says "in stock"; you say "out of stock".
It leads me to wonder if maybe the code you think is running is not the code that actually is running.
Re: MySQL Inner Join - how do you make them?
Posted: Tue Jun 21, 2011 7:46 pm
by califdon
A left join is, by definition, an outer join, since it means that you are asking for all rows of the "left" table and only the matching rows of the "right" table. An inner join is defined as containing ONLY matching rows. It sounds like you do want an inner join, since you want to filter the results with a constraint on a field in the "right" table, so replace "left" with "inner", then use your WHERE clause to filter for the "in stock" (or "out of stock", as was noted) value.
Apparently there is an earlier query that produces the value $row->catid, right?
Re: MySQL Inner Join - how do you make them?
Posted: Tue Feb 14, 2012 6:07 am
by simonmlewis
Code: Select all
$result = mysql_query ("SELECT * FROM subcategories INNER JOIN products on subcategories.subid=products.subid WHERE products.pause = 'off' ORDER BY subcategories.name ASC")or die(mysql_error());
while ($row = mysql_fetch_object($result))
{
$resultcat = mysql_query ("SELECT * FROM categories WHERE id = '$row->subcategories.catid'")or die(mysql_error());
while ($rowc = mysql_fetch_object($resultcat))
{
echo "$row->subcategories.name";
}
}
This query is looking for Subcategories names, where the products in the system are not paused (ie. available).
So I have to join the two tables, find all the subcategories where products 'pause' field show 'off'.
This isn't producing anything tho. I've also had trouble with JOINS - where am I going wrong?
Re: MySQL Inner Join - how do you make them?
Posted: Tue Feb 14, 2012 12:40 pm
by califdon
We have a better chance of helping you if you will describe what is going wrong and how. "This isn't producing anything" doesn't tell us anything useful. Nor does "I've also had trouble with JOINS." If you can be specific, maybe we can help. What exactly happens when you run this? Any error messages? Does anything appear at all? Have you checked to see if your data contains any records that meet the criteria in your queries? Have you checked to see if the variables in your queries have values when you run them? (For example, you can add a temporary debugging line to echo $row->subcategories.catid just before you try to use that variable in a query.) Specifically what kind of trouble have you had with Joins?
Re: MySQL Inner Join - how do you make them?
Posted: Tue Feb 14, 2012 4:47 pm
by simonmlewis
Ok I found a better way of doing it anyway, as this - looking at it again - was never going to work properly.
However, it produced no errors at all, nothing. I can never understand what INNER, RIGHT and LEFT joins really mean. I tried all three!
Re: MySQL Inner Join - how do you make them?
Posted: Tue Feb 14, 2012 7:38 pm
by califdon
simonmlewis wrote:I can never understand what INNER, RIGHT and LEFT joins really mean. I tried all three!
Trying things without understanding them will usually make it impossible for you to learn. The types of joins are not that difficult to learn. There's a pretty good simple explanation here:
http://www.w3schools.com/sql/sql_join.asp. It depends on what rows you want the query to return: only the rows from each table that match the JOIN criteria, or ALL the rows from one of the tables (RIGHT or LEFT in the SQL statement) plus rows in the other table that match the criteria.