Multiple selection from the same table
-
Hi all, I'm stuck with selecting multiple conditions in same table. I'll explain in this way. I've a table called tblPackages as follows. package id value code ------- ---- ------ ------ pak 1 | 1 | 0 | 58 pak 2 | 3 | 0 | 58 pak 3 | 4 | 0 | 58 pak 1 | 3 | 10 | 31 pak 2 | 2 | 100 | 31 pak 3 | 3 | 8 | 31 so I want to find all the package details that id = 1 / code = 58 and id = 3 / code = 31. So the package should be 'pak 1' I can explain the same like this. Say I execute the following query select * from tblPackages where id = 1 and code = 58, the result is package id value code ------- ---- ------ ------ pak 1 | 1 | 0 | 58 and then I execute the following query, select * from tblPackages where id = 3 and code = 31, the result is package id value code ------- ---- ------ ------ pak 1 | 3 | 10 | 31 pak 3 | 3 | 8 | 31 so the common result is, package id value code ------- ---- ------ ------ pak 1 | 1 | 0 | 58 pak 1 | 3 | 10 | 31 That's what I want to get. From that later I want to get either code of 58 data or code of 31 data. Can anyone of you comment on me. I've try to get data separately and merge manually, but it's not easy as i though. thanks
I appreciate your help all the time... CodingLover :)
-
Hi all, I'm stuck with selecting multiple conditions in same table. I'll explain in this way. I've a table called tblPackages as follows. package id value code ------- ---- ------ ------ pak 1 | 1 | 0 | 58 pak 2 | 3 | 0 | 58 pak 3 | 4 | 0 | 58 pak 1 | 3 | 10 | 31 pak 2 | 2 | 100 | 31 pak 3 | 3 | 8 | 31 so I want to find all the package details that id = 1 / code = 58 and id = 3 / code = 31. So the package should be 'pak 1' I can explain the same like this. Say I execute the following query select * from tblPackages where id = 1 and code = 58, the result is package id value code ------- ---- ------ ------ pak 1 | 1 | 0 | 58 and then I execute the following query, select * from tblPackages where id = 3 and code = 31, the result is package id value code ------- ---- ------ ------ pak 1 | 3 | 10 | 31 pak 3 | 3 | 8 | 31 so the common result is, package id value code ------- ---- ------ ------ pak 1 | 1 | 0 | 58 pak 1 | 3 | 10 | 31 That's what I want to get. From that later I want to get either code of 58 data or code of 31 data. Can anyone of you comment on me. I've try to get data separately and merge manually, but it's not easy as i though. thanks
I appreciate your help all the time... CodingLover :)
It is not clear that what is your problem. As i understood your problem is to view both results of those query in a single output. To that you have to use
'UNION'.select * from tblPackages where id = 1 and code = 58
UNION
select * from tblPackages where id = 3 and code = 31It'll produce this output: package id value code ------- ---- ------ ------ pak 1 | 1 | 0 | 58 pak 1 | 3 | 10 | 31 You have to use
'DISTINCT'to eliminate duplicate entries. You can also try usingJOINbut I can't understand what is you problem. -
Hi all, I'm stuck with selecting multiple conditions in same table. I'll explain in this way. I've a table called tblPackages as follows. package id value code ------- ---- ------ ------ pak 1 | 1 | 0 | 58 pak 2 | 3 | 0 | 58 pak 3 | 4 | 0 | 58 pak 1 | 3 | 10 | 31 pak 2 | 2 | 100 | 31 pak 3 | 3 | 8 | 31 so I want to find all the package details that id = 1 / code = 58 and id = 3 / code = 31. So the package should be 'pak 1' I can explain the same like this. Say I execute the following query select * from tblPackages where id = 1 and code = 58, the result is package id value code ------- ---- ------ ------ pak 1 | 1 | 0 | 58 and then I execute the following query, select * from tblPackages where id = 3 and code = 31, the result is package id value code ------- ---- ------ ------ pak 1 | 3 | 10 | 31 pak 3 | 3 | 8 | 31 so the common result is, package id value code ------- ---- ------ ------ pak 1 | 1 | 0 | 58 pak 1 | 3 | 10 | 31 That's what I want to get. From that later I want to get either code of 58 data or code of 31 data. Can anyone of you comment on me. I've try to get data separately and merge manually, but it's not easy as i though. thanks
I appreciate your help all the time... CodingLover :)
Hi, You didn't quite specify what relates those to rows so I assume it's the package. In that case could have something like:
SELECT *
FROM tblPackages main
WHERE Id IN (1,3)
AND Code IN (58, 31)
AND Package = (SELECT DISTINCT Package
FROM (SELECT Package
FROM tblPackages
WHERE id = 1
AND code = 58) a,
(SELECT Package
FROM tblPackages
WHERE id = 3
AND code = 31) b
WHERE a.Package = b.Package)Don't mind about typos etc, it's not tested at all. In the above if you can have several matching package use IN instead of equality for the package comparison. Also the statement can be simplified so this is just one version.
The need to optimize rises from a bad design.My articles[^]
-
Hi all, I'm stuck with selecting multiple conditions in same table. I'll explain in this way. I've a table called tblPackages as follows. package id value code ------- ---- ------ ------ pak 1 | 1 | 0 | 58 pak 2 | 3 | 0 | 58 pak 3 | 4 | 0 | 58 pak 1 | 3 | 10 | 31 pak 2 | 2 | 100 | 31 pak 3 | 3 | 8 | 31 so I want to find all the package details that id = 1 / code = 58 and id = 3 / code = 31. So the package should be 'pak 1' I can explain the same like this. Say I execute the following query select * from tblPackages where id = 1 and code = 58, the result is package id value code ------- ---- ------ ------ pak 1 | 1 | 0 | 58 and then I execute the following query, select * from tblPackages where id = 3 and code = 31, the result is package id value code ------- ---- ------ ------ pak 1 | 3 | 10 | 31 pak 3 | 3 | 8 | 31 so the common result is, package id value code ------- ---- ------ ------ pak 1 | 1 | 0 | 58 pak 1 | 3 | 10 | 31 That's what I want to get. From that later I want to get either code of 58 data or code of 31 data. Can anyone of you comment on me. I've try to get data separately and merge manually, but it's not easy as i though. thanks
I appreciate your help all the time... CodingLover :)
Huh? I think this[^] holds the answer, and
... WHERE (id = 1 AND code = 58) OR (id = 3 AND code = 31)
should do it.
CodingLover wrote:
all the package details that id = 1 / code = 58 and id = 3 / code = 31
I guess your problem is linguistic: the "and" in the above sentence isn't really an "and" (a row couldn't have ID=1 AND ID=3 at the same time) it is more of an "and also", which actually indicates an "or" situation. :)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
Huh? I think this[^] holds the answer, and
... WHERE (id = 1 AND code = 58) OR (id = 3 AND code = 31)
should do it.
CodingLover wrote:
all the package details that id = 1 / code = 58 and id = 3 / code = 31
I guess your problem is linguistic: the "and" in the above sentence isn't really an "and" (a row couldn't have ID=1 AND ID=3 at the same time) it is more of an "and also", which actually indicates an "or" situation. :)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
Wouldn't that lead to a situation where the row
pak 3 | 3 | 8 | 31
is included? I got the impression that it should be eliminated from the result.
The need to optimize rises from a bad design.My articles[^]
yes it would. however I found "so I want to find all the package details that id = 1 / code = 58 and id = 3 / code = 31. So the package should be 'pak 1'" a bit confusing, it would result in two rows IMO. The enquirer will have to make up his mind and tell us what he really wants. :)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
yes it would. however I found "so I want to find all the package details that id = 1 / code = 58 and id = 3 / code = 31. So the package should be 'pak 1'" a bit confusing, it would result in two rows IMO. The enquirer will have to make up his mind and tell us what he really wants. :)
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.