Sql Issue

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 & ""
LVL 2
MoedAsked:
Who is Participating?
 
Ioannis ParaskevopoulosConnect With a Mentor Commented:
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
 
Ioannis ParaskevopoulosCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Ioannis ParaskevopoulosCommented:
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
 
MoedAuthor Commented:
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
 
Ioannis ParaskevopoulosCommented:
Can you give us examples of the exceptions?
0
 
MoedAuthor Commented:
jyparask,
Cleaned yours up and it only returns records that are in both tables.
0
 
MoedAuthor Commented:
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
 
Ioannis ParaskevopoulosCommented:
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
 
Ioannis ParaskevopoulosCommented:
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
 
MoedAuthor Commented:
I get exception "Join expression not supported."
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
which is what I posted already above, if I am not mistaken ...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as it's a "JET" (access) database, you have to add the  brackets
0
 
MoedAuthor Commented:
angelIII,
True but it throws the same exception.
0
 
MoedAuthor Commented:
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
 
MoedAuthor Commented:
Thank you very much.  That worked perfect.
0
All Courses

From novice to tech pro — start learning today.