SQL Query - Help

[J P Singh]

Can someone please help me with query.

We are using this query in one of our asp pages. As I didn't write this
query in the firstplace I am finding it hard to add the two conditions I
want.

I want to add two conditions in the where clause like the one below

EmpProfile.Left = False and Holidayrequest.Status = 3 to the overall
condition


sqlstr = "SELECT [FirstName] & ' ' & [LastName] AS
Expr1,IIf(DateSerial(Year([fdate]),Month([fdate]),1)<DateSerial(" & selyear
& "," & selmonth & ",1),1,CInt(Day([FDate]))) AS StartDay, IIf(
DateSerial(Year([TDate]),Month([TDate]),1)>DateSerial(" & selyear & "," &
selmonth & ",1),Day(DateSerial(" & selyear & "," & selmonth &
",0))+CInt(Day([tDate])),CInt(Day([tDate]))) AS EndDay,
HolidayRequests.FDate, HolidayRequests.TDate, CInt(Month([FDate])) AS
StartMonth, CInt(Month([tDate])) AS EndMonth "

sqlstr = sqlstr & "FROM EmpProfile INNER JOIN HolidayRequests ON
EmpProfile.EmployeeNumber = HolidayRequests.Userid "

sqlstr = sqlstr & "WHERE (((Month([FDate]))=" & selmonth & ") AND
((Year([FDate]))=" & selyear & ") AND EmpProfile.Department='" & seldept &
"') OR (((Month([FDate]))="


The table structure is as below

Empprofile

EmployeeNumber (Number)
FirstName (Text)
LastName (Text)
Department (Text)
Left (Boolean)

HolidayRequests

UsedId (number)
Fdate (Date)
Tdate (Date)
Status (Number)

[Ray at]

Good lord.

modified snippet:

sqlstr = sqlstr & "WHERE (((Month([FDate]))=" & selmonth & ") AND
((Year([FDate]))=" & selyear & ") AND EmpProfile.Department='" & seldept &
"') OR (((Month([FDate]))=" ThereShouldBeAVariableHere & " AND
(EmpProfile.Left = FALSE AND Holidayrequests.status=3)"

Ray at work

"J P Singh" <noemail@asIhatespam> wrote in message
news:uE3dkeWcDHA.2820@tk2msftngp13.phx.gbl...

> Can someone please help me with query.
>
> We are using this query in one of our asp pages. As I didn't write this
> query in the firstplace I am finding it hard to add the two conditions I
> want.
>
> I want to add two conditions in the where clause like the one below
>
> EmpProfile.Left = False and Holidayrequest.Status = 3 to the overall
> condition
>
>
> sqlstr = "SELECT [FirstName] & ' ' & [LastName] AS
> Expr1,IIf(DateSerial(Year([fdate]),Month([fdate]),1)<DateSerial(" &

selyear

> & "," & selmonth & ",1),1,CInt(Day([FDate]))) AS StartDay, IIf(
> DateSerial(Year([TDate]),Month([TDate]),1)>DateSerial(" & selyear & "," &
> selmonth & ",1),Day(DateSerial(" & selyear & "," & selmonth &
> ",0))+CInt(Day([tDate])),CInt(Day([tDate]))) AS EndDay,
> HolidayRequests.FDate, HolidayRequests.TDate, CInt(Month([FDate])) AS
> StartMonth, CInt(Month([tDate])) AS EndMonth "
>
> sqlstr = sqlstr & "FROM EmpProfile INNER JOIN HolidayRequests ON
> EmpProfile.EmployeeNumber = HolidayRequests.Userid "
>
> sqlstr = sqlstr & "WHERE (((Month([FDate]))=" & selmonth & ") AND
> ((Year([FDate]))=" & selyear & ") AND EmpProfile.Department='" & seldept

&

> "') OR (((Month([FDate]))="
>
>
> The table structure is as below
>
> Empprofile
>
> EmployeeNumber (Number)
> FirstName (Text)
> LastName (Text)
> Department (Text)
> Left (Boolean)
>
> HolidayRequests
>
> UsedId (number)
> Fdate (Date)
> Tdate (Date)
> Status (Number)
>
>

[CoffeeCup]

I?m trying to get a SQL query to return data from 3 tables.

I need all the data in my tbl_producs table to be returned, the other two
table are linked but some fields are empty i.e. product 1 might only have a
cat3ID and not a brandID. Product 2 might have both etc.

I keep getting a ? ?Syntax error (missing operator) in query expression
'tbl_category_3.cat3ID = tbl_products.cat3ID RIGHT JOIN tbl_brands ON
tbl_brands.brandID = tbl_products.brandID'. ?

Here?s my query ?

<cfquery datasource="#REQUEST.DataSource#" name="ProdList">
SELECT tbl_products.productsID,
tbl_products.cat3ID,
tbl_products.branchID,
tbl_products.brandID,
tbl_products.prod_name,
tbl_category_3.cat3ID,
tbl_category_3.cat3_name,
tbl_brands.brandID,
tbl_brands.brand_name

FROM tbl_category_3
LEFT JOIN tbl_products
ON tbl_category_3.cat3ID = tbl_products.cat3ID
RIGHT JOIN tbl_brands
ON tbl_brands.brandID = tbl_products.brandID

WHERE tbl_products.branchID = '#branchID#'
ORDER BY prod_name
</cfquery>

Any help is greatly appreciated

[mxstu]

What database are you using?

[CoffeeCup]

I'm using MS Access

[mxstu]

I think Access requires parenthesis around each JOIN in the FROM statement I don't have Access on this machine so I cannot give you the correct syntax.

[CoffeeCup]

Thanks "mxstu"

I will give it a go see what happens...

[mxstu]

You may want to use the query builder in Access just to give you an idea of the syntax. I wouldn't use the Access generated code for your cfquery though... it's usually quite ugly ;-)

[CoffeeCup]

I tried the query builder in access.

It returns the data, but only the recordes that have data in all filed. So
catID fields where there is not data it does not return the data???

Here's the query..

<cfquery datasource="#REQUEST.DataSource#" name="ProdList">
SELECT tbl_category_2.cat2ID AS tbl_category_2_cat2ID,
tbl_category_2.cat2_name,
tbl_brands.brandID AS tbl_brands_brandID,
tbl_brands.brand_name,
tbl_category_3.cat3ID AS tbl_category_3_cat3ID,
tbl_category_3.cat3_name,
tbl_products.productsID,
tbl_products.cat2ID AS tbl_products_cat2ID,
tbl_products.cat3ID AS tbl_products_cat3ID,
tbl_products.branchID, tbl_products.brandID AS tbl_products_brandID,
tbl_products.prod_name
FROM tbl_category_3
INNER JOIN (tbl_category_2 INNER JOIN (tbl_brands INNER JOIN tbl_products ON
tbl_brands.brandID = tbl_products.brandID) ON tbl_category_2.cat2ID =
tbl_products.cat2ID) ON tbl_category_3.cat3ID = tbl_brands.cat3ID

WHERE tbl_products.branchID = '#branchID#'
ORDER BY prod_name
</cfquery>

[mxstu]

That's because you're using all INNER joins. Now that you understand what parenthesis are required, uou need to change the joins to the approriate type.

[CoffeeCup]

Thanks "mxstu"

Changed the joins to right joins and it works.

By the way, if i change the database to SQL will i need to remover the parenthesis?

Thank for your help

[mxstu]

If you mean sql server, then no you should not *have* to remove the parenthesis, but it would be cleaner if you did.

[CoffeeCup]

Thanks will work on this