[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

Conflicting WHERE clause

Hello, I Have a SQL statement which returns no records...
I'm not sure what the problem is, but this is what I'm trying to do:

I added a field to this SQL statment called IB_GROUP and the where clause for this statement is:
b.IB_GROUP = lv.LOOKUP_CODE AND lv.LOOKUP_NAME = 'IB_GROUP'

But it's conflicting with the other field called STATUS
for which the where clause is:
b.ACTIVE = lv.LOOKUP_CODE AND lv.LOOKUP_NAME = 'BAN_STATUS'

I think it's looking for records that fit all these 4 criterias together instead of looking for records that fit the first two to put into the IB_GROUP field and the second two to put into the STATUS field

So basically how do you do a SQL statment like this which requires so many different where clauses INTO just one WHERE Clause
Like if I could do this with two separate where clauses it would work, but I need both fields to show up into this one TABLE
Can you help?

THIS IS MY CURRENT CODE BUT IT DOESN'T WORK:

CMD.CommandText = "Select BANKER_ID, PREFIX, FIRST_NM, MIDDLE_INIT, LAST_NM " & _
                        ", SUFFIX, lv.LOOKUP_DESC AS STATUS, lv.LOOKUP_DESC AS IB_GROUP, TITLE From BANKER b, LOOKUP_VALUES lv " & _
                        "Where (b.ACTIVE = lv.LOOKUP_CODE AND lv.LOOKUP_NAME = 'BAN_STATUS')" & _
                        "AND (b.IB_GROUP = lv.LOOKUP_CODE AND lv.LOOKUP_NAME = 'IB_GROUP')" & _
                        "AND LAST_NM Like '" & Replace(Me.txtLastName.Text, "'", "''") & "%' ORDER BY LAST_NM;"



THE FOLLOWING CODE IS WHAT I HAD BEFORE I ADDED THE IB_GROUP FIELD, AND IT WORKS, BUT I REALLY NEED THIS IB_GROUP FIELD INTO THE SAME TABLE:

CMD.CommandText = "Select BANKER_ID, PREFIX, FIRST_NM, MIDDLE_INIT, LAST_NM " & _
                        ", SUFFIX, lv.LOOKUP_DESC AS STATUS, TITLE From BANKER b, LOOKUP_VALUES lv " & _
                        "Where (b.ACTIVE = lv.LOOKUP_CODE AND lv.LOOKUP_NAME = 'BAN_STATUS')" & _
                         "AND LAST_NM Like '" & Replace(Me.txtLastName.Text, "'", "''") & "%' ORDER BY LAST_NM;"
0
angelahontau
Asked:
angelahontau
  • 2
  • 2
1 Solution
 
JesterTooCommented:
Try it like this... (BTW, which DBMS are you running this against?  I'm curious about the ";" at the end of the query.

CMD.CommandText = "Select BANKER_ID, PREFIX, FIRST_NM, MIDDLE_INIT, LAST_NM " & _
                        ", SUFFIX, lv1.LOOKUP_DESC AS STATUS, lv2.LOOKUP_DESC AS IB_GROUP, TITLE From BANKER b, LOOKUP_VALUES lv1 " & _
                        ", LOOKUP_VALUES lv2 " & _
                        "Where b.ACTIVE = lv1.LOOKUP_CODE AND lv1.LOOKUP_NAME = 'BAN_STATUS'" & _
                        "AND b.IB_GROUP = lv2.LOOKUP_CODE AND lv2.LOOKUP_NAME = 'IB_GROUP'" & _
                        "AND LAST_NM Like '" & Replace(Me.txtLastName.Text, "'", "''") & "%' ORDER BY LAST_NM;"    
0
 
angelahontauAuthor Commented:
The database is a Microsoft Access database called "Pipeline.mdb," is that what you mean by DBMS?
0
 
angelahontauAuthor Commented:
THANK YOU!
0
 
JesterTooCommented:
You're welcome and thank you for the grade!

Lynn
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now