CODEWITHSUNDEEP
sql-server
Willian
Willian

Reputation: 95

Select data from a couple of tables

I have two tables, tab_ad and tab_photos, each ad can have five photos (or any).

Structure:

tab_ad

  • ad_id
  • ad_title
  • ad_desc

tab_photos

  • ph_id
  • ph_path
  • ad_id

I'm trying to make a select that select all columns of the tab_ad and the first photo on the tab_photos (each ad_id) and if I don't find the ad_id on tab_photos should return NULL.

Example: return ad_id, ad_title, ad_desc, ph_id, ph_path

Upvotes: 1

Views: 80

Answers (2)

Nithesh Narayanan
Nithesh Narayanan

Reputation: 11765

Use correlated sub query hence you want to select the top photo from the second table. 

SELECT 
    ad_id,
    ad_title,
    ad_desc,
    (SELECT TOP(1)ph_path FROM tab_photos b WHERE b.ad_id=a.ad_ID)
FROM tab_ad a

Upvotes: 1

Himanshu
Himanshu

Reputation: 32602

Try LEFT JOIN it will give values of tab_photos as NULL if the data (i.e. ad_id) is not available there :

SELECT ad.ad_id, ad.ad_title, ad.ad_desc, ph.ph_id, ph.ph_path
  FROM tab_ad ad 
  LEFT JOIN tab_photos ph
    ON ad.ad_id = ph.ad_id

Upvotes: 3

Related Questions