SEO

#1 (**permalink**) 03-01-2008, 02:04 PM

hi..i'm new in sql progaming,
i try to make make a query that in table field "match" return to "1"
if no member record in another table and return to "0" if there is any
record member :

ex
table member:
member id
A 12
B 14

Table Incoming.
member note match
C bla..bla 1
A bla..bla 0
D bla..bla 1
...... ....... .....

can anyone help me please?
D

#2 (**permalink**) 03-01-2008, 02:04 PM

neolempires2@gmail.com wrote:

> hi..i'm new in sql progaming,
> i try to make make a query that in table field "match" return to "1"
> if no member record in another table and return to "0" if there is any
> record member :
>
> ex
> table member:
> member id
> A 12
> B 14

You have a table and a column of that table with the same
name? Eww, fix that.

> Table Incoming.
> member note match
> C bla..bla 1
> A bla..bla 0
> D bla..bla 1

Assuming that the Incoming table already has rows with correct data
in all columns except possibly 'match':

update Incoming
set match = 0
where member in (select member from member)

update Incoming
set match = 1
where member not in (select member from member)

#3 (**permalink**) 03-01-2008, 02:04 PM

Something like this should work if there is at
most 1 matching row in the other table:

select
M.member,
M.note,
case when Another.member is null
then 1
else 0 end as match
from member as M
left outer join Another
on Another.member = M.member

If there could be multiple matching rows, you have to do
something that will likely be less efficient:

select
M.member,
M.note,
case when exists (
select * from Another
where Another.member = M.member
) then 1 else 0 end as match
from member as M

-- Steve Kass
-- Drew University
-- http://www.stevekass.com

neolempires2@gmail.com wrote:
> hi..i'm new in sql progaming,
> i try to make make a query that in table field "match" return to "1"
> if no member record in another table and return to "0" if there is any
> record member :
>
> ex
> table member:
> member id
> A 12
> B 14
>
>
> Table Incoming.
> member note match
> C bla..bla 1
> A bla..bla 0
> D bla..bla 1
> ..... ....... .....
>
> can anyone help me please?
> D
>

#4 (**permalink**) 03-01-2008, 02:05 PM

it's work! thx steve.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Match field and return to "1"