CODEWITHSUNDEEP
mysqlrelational-database
Cameeob2003
Cameeob2003

Reputation: 482

LEFT JOIN if a condition is met otherwise continue to select others

I have looked around for something that would fit what I'm looking for. I am by no means close to a SQL expert so I'm asking here. I want to grab a and b regardless of if c exists, but if c does exist LEFT JOIN it to the return. Here is the statement I am working with at the moment:

SELECT a.display_name, a.first_name, a.last_name, a.profile_image, a.tagline,
       a.bragging_rights, a.about_me, b.type AS post_type, b.title AS post_title,
       b.published AS post_published, b.updated AS post_updated,
       b.content AS post_content, b.num_replies AS post_replies, 
       b.num_plus_ones AS post_plus_ones, b.num_reshares AS post_reshares,
       c.display_name AS attach_display_name, c.content AS attach_content,
       c.url AS attach_url, c.image_url AS attach_image,
       c.image_width AS attach_width, c.image_height AS attach_height,
       c.full_image_url AS attach_full_image
  FROM cr_google_profiles a
  JOIN cr_google_posts b
  LEFT JOIN cr_google_post_attachements c 
    ON b.post_id = c.post_id
 WHERE a.google_id = :google_id AND b.google_id = :google_id AND c.google_id = :google_id

I'm really lost on how to do this; I've been YouTubing and reading the manual but haven't come up with anything that would accomplish what I'm looking for. Any help or constructive criticism is more than welcome.

Upvotes: 0

Views: 90

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 753970

I think your problem is that you've not specified a join condition for the join of a and b, so you really need something like:

SELECT a.display_name, a.first_name, a.last_name, a.profile_image, a.tagline,
       a.bragging_rights, a.about_me, b.type AS post_type, b.title AS post_title,
       b.published AS post_published, b.updated AS post_updated,
       b.content AS post_content, b.num_replies AS post_replies, 
       b.num_plus_ones AS post_plus_ones, b.num_reshares AS post_reshares,
       c.display_name AS attach_display_name, c.content AS attach_content,
       c.url AS attach_url, c.image_url AS attach_image,
       c.image_width AS attach_width, c.image_height AS attach_height,
       c.full_image_url AS attach_full_image
  FROM cr_google_profiles a
  JOIN cr_google_posts b
    ON a.google_id = b.google_id
  LEFT JOIN cr_google_post_attachements c 
    ON b.post_id = c.post_id
 WHERE a.google_id = :google_id
   AND b.google_id = :google_id
   AND c.google_id = :google_id

Upvotes: 0

peterm
peterm

Reputation: 92785

Are you looking for a way to correctly JOIN your tables? Something like this

SELECT ...
  FROM cr_google_profiles a JOIN cr_google_posts b 
    ON a.google_id = b.google_id LEFT JOIN cr_google_post_attachements c 
    ON b.google_id = c.google_id AND b.post_id = c.post_id 
WHERE a.google_id = :google_id

Upvotes: 1

Related Questions