CODEWITHSUNDEEP
phpmysqlleft-join
Merlin
Merlin

Reputation: 978

Can I replace the output value from one table with the value from another table on match?

I have two tables for an example: Posts and Categories. Posts are assigned a category, but by an ID number. When grabbing my posts, I would like to perform a LEFT JOIN and get the name of the category associated with that post, then switch the output - not the actual table content - to include the name directly in place of the value. So far, this is what I have, and I know it works to a point up to the LEFT JOIN, but the replacing values is where I get confused, though I believe it is possible

        SELECT * FROM nve_multi_posts
        LEFT JOIN nve_categories
        SELECT
        CASE
        WHEN nve_multi_posts.category = nve_categories.id
        THEN
        nve_multi_posts.category = nve_categories.name
        END                 
        ON nve_multi_posts.category = nve_categories.id

What am I messing up?

Upvotes: 0

Views: 23

Answers (1)

Alex
Alex

Reputation: 17289

Just that simple:

SELECT p.*, c.*
FROM nve_multi_posts p
LEFT JOIN nve_categories c
ON p.category = c.id

Or do you want some kind of UPDATE query to chenge data stored in nve_multi_posts table?

If you just need named column returned from the query you can just alias every column you need like:

SELECT p.id as post _id,
       p.category as category_id,
       c.name as category
FROM nve_multi_posts p
LEFT JOIN nve_categories c
ON p.category = c.id

Upvotes: 1

Related Questions