Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Sql Issue

Posted on 2013-02-04
16
Medium Priority
?
208 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
ID: 38850554
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38850570
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
ID: 38850578
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Author Comment

by:Moed
ID: 38850629
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
ID: 38850634
Can you give us examples of the exceptions?
0
 
LVL 2

Author Comment

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

Author Comment

by:Moed
ID: 38850645
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
ID: 38850651
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
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 38850653
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
ID: 38850671
I get exception "Join expression not supported."
0
 
LVL 143

Expert Comment

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

Expert Comment

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

Author Comment

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

Author Comment

by:Moed
ID: 38850721
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 1000 total points
ID: 38850732
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
ID: 38850757
Thank you very much.  That worked perfect.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
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.
Suggested Courses

877 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