CODEWITHSUNDEEP
mysql
Blue Da Noob
Blue Da Noob

Reputation: 317

Pulling data from two tables (for a 'related products' query) in MySQL

I'm trying to pull data from two tables for a 'related products' widget. I've tried all the JOINS and UNIONS I can and still get nothing.

The first table (productdocs) stores documents. The second (prodrelated) shows when a product is related to a document:

I am trying to output the productdocs.pdname for any documents that match up with the product's id. In otherwords, show the pdname when: 

WHERE productdocs.pdid = prodrelated.pritemid

I would post my SQL code, but none of it has worked, so I think it would be pointless. I hope I explained this correctly given my frazzled brain - Any help greatly appreciated.

Upvotes: 0

Views: 89

Answers (2)

Blue Da Noob
Blue Da Noob

Reputation: 317

Wow you guys are fast - thank you so much.

Darshan - thank you above all, I was able to make a few mods to what you wrote and it worked great. I tried to +1 your answer but maybe I don't have enough 'reputation'? Here is what I got working, thanks to you:

SELECT pd.pdid, pd.pdname
FROM productdocs pd 
        JOIN prodrelated pr 
        ON pd.pdid = pr.pritemid
WHERE pr.prprodid = '#url.prodid#'

In the future I will try to post some code example, but on this one I honestly tried at least 7 different queries so I had no idea which to post!

Upvotes: 0

Darshan Mehta
Darshan Mehta

Reputation: 30849

You can use a simple INNER JOIN for this, e.g.:

SELECT pd.pdid, pd.pdname
FROM   productdocs pd JOIN prodrelated pr ON pd.pdid = pr.pritemid
WHERE  pd.prprodid = <any_id>;

If you don't want to filter out any records, you can get rid of WHERE clause and it will output all the records.

Here's MySQL's documentation for JOIN.

Upvotes: 1

Related Questions