Solved

Sql Issue

Posted on 2013-02-04
16
181 Views
Last Modified: 2013-02-04
visual basic, jet database.
I have two tables  Plants & Images, one to many.  I want to get all the records in plants and only the field Images.txtPicPath from images WHERE Images.blnDefaultPic=-1.  I cant get the syntax in the where statement correct.
Thanks

     Sql = " SELECT Plants.txtCommonName, Plants.txtBotanicalName, Plants.txtPlantLibrary, Plants.blnWishList, Plants.blnStockPlant, Images.txtPicPath, Images.blnDefaultPic " _
& " FROM Plants LEFT JOIN Images ON Plants.intRecordID = Images.intPlantID " _
& " WHERE Plants.blnWishList =False AND (SELECT images.blnDefaultPic WHERE  " _
& " ORDER BY Plants." & sSortBy & ""
0
Comment
Question by:Moed
  • 7
  • 6
  • 3
16 Comments
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
Comment Utility
Hi,

Try the following:

SQL = 
"SELECT P.*,txtPicPath" _
& "FROM Plants P" _
& "LEFT JOIN Images I ON P.intRecordID = I.intPlantID" _
& "WHERE Plants.blnWishList =False AND Images.blnDefaultPic=-1" _
& "ORDER BY P." & sSortBy &""

Open in new window


Giannis
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
with the LEFT JOIN, your condition needs to go to the JOIN ON ...

    Sql = " SELECT Plants.txtCommonName, Plants.txtBotanicalName, Plants.txtPlantLibrary, Plants.blnWishList, Plants.blnStockPlant, Images.txtPicPath, Images.blnDefaultPic " _
& " FROM Plants LEFT JOIN Images ON ( (Plants.intRecordID = Images.intPlantID ) AND (  ( images.blnDefaultPic  = -1 )) " _
& " WHERE (  Plants.blnWishList =False )  " _
& " ORDER BY Plants." & sSortBy & "" 

Open in new window

0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
Comment Utility
Also, keep in mind that if you want to get rows of the Plant table that don't have rows in the Images table then your query should be :

SQL = 
"SELECT P.*,txtPicPath" _
& "FROM Plants P" _
& "LEFT JOIN Images I ON P.intRecordID = I.intPlantID" _
& "WHERE P.blnWishList =False AND I.intPlantID IS NULL" _
& "ORDER BY P." & sSortBy &""

Open in new window


Giannis
0
 
LVL 2

Author Comment

by:Moed
Comment Utility
I want all the plant records and only the image record whose field blnDefaultPic = -1.  Unfortunately all the examples you've give throw exceptions.
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
Comment Utility
Can you give us examples of the exceptions?
0
 
LVL 2

Author Comment

by:Moed
Comment Utility
jyparask,
Cleaned yours up and it only returns records that are in both tables.
0
 
LVL 2

Author Comment

by:Moed
Comment Utility
Here is my cleaned up version:

 Sql = "SELECT Plants.txtCommonName, Plants.txtBotanicalName, Plants.txtPlantLibrary, Plants.blnWishList, Plants.blnStockPlant, " _
                   & " Images.txtPicPath, Images.blnDefaultPic " _
                   & " FROM Plants LEFT JOIN Images ON Plants.intRecordID = Images.intPlantID " _
                   & " WHERE Plants.blnWishList = False AND Images.blnDefaultPic=-1 " _
                   & " ORDER BY Plants." & sSortBy & ""
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
Comment Utility
The following will join the two tables only when they have a matching id and the blnDefaultPic equals -1. What i did wrong before was that i joined only with id and cleared every record that didn't have blnDefaultPic equals -1. This should be ok


SQL = 
"SELECT P.*,txtPicPath" _
& "FROM Plants P" _
& "LEFT JOIN Images I ON P.intRecordID = I.intPlantID" _ 
& "AND I.blnDefaultPic=-1" _
& "WHERE P.blnWishList =False" _
& "ORDER BY P." & sSortBy &""

Open in new window


Giannis
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
Comment Utility
Then it should be:

Sql = "SELECT Plants.txtCommonName, Plants.txtBotanicalName, Plants.txtPlantLibrary, Plants.blnWishList, Plants.blnStockPlant, " _
                   & " Images.txtPicPath, Images.blnDefaultPic " _
                   & " FROM Plants LEFT JOIN Images ON Plants.intRecordID = Images.intPlantID " _
                   & " AND Images.blnDefaultPic=-1 " _
                   & " WHERE Plants.blnWishList = False" _
                   & " ORDER BY Plants." & sSortBy & ""

Open in new window

Giannis
0
 
LVL 2

Author Comment

by:Moed
Comment Utility
I get exception "Join expression not supported."
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
which is what I posted already above, if I am not mistaken ...
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
as it's a "JET" (access) database, you have to add the  brackets
0
 
LVL 2

Author Comment

by:Moed
Comment Utility
angelIII,
True but it throws the same exception.
0
 
LVL 2

Author Comment

by:Moed
Comment Utility
even with the brackets.  This is what I used.

    Sql = " SELECT Plants.txtCommonName, Plants.txtBotanicalName, Plants.txtPlantLibrary, Plants.blnWishList, Plants.blnStockPlant, Images.txtPicPath, Images.blnDefaultPic " _
                & " FROM Plants LEFT JOIN Images ON ((Plants.intRecordID = Images.intPlantID))  AND ((images.blnDefaultPic  = -1))  " _
                & " WHERE (Plants.blnWishList =False)  " _
                & " ORDER BY Plants." & sSortBy & ""

Open in new window

0
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 250 total points
Comment Utility
Hi,

Can you try this:

    Sql = " SELECT Plants.txtCommonName, Plants.txtBotanicalName, Plants.txtPlantLibrary, Plants.blnWishList, Plants.blnStockPlant, Images.txtPicPath, Images.blnDefaultPic " _
                & " FROM Plants LEFT JOIN Images ON (Plants.intRecordID = Images.intPlantID  AND images.blnDefaultPic  = -1)  " _
                & " WHERE (Plants.blnWishList =False)  " _
                & " ORDER BY Plants." & sSortBy & ""

Open in new window


Giannis
0
 
LVL 2

Author Comment

by:Moed
Comment Utility
Thank you very much.  That worked perfect.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

743 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

17 Experts available now in Live!

Get 1:1 Help Now