Solved

Sql Issue

Posted on 2013-02-04
16
188 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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 250 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sending an input value from a view to a controller in MVC 3 23
JSON Deserialize issue 6 26
SSRS 2016 Rendering HTML tables 3 31
Groupbox Control ? 2 19
If you haven’t already, I encourage you to read the first article (http://www.experts-exchange.com/articles/18680/An-Introduction-to-R-Programming-and-R-Studio.html) in my series to gain a basic foundation of R and R Studio.  You will also find the …
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.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

820 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