If in a where clause?

[lunchbox88]

In my where clause, I need to compare a field in table_1 to a field in table_2. My problem is that the field in table_1 is a text field, and the field in table_2 is a numeric id...so...

table_1.type = "blue" and the equivalent in table_2 would be table_2.type_id = 4

So I essentially need something like
where
if table_1.type = blue and table_2.type_id = 4 then true.

Is something like this even possible?

Does this post even make sense?

 

[bborissov]

Sure, why not. And you don't have to use any IFs or CASE (that is the inline IF in T-SQL)

SELECT * 
       FROM Table1
INNER JOIN Table2 ON Table1.PK = Table2.FK
WHERE Table1.Type = 'blue' AND Table2.Type = 4

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[gmmastros]

If Table_1.Type is a Text data type, you cannot compare it with [!]=[/!]. Instead, you can use Like.

Using Boris's example...

SELECT *
FROM   Table1
INNER JOIN Table2 ON Table1.PK = Table2.FK
WHERE Table1.Type [!]Like[/!] 'blue' AND Table2.Type = 4

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lunchbox88]

Ok, that makes sense. If I needed to check 2 situations like this would it be

Select *
from table1
where table1.type like 'blue' and table2.type_id = 4
OR table1.type like 'red' and table2.type_id = 2

?

How about if I need other where clauses as well?

select *
from table1
where table1.name = table2.name
and table1.date = table2.date
and table1.type like 'blue' and table2.type_id = 4
OR table1.type like 'red' and table2.type_id = 2

?

 

[bborissov]

TEXT!?!?
I missed that part of the post.
Sorry lunchbox88.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[bborissov]

Just put appropriate brackets:

select *
from table1
where table1.name = table2.name
and   table1.date = table2.date
and   (table1.type like 'blue' and table2.type_id = 4
    OR table1.type like 'red'  and table2.type_id = 2)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[lunchbox88]

Thanks much, but now I have to make it a bit more complex...

Ok, so

we have this

select *
from table1
where table1.name = table2.name
and   table1.date = table2.date
and   (table1.type like 'blue' and table2.type_id = 4
    OR table1.type like 'red'  and table2.type_id = 2)

Now, what would I do if the type 'blue' is the type_id of 4, but any of the other types ('red', 'orange', 'yellow',or anything not 'blue') default to a type_id of 2?

could I do it this way:

select *
from table1
where table1.name = table2.name
and   table1.date = table2.date
and   (table1.type like 'blue' and table2.type_id = 4
    OR not(table1.type like 'blue')  and table2.type_id = 2)

 

[gmmastros]

select *
from table1
where table1.name = table2.name
and   table1.date = table2.date
and   ([!]([/!]table1.type like 'blue' and table2.type_id = 4[!])[/!]
    OR [!]([/!]table1.type like 'red'  and table2.type_id = 2[!])[/!]
      ) 
      And OtherColumn = OtherValue

My number 1 rule about OR's in a where is to use a lot of parenthesis. It will keep you out of trouble.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom