Solved

Query Syntax needed

Posted on 2011-02-24
3
301 Views
Last Modified: 2012-05-11
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
Comment
Question by:TownTalk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 22

Expert Comment

by:pivar
ID: 34968558
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
 
LVL 22

Accepted Solution

by:
pivar earned 500 total points
ID: 34968562
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
 

Author Comment

by:TownTalk
ID: 34968672
Yes that works. I had no idea you could specify criteria inside a Join expresssion. Thank you for your help.

Ian
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql Audit table 3 85
T-SQL to Update Table Dynamically 2 64
How to query LOCK_ESCALATION 4 42
What is this datetime? 1 29
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question