Topic: INNER JOIN vs. LEFT JOIN

[e1seix]

Bit of trouble with this one. The following statement is for my basket on my website. Straight forward enough. Counts up the "id" column of the "basket" table according to the users ip address to determine how many items in their basket.

I need to inner join this statement with another table ( products ) where "id" is the common denominator to grab the "id"'s corresponding "price" and total the price of all "id"'s together.

Getting very confused if I should do INNER, LEFT JOIN and if I require another statement within a statement.

Can somebody help me out here?

Code: [Select]

SELECT COUNT( id ) FROM basket WHERE ip_address = "'.$_SERVER["REMOTE_ADDR"].'" GROUP BY ip_address

Need to join the above to the following:

Code: [Select]

SELECT COUNT( search_price ) FROM products WHERE for_sale = "yes"

This is how far I've come however it's saying b.id doesn't exist when "id" is very much present in both products and basket tables

Code: [Select]

SELECT COUNT( id ) FROM basket a INNER JOIN ( SELECT COUNT( search_price ) FROM products WHERE for_sale = "yes" ) b ON a.id = b.id WHERE ip_address = "'.$_SERVER["REMOTE_ADDR"].'" GROUP BY ip_address


[Zane]

It's because the id in your COUNT function is ambiguous.  Should be Count(b.id) or Count(a.id)

Moreover, here's my take on your SQL situation.

Code: [Select]

SELECT COUNT( b.id ) FROM basket b
INNER JOIN products b ON b.id = p.id
WHERE p.for_sale = "yes" AND b.ip_address = "'.$_SERVER["REMOTE_ADDR"].'" GROUP BY b.ip_address

[mikosiko]

what Zanus said... but the main reason for your error is that you didn't define the alias (b) for your table products as you did for the table basket (a).

+1 in the way that Zanus wrote the sentence

[btherl]

INNER JOIN means "find matching rows in each table"
LEFT JOIN means "find matching rows in each table, AND also include any unmatched rows in the LEFT table"
RIGHT JOIN means "find matching rows in each table, AND also include any unmatched rows in the RIGHT table"

[pengu]

Totally beat to this.  Look up inner joins.

It'd have to be something like this.

Code: [Select]

SELECT COUNT( a.id ), COUNT( b.search_price )
FROM basket AS a
INNER JOIN products AS b ON a.id = b.id
WHERE a.ip_address = "'.$_SERVER["REMOTE_ADDR"].'"
AND b.for_sale = "yes"

On second thought and after some testing.

I think this will work.

Code: [Select]

SELECT COUNT( a.id ) AS ACount, (SELECT COUNT( b.search_price ) FROM products WHERE for_sale = "yes") AS BCount
FROM basket
WHERE a.ip_address = "'.$_SERVER["REMOTE_ADDR"].'"


[pengu]

Totally beat to this.  Look up inner joins.

It'd have to be something like this.

Code: [Select]

SELECT COUNT( a.id ), COUNT( b.search_price )
FROM basket AS a
INNER JOIN products AS b ON a.id = b.id
WHERE a.ip_address = "'.$_SERVER["REMOTE_ADDR"].'"
AND b.for_sale = "yes"

On second thought and after some testing.

I think this will work.

Code: [Select]

SELECT COUNT( a.id ) AS ACount, (SELECT COUNT( b.search_price ) FROM products WHERE for_sale = "yes") AS BCount
FROM basket
WHERE a.ip_address = "'.$_SERVER["REMOTE_ADDR"].'"


Can't edit my damn post..

I meant this.

Code: [Select]

SELECT COUNT( id ) AS ACount, (SELECT COUNT( search_price ) FROM products WHERE for_sale = "yes") AS BCount
FROM basket
WHERE ip_address = "'.$_SERVER["REMOTE_ADDR"].'"


[btherl]

pengu, the first query finds matching rows in both tables and counts those, whereas the second query (without the join) counts ALL rows fitting the "where" condition, even if they don't match a row in the other table.

So it will work only if there's a 1 to 1 correspondence between ids in each table, which is unlikely when one table is a basket and the other is a product list.

I think what the OP actually wanted was this:

SELECT COUNT( b.id ), SUM(p.search_price)
FROM basket b
INNER JOIN products b ON b.id = p.id
WHERE p.for_sale = "yes" AND b.ip_address = "'.$_SERVER["REMOTE_ADDR"].'" GROUP BY b.ip_address

That gives the total product count and the total price.  It doesn't get the unique item count, but I'm not clear on exactly what was wanted.

[pengu]

pengu, the first query finds matching rows in both tables and counts those, whereas the second query (without the join) counts ALL rows fitting the "where" condition, even if they don't match a row in the other table.

So it will work only if there's a 1 to 1 correspondence between ids in each table, which is unlikely when one table is a basket and the other is a product list.

I think what the OP actually wanted was this:

SELECT COUNT( b.id ), SUM(p.search_price)
FROM basket b
INNER JOIN products b ON b.id = p.id
WHERE p.for_sale = "yes" AND b.ip_address = "'.$_SERVER["REMOTE_ADDR"].'" GROUP BY b.ip_address

That gives the total product count and the total price.  It doesn't get the unique item count, but I'm not clear on exactly what was wanted.

Yeah, I'm not sure what the OP wanted either.  As far as I could tell it was count of both of those fields.