Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Aug 2006
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Really need some help on Select Statement

    Hi,

    I have this code:

    Code:
    sqlLstProds="Select * From PRODUCT_DEF "&_
    	"WHERE productID IN(Select productID From PRODUCT_DEF WHERE (IDProductCriteria = 5) AND ([Value] = '"&getSession("ModelID")&"')) "&_
    	"  Order By IDProduct, IDProductCriteria"

    This queries the Product_Def table, however there is also a table called Product_sType, the primary key in that table is productID which corresponds to productID ub the Product_Def table.

    I need to add to the query a where clause, where active = 1
    active 0 is obviously discontinued products, how would I do this?

  • #2
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,273
    Thanks
    4
    Thanked 83 Times in 82 Posts
    And you posted this in the PHP forum verses the database forums why? You also seem to have cross posted this in the ASP forum. In the future please post in the appropriate forum and only once. This is clearly stated in our forum rules and guidelines.

    Moving to appropriate forum...
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #3
    Regular Coder
    Join Date
    Nov 2002
    Location
    Manchester, UK
    Posts
    533
    Thanks
    4
    Thanked 1 Time in 1 Post
    Code:
    sqlLstProds="Select * From PRODUCT_DEF "&_
    	"WHERE active = 1 AND productID IN(Select productID From PRODUCT_DEF WHERE (IDProductCriteria = 5) AND ([Value] = '"&getSession("ModelID")&"')) "&_
    	"  Order By IDProduct, IDProductCriteria"
    that should work?

  • #4
    Regular Coder
    Join Date
    Aug 2006
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Spookster View Post
    And you posted this in the PHP forum verses the database forums why? You also seem to have cross posted this in the ASP forum. In the future please post in the appropriate forum and only once. This is clearly stated in our forum rules and guidelines.

    Moving to appropriate forum...
    Yeah, I emailed the moderator, I did the thread in the PHP section by mistake, when but I couldn't delete it, even though the ALT on the Edit box says Edit/Delete. The problem is not with me but rather a site limitation.

  • #5
    Regular Coder
    Join Date
    Aug 2006
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by homerUK View Post
    Code:
    sqlLstProds="Select * From PRODUCT_DEF "&_
    	"WHERE active = 1 AND productID IN(Select productID From PRODUCT_DEF WHERE (IDProductCriteria = 5) AND ([Value] = '"&getSession("ModelID")&"')) "&_
    	"  Order By IDProduct, IDProductCriteria"
    that should work?
    Well that code assumes that the active column is in Product_def.
    This queries the Product_Def table, however there is also a table called Product_sType, the primary key in that table is productID which corresponds to productID ub the Product_Def table.

    So basically, it's somewhat of a join of some sort, like select * from Product_def, but only the rows where productID match the productID in Product_sType Where active = 1.

    And that's what I cannot do. If you could help me out on this I would apreciate it.

  • #6
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    try this:
    Code:
    sqlListProds = "SELECT * " & _
    "FROM PRODUCT_DEF AS pdef " & _
    "INNER JOIN PRODUCT_STYPE AS ptype ON pdef.productId = ptype.productId " & _
    "WHERE (IDProductCriteria = 5 ) " & _
    "AND ([Value] = '"&getSession("ModelID")&"') " & _
    "AND (Active = 1) " & _
    "ORDER BY IDProduct, IDProductCriteria"
    I am the luckiest man in the world

  • #7
    Regular Coder
    Join Date
    Aug 2006
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i got it
    Last edited by LondonBoy; 11-03-2006 at 05:04 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •