How can I create this SQL query that has to join four tables to allow me extract the data I need?

What I want to do is a PHP script that gets all the products of a Joomla VirtueMart using PHP and mySQL. However my question has nothing to do about Joomla or PHP but I am in the need of a mySQL query.

Below you can find the tables and the fields that holds the data I need which are

product_id, product_desc, product_full_image, product_name, product_price, category_name.

To get the product_price you will need the product_id from jos_vm_pro开发者_如何学Cduct and match it with the product_id from jos_vm_product_price to get the product_price.

Every product_id has a category_id inside jos_vm_product_category_xref. So, when the category_id is found, you get the category_name from jos_vm_category.

This is difficult for me as I am not very familiar with SQL queries and I need your help on how to to join those tables, so it will allow me to echo the values I need.

Thank you.

jos_vm_poduct

product_id
product_desc
product_full_image
product_name

jos_vm_product_price

product_id
product_price

jos_vm_product_category_xref

product_id
category_id

jos_vm_category

category_id
categroy_name

Sure we could write you an answer, but, to best understand it heres the things you want to know

select <stuff> from <table1> 

http://dev.mysql.com/doc/refman/5.0/en/join.html

You then join it with another table

so,

select product_name, product_price from table1 
left join table2 on table1.product_id=table2.product_id

once you've linked your tables.

Now strip out the bits you either want, or dont want. Such as, if you left join.. you may want

where product_price>0

When you have then stripped or selected the bit(s) you wanted, job done.

Something like this ought to work:

SELECT jvp.product_id, product_desc, product_full_image, product_name, product_price, category_name   
FROM jos_vm_product jvp  
JOIN jos_vm_product_price jvpp  
ON jvp.product_id = jvpp.product_id  
JOIN jos_vm_product_category_xref jvpcx  
ON jvpcx.product_id = jvp.product_id  
JOIN jos_vm_category jvc  
ON jvc.category_id = jvpcx.category_id  

Any questions about why, please ask away!

I'm assuming you want all the products of a given category. If not it's easy to remove the where clause to grab them all.

I used a subquery to fetch all the product id's of a given category, then join the other two tables.

    SELECT
        *
    FROM
        (SELECT 
            p.product_id,
            c.category_name, 
        FROM 
            jos_vm_product_category_xref AS p
            LEFT JOIN jos_vm_category AS c
            ON p.category_id == c.category_id
        WHERE 
            c.category_id = 4
        ) as products
        LEFT JOIN jos_product 
        ON jos_product.product_id=products.product_id
        LEFT JOIN jos_vm_product_price 
        ON jos_vm_product_price=products.product_id