Thread: Sequential select queries...??
Hello, At first I thought what I was trying to do was simple and could be done easily - but alas, I've spent way too much time and could not figure out how to get the results in question. Let's say I have a table T comprised of id of type integer, name and description both of type text. What i'd like to do is the following: Select id from T where name = 'bleh'; and Select id from T where description = 'bleh'; and result both results in the same result set. That is, duplicate id's if they appear. So then I could do a GROUP BY and a COUNT to see how many appeared in only one, and how many appeared in both. Could someone help me? I've tried countless different sql queries, can't seem to get one to work. If I can just get those duplicate id's in the query.. then I'd be laughing and then I can complete my task. Thanks in advance, Mark
>Hello, >At first I thought what I was trying to do was simple and could be done >easily - but alas, I've spent way too much time and could not figure out >how to get the results in question. I think there's cause for excitement, because unless I'm interpreting you incorrectly, it IS easy to do what you want to do: Use the UNION functionality. If your table T is this id name description------------------------- 1 abc def 2 bcd abc 3 def ghi 4 jkl bcd 5 hij hij And you do this: SELECT id FROM T WHERE name = 'abc' UNION SELECT id FROM T WHERE desc = 'abc'; You'll get: id ---- 1 2 That ought to do it for you! Hope this helps, Robby Slaughter
Mark, > What i'd like to do is the following: > > Select id from T where name = 'bleh'; > > and > > Select id from T where description = 'bleh'; > > and result both results in the same result set. That is, duplicate > id's > if they appear. So then I could do a GROUP BY and a COUNT to see how > many appeared in only one, and how many appeared in both. What you want is UNION ALL. You also want to go out and purchase "SQL for Smarties" after you finish reading my e-mail. SELECT id FROM T WHERE name = 'bleh' UNION ALL SELECT id FROM T WHERE description = 'bleh'; This gives you both result sets, once right after the other. If you didn't want to see duplicate values (i.e. only one instance of each "id"), you would use simply UNION without the "ALL". This means that it is possible to get both your desired rowcounts out of a *single* query, using subselects. "SQL for Smarties" can help you learn to build this kind of query. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
I don't knoe, may be I don't understand the question, but for me its looking like UNION statement. For example: select id from T where name='bleh' UNION select id from T where description='bleh'; Aug 14, 20:40 -0400, Mark Mikulec wrote: > Hello, > > At first I thought what I was trying to do was simple and could be done > easily - but alas, I've spent way too much time and could not figure out > how to get the results in question. > > Let's say I have a table T comprised of id of type integer, name and > description both of type text. > > What i'd like to do is the following: > > Select id from T where name = 'bleh'; > > and > > Select id from T where description = 'bleh'; > > and result both results in the same result set. That is, duplicate id's > if they appear. So then I could do a GROUP BY and a COUNT to see how > many appeared in only one, and how many appeared in both. > > Could someone help me? I've tried countless different sql queries, can't > seem to get one to work. If I can just get those duplicate id's in the > query.. then I'd be laughing and then I can complete my task. > > Thanks in advance, > > Mark > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > my best regards, ---------------- Grigoriy G. Vovk
Well, a short answerto your direct question would be: Select id from T where name = 'bleh'UNION ALLSelect id from T where description = 'bleh'; But since you described what your trying to do, not just how your trying to do it, doesn't this do it for you? SELECT id from T where name = 'bleh' and description = 'bleh'; That should give you just the ones where it appears in both. Ross On Tue, Aug 14, 2001 at 08:40:50PM -0400, Mark Mikulec wrote: > Hello, > > At first I thought what I was trying to do was simple and could be done > easily - but alas, I've spent way too much time and could not figure out > how to get the results in question. > > Let's say I have a table T comprised of id of type integer, name and > description both of type text. > > What i'd like to do is the following: > > Select id from T where name = 'bleh'; > > and > > Select id from T where description = 'bleh'; > > and result both results in the same result set. That is, duplicate id's > if they appear. So then I could do a GROUP BY and a COUNT to see how > many appeared in only one, and how many appeared in both. > > Could someone help me? I've tried countless different sql queries, can't > seem to get one to work. If I can just get those duplicate id's in the > query.. then I'd be laughing and then I can complete my task. > > Thanks in advance, > > Mark > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
Select id from T where name = 'bleh' UNION ALL Select id from T where description = 'bleh'; Will get you the resultset you want, but: I don't believe that you can do a GROUP BY on it. If you just want counts, as you describe below, you could do something like: SELECT 'Only One', (SELECT count (id) from T where name = 'bleh' OR description = 'bleh') - (SELECT count (id) from T where name = 'bleh' AND description = 'bleh') UNION SELECT 'Both', (SELECT count (id) from T where name = 'bleh' AND description = 'bleh'); ----- Original Message ----- From: "Mark Mikulec" <mm98au@badger.ac.brocku.ca> To: <pgsql-sql@postgresql.org> Sent: Tuesday, August 14, 2001 7:40 PM Subject: Sequential select queries...?? > Hello, > > At first I thought what I was trying to do was simple and could be done > easily - but alas, I've spent way too much time and could not figure out > how to get the results in question. > > Let's say I have a table T comprised of id of type integer, name and > description both of type text. > > What i'd like to do is the following: > > Select id from T where name = 'bleh'; > > and > > Select id from T where description = 'bleh'; > > and result both results in the same result set. That is, duplicate id's > if they appear. So then I could do a GROUP BY and a COUNT to see how > many appeared in only one, and how many appeared in both. > > Could someone help me? I've tried countless different sql queries, can't > seem to get one to work. If I can just get those duplicate id's in the > query.. then I'd be laughing and then I can complete my task. > > Thanks in advance, > > Mark > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >
If you want to know for each individual one wether both equal or not you could do: SELECT *,NOT ((name=description) IS NULL) AND (name=description) AS both FROM t WHERE name='bleh' OR description='bleh'; If you wanted totals of the same you could do: SELECT count(*) AS tot,NOT ((name=description) IS NULL) AND (name=description) AS both FROM t WHERE name='bleh' OR description='bleh' GROUP BY NOT ((name=description) IS NULL AND (name=description); I think this should be more efficient than UNIONs, but am not an expert on the query planner or executor. - Stuart > -----Original Message----- > From: Mark Mikulec [SMTP:mm98au@badger.ac.brocku.ca] > Sent: Wednesday, August 15, 2001 1:41 AM > To: pgsql-sql@postgresql.org > Subject: Sequential select queries...?? > > Hello, > > At first I thought what I was trying to do was simple and could be done > easily - but alas, I've spent way too much time and could not figure out > how to get the results in question. > > Let's say I have a table T comprised of id of type integer, name and > description both of type text. > > What i'd like to do is the following: > > Select id from T where name = 'bleh'; > > and > > Select id from T where description = 'bleh'; > > and result both results in the same result set. That is, duplicate id's > if they appear. So then I could do a GROUP BY and a COUNT to see how > many appeared in only one, and how many appeared in both. > > Could someone help me? I've tried countless different sql queries, can't > seem to get one to work. If I can just get those duplicate id's in the > query.. then I'd be laughing and then I can complete my task. > > Thanks in advance, > > Mark >