Solved

Need Help with a select

Posted on 2008-06-12
6
252 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

863 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

25 Experts available now in Live!

Get 1:1 Help Now