...

View Full Version : Really need some help on Select Statement



LondonBoy
11-01-2006, 05:23 PM
Hi,

I have this 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?

Spookster
11-01-2006, 06:03 PM
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...

homerUK
11-01-2006, 06:14 PM
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?

LondonBoy
11-01-2006, 06:17 PM
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.

LondonBoy
11-01-2006, 06:20 PM
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.

Roelf
11-02-2006, 09:52 AM
try this:

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"

LondonBoy
11-03-2006, 04:20 PM
i got it



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum