Solved

Need Help with a select

Posted on 2008-06-12
6
251 Views
Last Modified: 2012-05-05
I have this select

$pricelist = "select * from PRODATA!ICITEM01 LEFT JOIN PRODATA!VSPROT01 ON Icitem01.item = Vsprot01.item AND Vsprot01.menuid = '$CustMenu->value' WHERE Icitem01.market1>0 ORDER BY Icitem01.itmdesc";

basically the first select pulls the master item list from icitem01, then it joins the data from the vspoto01 which is a custom item list, matching the menu id from both tables while keep the data from the master item table even if there is no matching item in the menu override table

what i need to do is create a second select that pulls the same information as the first select but also further limit the results where Vsprot01.cntpa > 0

0
Comment
Question by:SuprSpy79
  • 3
  • 2
6 Comments
 
LVL 48

Accepted Solution

by:
hernst42 earned 500 total points
ID: 21770184
Does this work:

$pricelist = "select * from PRODATA!ICITEM01 LEFT JOIN PRODATA!VSPROT01 ON Icitem01.item = Vsprot01.item AND Vsprot01.menuid = '$CustMenu->value' WHERE Icitem01.market1>0 AND Vsprot01.cntpa > 0 ORDER BY Icitem01.itmdesc";

or isn't Vsprot01.cntpa a field?
0
 
LVL 5

Author Comment

by:SuprSpy79
ID: 21770198
i needed to add this but was too late to edit

I also do not need the fields from the icitem01 that dont have a matching item in the vsproto01 as i do in the first select, since i only want the records where cntpa is greater than 0 but i need some of the field information from the master table icitem01.
0
 
LVL 14

Expert Comment

by:tusharkanvinde
ID: 21770319
To get only those records that have matching records in vsproto01 add

AND VSPROTO01.item is not null

in the WHERE clause

I think you should move the

AND Vsprot01.menuid = '$CustMenu->value'

to the WHERE clause since it does not seem to have anything to do with the join
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Author Comment

by:SuprSpy79
ID: 21770351
actually isnt it already doing that by adding the AND Vsprot01.cntpa > 0 at the end?
0
 
LVL 48

Expert Comment

by:hernst42
ID: 21770363
sorry not really getting the problem or the requirement you need.
0
 
LVL 5

Author Comment

by:SuprSpy79
ID: 21770454
the AND Vsprot01.menuid = '$CustMenu->value'  had to be before the where. When i moved it after the where it was not giving me the records that didnt match according to menu id. for my first select I needed to include only the records from vsprot01 that had a matching menu id while still retaining the rest of the records from the master item list.

hernst i think your first solution is working, ill just close this question and ill reopen it if i get confused later.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

These days socially coordinated efforts have turned into a critical requirement for enterprises.
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

708 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

15 Experts available now in Live!

Get 1:1 Help Now