Solved

Query Syntax needed

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now