Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

Query Syntax needed

Because of a problem elsewhere in my code, i've had to join 2 queries together. I want to select all fields from a record in my stock table, and all records from the Stock Images table, but only where the image category is 'P'. This is what I currently have.

SELECT Stock.*, StockImages.ImagePath
FROM Stock LEFT JOIN StockImages ON Stock.StockCode=StockImages.StockRef
WHERE ((Stock.StockCode='ABC123') AND (StockImages.Category='P'))

The problem with this is that because the Where Clause is specifying images with a category of 'P', no data at all is returned if there aren't any images associated with a stock record.

I need to modify the above query so that the fields from the stock record are always returned, and image data only if the image has a category of 'P'. How do I do this?

Ian
0
TownTalk
Asked:
TownTalk
  • 2
1 Solution
 
pivarCommented:
Hi,

Try


SELECT Stock.*, StockImages.ImagePath
FROM Stock
LEFT JOIN StockImages ON Stock.StockCode=StockImages.StockRef AND (StockImages.Category='P')
WHERE Stock.StockCode='ABC123'

/peter
0
 
pivarCommented:
Of course you don't need the parenthesis

SELECT Stock.*, StockImages.ImagePath
FROM Stock
LEFT JOIN StockImages ON Stock.StockCode=StockImages.StockRef AND StockImages.Category='P'
WHERE Stock.StockCode='ABC123'
0
 
TownTalkAuthor Commented:
Yes that works. I had no idea you could specify criteria inside a Join expresssion. Thank you for your help.

Ian
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now