Gathering Data Without 'Re-Querying' 1

[MSRez]

Ok, this is quite difficult to explain but I'll give it a go.

First of all, I have a table called 'Products' and I want to display one product on a page along with other information about that product gathered from related tables. The information gathered from other tables will not always be possible because a piece of information for a product may not be set so I cannot do one query to gather all the information because matches won't necessarily turn up for each table.

For instance, the products have manufacturers and the manufacturers are stored on another table. The product may not have a manufacturer alocated to it i.e:

SELECT p.product_id, p.product_name, p.manufacturer_id, m.manufacturer_id, m.manufacturer_name
FROM products AS p, manufacturers AS m
WHERE p.product_id = 20 AND m.manufacturer_id = p.manufacturer_id

That would be the query I could run to gather both pieces of information. However, if the product did not have a manufacturer set to it, how can I still make the table yield information about the product on its own without having to re-query the database to see if it does or doesn't have a manufacturer? (baring in mind I may have multiple relational tables I want to query which are not essential values but I don't want to keep re-querying, I'd rather just have one query if possible)

I'm not familiar with table JOIN or GROUP in MySQL, so those could perhaps be the answer to the problem, I don't know!

Anyway, I hope I have explained the problem and I'm sure it's a pretty common bridge developers have to cross. Anyone know how to cross this bridge!?

Thanks

 

[steven290]

how about something like this

SELECT p.product_id, p.product_name, p.manufacturer_id
FROM products AS p
left join manufacturers AS m
on m.manufacturer_id = p.manufacturer_id
where p.product_id = 20

 

[steven290]

sorry - update

SELECT p.product_id, p.product_name, p.manufacturer_id, m.manufacturer_id, m.manufacturer_name
FROM products AS p
left join manufacturers AS m
on m.manufacturer_id = p.manufacturer_id
where p.product_id = 20

 

[MSRez]

Ah, that's exactly it. Thank you very much, it works great.

Just so I understand it, what exactly is a LEFT JOIN?

Many thanks.

 

[sylve]

products as p LEFT JOIN manufacturers as m USING manufacturer_id

LEFT JOIN associates 2 tables. It will take ALL info in products table even if there is no correspondance in the manufacturers table.

Joins are really useful, you can go to this site for more info :

http://www.w3schools.com/sql/sql_join.asp

 

[steven290]

glad it worked out for you,

remember those who helped you along the way

 

[MSRez]

Thanks, and I will not forget your help

One other thing, is it alright to keep adding JOINs to the query or is it not a very efficient thing to do?

At the moment I've got the statement below, with still quite a few things I can add!

SELECT "lots of select parts"
	FROM " . $tables['titles'] . " AS t, " . $tables['platforms'] . " AS p
	LEFT JOIN " . $tables['boxart'] . " AS b
    ON b.game_id = t.title_id
	LEFT JOIN " . $tables['companies'] . " AS developer
    ON developer.company_id = t.developer_id
	LEFT JOIN " . $tables['companies'] . " AS publisher
    ON publisher.company_id = t.publisher_id
	WHERE t.title_id = '$title_id' AND p.abbrev = '$platform' AND t.title_platform_id = p.platform_id
	LIMIT 0,1

 

[steven290]

yes you can add more, and i believe this is the most efficient way but don't quote me on that.