Solved

Query Syntax needed

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 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