Returning fields from word_custom in core_sys_lang_words

[Meddysong]

I'm trying to create a query which will display the items we have for sale, the descriptions, the stock count, and so on.

One of the challenges I have is that the site has two languages, so if I want the name of a product, I have to look in core_sys_lang_words:

SELECT p_id AS ID, word_custom AS Title, word_custom AS Description, p_group AS Category, p_stock AS Stock, p_store AS 'In Store', GBP AS Price, p_discounts AS Discounts, from_unixtime(p_date_added, '%Y-%d-%m') AS Added, from_unixtime(p_date_updated, '%Y-%d-%m') AS Updated
FROM nexus_packages as np
	LEFT JOIN core_sys_lang_words as lang
	ON lang.lang_id=1 AND lang.word_key=CONCAT('nexus_package_', np.p_id)
    LEFT JOIN nexus_package_base_prices as prices
	ON prices.id = np.p_id 
ORDER BY p_store DESC

The crucial part there is:

LEFT JOIN core_sys_lang_words as lang
ON lang.lang_id=1 AND lang.word_key=CONCAT('nexus_package_', np.p_id) 

For the product with the p_id 1, that looks up the row with the value nexus_package_1 in the field word_key, returning the value for word_custom:

Here's where I am stuck:

1) I'd also like to select the description, which is in the field word_custom where the word_key is nexus_package_X_desc.

2) I'd ideally like to select the title and description where lang_id = 2 too.

3) I'd also like to select the category name, which will involve looking up word_custom for 'nexus_pgroup_X'.

The problem I'm experiencing is in the SELECT line, since in each case it involves the field word_custom, which will naturally repeat the same value every time I mention it. How can I do the equivalent of word_custom AS Description, word_custom AS 'Title (language 2)', word_custom AS 'Category (language 1), word_custom AS 'Category (language 2), etc?  

 

[bfarber]

You join the same table more than once with different aliases.

LEFT JOIN core_sys_lang_words as titleJoin ON ...
LEFT JOIN core_sys_lang_words as descJoin ON ...

[Meddysong]

57 minutes ago, bfarber said:

You join the same table more than once with different aliases.

LEFT JOIN core_sys_lang_words as titleJoin ON ...
LEFT JOIN core_sys_lang_words as descJoin ON ...

Lovely: that's sorted out how to set out the joins. Thank you for that.

For anybody who is looking to do the same, you preprend the JOIN aliases to the field word_custom in the SELECT part to disambiguate:

SELECT p_id AS ID, titleEn.word_custom AS Title, descEn.word_custom AS Description, p_group AS Category, categories.pg_id, categories.pg_parent, catName.word_custom AS Category, parentCat.word_custom AS 'Category Parent', p_stock AS Stock, p_store AS 'On Sale', GBP AS Price, p_discounts AS Discounts, titleEo.word_custom AS Titolo, descEo.word_custom AS Priskribo, from_unixtime(p_date_added, '%Y-%d-%m') AS Added, from_unixtime(p_date_updated, '%Y-%d-%m') AS Updated
FROM nexus_packages as np
	LEFT JOIN core_sys_lang_words AS titleEn
	ON titleEn.lang_id=1 AND titleEn.word_key=CONCAT('nexus_package_', np.p_id)
	LEFT JOIN core_sys_lang_words AS descEn
	ON descEn.lang_id=1 AND descEn.word_key=CONCAT('nexus_package_', np.p_id, '_desc')
	LEFT JOIN core_sys_lang_words AS titleEo
	ON titleEo.lang_id=2 AND titleEo.word_key=CONCAT('nexus_package_', np.p_id)
	LEFT JOIN core_sys_lang_words AS descEo
	ON descEo.lang_id=2 AND descEo.word_key=CONCAT('nexus_package_', np.p_id, '_desc')
    LEFT JOIN nexus_package_base_prices AS prices
	ON prices.id = np.p_id 
    LEFT JOIN nexus_package_groups AS categories
	ON categories.pg_id = np.p_group
    LEFT JOIN core_sys_lang_words AS catName
	ON catName.lang_id=1 AND catName.word_key=CONCAT('nexus_pgroup_', categories.pg_id)
    LEFT JOIN core_sys_lang_words AS parentCat
	ON parentCat.lang_id=1 AND parentCat.word_key=CONCAT('nexus_pgroup_', categories.pg_parent)
ORDER BY p_store DESC

In other words, if your alias is 'title', then your SELECT line will feature 'title.word_custom'.