?
Solved

Need Help with a select

Posted on 2008-06-12
6
Medium Priority
?
259 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 48

Accepted Solution

by:
hernst42 earned 2000 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
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

 
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

Create CentOS 7 Newton Packstack Running Keystone

A bug was filed against RDO for the installation of Keystone v3. This guide is designed to walk you through the configuration for using Keystone v3 with Packstack. You will accomplish this using various repos and the Answers file.

Question has a verified solution.

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

This article discusses four methods for overlaying images in a container on a web page
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…
Suggested Courses

777 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