Solved

Query Syntax needed

Posted on 2011-02-24
3
302 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 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