Retrieve the rows with some complicated conditions

[klwong]

Hi,
I have these two tables:

Table stringwid
Field: wid nvarchar(10)
content: 01010101
02010101

Table stringrule
Field: rid integer, rulewid nvarchar(10)
content: 1 01010101
1 02
2 0101
2 0201
3 01

I've made the following SQL:
select stringwid.wid as purewid, rulewid, stringrule.rid
from stringwid, stringrule
where stringwid.wid like rulewid+'%' order by rid

And the results follow:
purewid rulewid rid
01010101 01010101 1
02010101 02 1
02010101 0201 2
01010101 0101 2
01010101 01 3

Actually, I want the result only have the rids with 1 and 2
because the corresponding rulewids are both similar to purewid. i.e. I want to retrieve the count of rid = the number of row of stringwid table..
How to do this?

 

[sguslan]

If I understand you correctly, you need to show rows with RID column=1 or 2, then the following will do:

select stringwid.wid as purewid, rulewid, stringrule.rid
from stringwid, stringrule
where stringwid.wid like rulewid+'%' and
rid in(1,2)
order by rid

 

[klwong]

Yes, I want to get the rows with RID 1 and 2

but before getting the result, I don't know the answer I get should be 1 and 2, the reasons to get those because the corresponding rulewids both are similar to stringwid.wid.
ie.
(in the two rows of RID 1:
row one: rulewid is 01010101 similar to row one of stringwid : 01010101
row two: rulewid is 02 similar to row two of stringwid: 02010101

in the two rows of RID 2:
row one: rulewid is 0101 similar to row one of stringwid : 01010101
row two: rulewid is 0201 similar to row two of stringwid: 02010101
)

However, I don't want to retrieve RID 3 because only one is similar with the stringwid . e.g.
(
in the row of RID 3:
rulewid is 01 similar to row one of stringwid : 01010101
However, it doesn't has the row which is similar to 02010101
)

How to write this sql?