Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
query where statement two row in same column
- Thread starter yoyok007
- Start date
- Thread starter
- #3
below are my sample table
-------------------------
id(pk) | sy_id | dm_id
-------------------------
1 | 1 | 1
2 | 2 | 1
3 | 1 | 2
4 | 4 | 3
--------------------------
as my first post "select * from table where sy_id = 1 and sy_id = 2"
because i want to get the row for column dm_id = 1
hope you guys understand my explaination. thnx again
-------------------------
id(pk) | sy_id | dm_id
-------------------------
1 | 1 | 1
2 | 2 | 1
3 | 1 | 2
4 | 4 | 3
--------------------------
as my first post "select * from table where sy_id = 1 and sy_id = 2"
because i want to get the row for column dm_id = 1
hope you guys understand my explaination. thnx again
I do understand what you're asking for which is why based on the info provided I said to use OR.... change the AND in your query to OR.
select * from table where sy_id = 1 OR sy_id = 2
But given this new info it shows tha using OR will give you the 2 rows you seek plus an additional row.
But note -- your AND condition will never work. The sy_id cannot be 1 AND 2 (in the same row).
So....to get the 2 rows you see AND ONLY the 2 rows you seek you will need to add additional criteria to your select.
Like this....
select * from table where sy_id = 1 OR sy_id = 2 and dm_id <> 2
or
select * from table where dm_id = 1
-- Jason
"It's Just Ones and Zeros
select * from table where sy_id = 1 OR sy_id = 2
But given this new info it shows tha using OR will give you the 2 rows you seek plus an additional row.
But note -- your AND condition will never work. The sy_id cannot be 1 AND 2 (in the same row).
So....to get the 2 rows you see AND ONLY the 2 rows you seek you will need to add additional criteria to your select.
Like this....
select * from table where sy_id = 1 OR sy_id = 2 and dm_id <> 2
or
select * from table where dm_id = 1
-- Jason
"It's Just Ones and Zeros
- Thread starter
- #6
thanks for reply guys, but the table dont actually like above,for some case, it can be like below
-------------------------
id(pk) | sy_id | dm_id
-------------------------
1 | 1 | 1
2 | 2 | 1
3 | 1 | 2
4 | 4 | 3
5 | 1 | 4
6 | 3 | 4
7 | 4 | 4
--------------------------
if you can see, the desease dm_id=4 has 3 symptom sy_id, 1,3,4
ya, i know to use AND is imposibble, and i think for OR also not kinda work out dude
any suggest? really appreciate it guys
-------------------------
id(pk) | sy_id | dm_id
-------------------------
1 | 1 | 1
2 | 2 | 1
3 | 1 | 2
4 | 4 | 3
5 | 1 | 4
6 | 3 | 4
7 | 4 | 4
--------------------------
if you can see, the desease dm_id=4 has 3 symptom sy_id, 1,3,4
ya, i know to use AND is imposibble, and i think for OR also not kinda work out dude
any suggest? really appreciate it guys
- Thread starter
- #9
the situation is like this, this is for desease detection, to make it simple, there are 2 table which are symptom and desease.
The desease are determine by its symptom and 1 desease may have 1 or more symptom.
the table above is for the desease table, sy_id is for symptom id(fk from symptom table) and dm_id is for desease master id (fk from desease master table)
to use the system, user will only choose the symptom (checkbox) then send the value to db.
The desease are determine by its symptom and 1 desease may have 1 or more symptom.
the table above is for the desease table, sy_id is for symptom id(fk from symptom table) and dm_id is for desease master id (fk from desease master table)
to use the system, user will only choose the symptom (checkbox) then send the value to db.
- Thread starter
- #10
coolgeek1987
Programmer
you need to use intersect
select dm_id from table where sy_id = 1
intersect
select dm_id from table where sy_id = 2
will return only the dm_id value = 1
select dm_id from table where sy_id = 1
intersect
select dm_id from table where sy_id = 2
will return only the dm_id value = 1
- Status
Similar threads
- Locked
- Question
- Locked
- Question