add a table on from clause with left join

Question

hi please check with me this sql query

SELECT 
    CAISSE.*
    ,IIf(USERS.USR_NOM_L IS NULL,CAISSE.USR_L, USERS.USR_NOM_L + ' ' + USERS.USR_PREN_L) AS CLIENT_L
    ,IIf(SOCIETE.SOC_ID = 0 ,CAISSE.SOC_L,SOCIETE.REF_L) AS SOC_REF_L 
FROM 
    (   CAISSE 
        LEFT JOIN USERS ON CAISSE.USR_ID = USERS.USR_ID
    ) 
    LEFT JOIN SOCIETE ON CAISSE.SOC_ID = SOCIETE.SOC_ID  
WHERE 
    1=1 ;

I need to add "and where SOCIETE.SITE='SiteName'" but this did not work because I didn't already selected SOCIETE table in the FROM clause. How can i do this?

@Strawberry It's a function used in SQL Server. Most likely the question is tagged wrong. Salah, what DBMS are you using? — fancyPants, Aug 12 '14 at 09:29
the problem is when i add the condition in my left join i have "Expression join non supported" — Salah Smiti, Aug 12 '14 at 09:37
`SELECT CAISSE.*,IIf(USERS.USR_NOM_L IS NULL,CAISSE.USR_L, USERS.USR_NOM_L + ' ' + USERS.USR_PREN_L) AS CLIENT_L,IIf(SOCIETE.SOC_ID = 0 ,CAISSE.SOC_L,SOCIETE.REF_L) AS SOC_REF_L FROM (CAISSE LEFT JOIN USERS ON CAISSE.USR_ID = USERS.USR_ID) LEFT JOIN SOCIETE ON CAISSE.SOC_ID = SOCIETE.SOC_ID AND SOCIETE.SITE='SITENAME' WHERE 1=1 ;` — Salah Smiti, Aug 12 '14 at 09:39
When you have more than 1 join you need parentheses for each respective join. http://stackoverflow.com/questions/7854969/sql-multiple-join-statement .. And I don't understand `WHERE 1=1`? Is that serving a purpose? — Mark C., Aug 12 '14 at 12:26
And @Strawberry, `IIF` is `If-Then-Else` syntax in function form in `MS Access` (in this context anyhow). I believe SQL Server has begun supporting it, but I think it came out in SSMS 2012. — Mark C., Aug 12 '14 at 12:31
thanks @overmind i put where 1=1 because i have this structure after that `if(page.user.isinrole("admin") then sqlqery +="and condition" if(page.user.isinrole("user") then sqlqery +="and othercondition"` ... — Salah Smiti, Aug 12 '14 at 12:59

score 0 · Answer 1 · answered Aug 13 '14 at 09:08

well i found a solution for my problem thanks every body

SELECT * FROM (SELECT CAISSE.*,IIf(USERS.USR_NOM_L IS NULL,CAISSE.USR_L,

USERS.USR_NOM_L + ' '     + USERS.USR_PREN_L) AS CLIENT_L,

IIf(SOCIETE.SOC_ID = 0 ,CAISSE.SOC_L,SOCIETE.REF_L) AS SOC_REF_L,SOCIETE.SITE


FROM (CAISSE LEFT JOIN USERS ON CAISSE.USR_ID = USERS.USR_ID) 

LEFT JOIN SOCIETE ON CAISSE.SOC_ID = SOCIETE.SOC_ID)


WHERE SOCIETE.SITE='SiteName';

add a table on from clause with left join

1 Answers1