[RESOLVED] Confused on why JOIN returns duplicate results

dmacman1962

Hi all,

I have a query that gives me the correct items from my database, but a duplicate set of them. I have read a bunch of JOIN tutorials and searched the posts, and still do not see my error. I do not see the reason why, and would like a little guidance on what I did wrong.

Here is my query:

$sql = "SELECT * FROM orders_products LEFT JOIN orders_products_attributes
ON orders_products.orders_id = orders_products_attributes.orders_id WHERE
orders_products_attributes.orders_id = '" . $oID . "'";

As always, I appreciate the help.

Regards,

Don

bretticus

If your orders_products_attributes table contains more than one record with the same orders_id as your orders_products table, you will get more than one record returned. How else can you get the orders_products_attributes rows?

You may want to check out GROUP_CONTACT.

dmacman1962

I understand some of your answer, but I am confused by this.

How else can you get the orders_products_attributes rows?

Maybe I did not explain what I get with a single query, vs the JOIN I attempting.

With a single query of each table I get:

orders_products (3 rows which is correct, 1 order with 3 different items)
2 shirts, and 1 mug

orders_products_attributes (2 rows which is correct, with 2 different items)
1 - Size/Color - L-White
1 - Size/Color - m-Blue

Now when I attempt the JOIN, I end up with (6 rows, which is not correct)
2 - Size/Color - L-White
2 - Size/Color - m-Blue
2 - Mugs

So what I seem to have got back was the left table (3 rows) multipled by the right table (2 rows).

Does that make sense?

And I read your link and that could concatenate it, but I think my join is messed up.

Thanks,
Don

bretticus

dmacman1962;10877401 wrote:
Maybe I did not explain what I get with a single query, vs the JOIN I attempting.

Yeah, always good to show your table structure when posting a question like this.

Okay, it appears that you have an orders table, an orders_product table, and an orders_product_attributes table.

I'll assume that orders_id is a primary key in orders and a foreign key in orders_product and orders_product_attributes.

It appears, you are using this query on a "orders detail page" to get the item listings.

Now, here is the "problem", it appears, based on your query, that you have no way to link an attribute to a product.

Of course, I have no idea unless you post your table structure.

But again, if you have 3 records with one id in one table and you have two records with the same id in another table, and now you join them, you'll get 6 records like you mentioned. In other words you have a many-to-many relationship. If you had an item id associated with your attribute, you'd at least have a one-to-many relationship.

Perhaps you shouldn't join on "orders_id".

dmacman1962

You were correct and your answers lead me to the solution:

$sql5 = "SELECT *
		FROM orders_products
		JOIN orders_products_attributes
		ON orders_products.orders_products_id = orders_products_attributes.orders_products_id
		WHERE orders_products_attributes.orders_id = '" . $oID . "'";

By joining the 'orders_products_id' and not the 'orders_id' I ended up with 2 records (the 2 shirts). So I selected all the items (except the ones with the products_attributes), then I just added another query inside the first to include the ones with the products_attributes, and it solved the problem.

Thanks for talking the issues and helping me figure this out. You were really helpfull and I appreciate it a lot!

Thanks,

Don