[RESOLVED] 3 table conditional join problem

[geek_girl_2020]

I have been working on this one for quite some time and I haven't been able to crack it. Lots of solutions for mysql 4.1 and later but having to work with 4.0 🙁

None of the queries I have found or examples in books and online are quite the same as my problem. Have also tried using subqueries /exists but no luck there either.

The three tables in question are:

product_market_id - (product_id, market_id) all products can have multiple market_ids so is a combined primary key.

product_textile_category - (product_id, textile_category_id) products can belong to multiple textile categories

product - (product_id, product_name etc)

I am trying to write a query that selects the product_ids that have a particular market_id, and a particular textile_category_id.

MY ATTEMPT THUS FAR:

$query = "SELECT product_textile_category.product_id
FROM product_textile_category, product_market_id
WHERE product_textile_category.product.id = product_market_id.product_id
AND product_textile_category.textile_category_id = '".$textile_category_id."'
AND product_market_id.market_id = '2'";

It is 3am in the morning and I'm trying to meet a deadline of tomorrow morning for a go live.....head swimming....must practise sql, must practise sql

Many thanks in advance for any pointers.

[Sxooter]

I'm guessing you need your product table in there somewhere.

Try:

SELECT  prd.product_name
FROM product prd 
JOIN product_textile_category ptc
ON (ptc.product_id=prd.product_id)
JOIN product_market pm
ON (pm.product_id=prd.product_id)
WHERE ptc.textile_category_id = '".$textile_category_id."' AND pm.market_id = '2'";

Does that make sense?

Note that the select list is probably too small, you likely want other things in there.

I'm pretty sure MySQL 4.0 supports proper join on syntax.

[geek_girl_2020]

Hey - thank you very much for your help - I am just about to check out your suggestion...and I will let you know how I go.

For the record one after spending hours struggling with the query I uncovered a deeper problem which related to the fact that I had use sort() on an array of names which need to stay linked to their correct id numbers for my query to return the correct results.

By sorting the names and not considering their id numbers - all the id numbers had been mixed up!!

Creating an associative array with the name as the key, and the id number as the value - and then using ksort() to do my alphabetical ordering has sorted my id numbers out and now I can actually test my query properly!!

Definitely a trap for young players there....anyhow better get back to this query...will let you know how I go. SQL looks good to go at first glance!! Cheers 🙂

[geek_girl_2020]

This is where I was at with the query before your assistance:

$query = "SELECT DISTINCT product.product_id
FROM product
INNER JOIN product_textile_category
ON product_textile_category.product_id = product.product_id
WHERE product_textile_category.textile_category_id = '".$textile_category_id."'";

I;m grabbing just the products in the right textile category - now I just need to make sure that they are also only product_ids that match with the market_id '2' in the product_market_id table....

[Sxooter]

geek_girl_2020 wrote:

Creating an associative array with the name as the key, and the id number as the value - and then using ksort() to do my alphabetical ordering has sorted my id numbers out and now I can actually test my query properly!!

Definitely a trap for young players there....anyhow better get back to this query...will let you know how I go. SQL looks good to go at first glance!! Cheers 🙂

Note that judicious use of the three following functions can switch such an array and switch it back when done.

array_combine
array_keys
array_values

I find the array functions in PHP to be quite amazingly comprehensive.

[geek_girl_2020]

I have rewritten the query only in name (I think!) .This is what I have.....that doesn't work...

$query = "SELECT DISTINCT product.product_id
FROM product
INNER JOIN product_textile_category
ON product_textile_category.product_id = product.product_id
WHERE product_textile_category.textile_category_id = '".$textile_category_id."'
INNER JOIN product_market_id
ON product_market_id.product_id = product.product_id
WHERE product_market_id.market_id = '".$market_id."'";

Still trying....

[geek_girl_2020]

I ended up being able to get away with a 2 table query. Just couldn't get the 3 table one to work. Just using a separate query for looking up the product_name.

This query checks whether a product belongs to 2 different categories by checking the relation/look-up tables for the product_ids matched to specific category ids. I hope someone finds this example useful.

$query = "SELECT DISTINCT product_textile_category.product_id
FROM product_textile_category
INNER JOIN product_market_id
ON product_textile_category.product_id = product_market_id.product_id
WHERE product_textile_category.textile_category_id = '".$textile_category_id."'
AND product_market_id.market_id = '".$market_id."'";