Solved

Need Help with a select

Posted on 2008-06-12
6
253 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

786 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