Improve company productivity with a Business Account.Sign Up

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

Need Help with a select

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
SuprSpy79
Asked:
SuprSpy79
  • 3
  • 2
1 Solution
 
hernst42Commented:
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
 
SuprSpy79Author Commented:
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
 
tusharkanvindeCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
SuprSpy79Author Commented:
actually isnt it already doing that by adding the AND Vsprot01.cntpa > 0 at the end?
0
 
hernst42Commented:
sorry not really getting the problem or the requirement you need.
0
 
SuprSpy79Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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