Solved

Sql Issue

Posted on 2013-02-04
16
182 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 142

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
 
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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 142

Expert Comment

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

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Data is not showing from images 15 38
ASP.NET 5 Templates 2 66
Javascript and Jquery not firing 9 42
Get size of each directory on each mapped drive 5 23
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

867 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

18 Experts available now in Live!

Get 1:1 Help Now