sql select problem

[KryptoS]

Hello,

I'm having 2 tables, I want to select all values out of table1 where var1=7 (also if they not exist in table2). And in table2 only the values where var2=4.

table1        table2
ID            ID
var1          FK (with table1.ID)
...           var2
              var3
              ...

my sql is:

select table1.*, table2.*
  LEFT JOIN table2 ON table1.ID = table2.FK
  WHERE (table1.var=7 AND table2.var1=4)
     OR (table1.var=7 AND table2.var1 Is Null)

E.g. table1 where var=7 has 3 values. table2 where var=4 has only 1 value that corresponds with table1. So I want the 3 rows out of table1 and one of the 3 rows also the values out of table2.

You feel me coming... It's not working .
I hope I explained it correct.

Greetings

visit my website at

http://www.phpscriptor.com

 

[SQLSister]

Try

select table1.*, table2.*
  LEFT JOIN table2 ON table1.ID = table2.FK and table2.var1=4
  WHERE table1.var=7

And you do know to never use select * right? Especially when you have a join as you are returning at least one field twice and it is a waste of system resources. Return only the columns you need.



"NOTHING is more important in a database than integrity." ESquared

 

[KryptoS]

Hey,

thanks for reply. Didn't know you could use more conditions in the JOIN.

Yes I know about the * in the select. But this was just for making it easy.

visit my website at

http://www.phpscriptor.com

 

[SQLSister]

It is important when you are using a left join to add the condtions on the second table to the join instead of in the where clause. This is because if you put a condition conerning the second table in the where clause (other than where some field is null) you essentially change the join to an inner join because the condition must be met.



"NOTHING is more important in a database than integrity." ESquared