Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query
| SQL Query [message #20674] |
Wed, 12 June 2002 22:47  |
Giri Gajjela
Messages: 14
Registered: June 2002
|
Junior Member |
|
|
Hi All,
I need one query which has two tables.
Table1
-------
Key1 Last_Name
1 John
2 Mike
3 Bichel
Table2
Key2 First_Name
1 Joe
1 Gray
2 Tyson
1 Greme
2 Anthony
3 Andy
I need the out put as follows:
Key1/Key2 Last_Name First_Name
-----------------------------------------------------------------------
1 John Joe Gray Greme
2 Mike Tyson Anthony
3 Bichel Andy
Basically all the First_Name columns from Table2 should appear next to eachother for a given key.
TIA
Thanks & Regards,
- Giri Gajjela
|
|
|
|
| Re: SQL Query [message #20678 is a reply to message #20674] |
Thu, 13 June 2002 05:09  |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
I don't know of a way to do this with native SQL, but if you are using Oracle and PL/SQL, it is pretty straightforward:
08:54:38 ==> create table table1 (key1 number, last_name varchar2(20));
Table created.
08:54:38 ==> create table table2 (key2 number, first_name varchar2(20));
Table created.
08:54:38 ==> insert into table1 values (1, 'John');
1 row created.
08:54:38 ==> insert into table1 values (2, 'Mike');
1 row created.
08:54:38 ==> insert into table1 values (3, 'Bichel');
1 row created.
08:54:38 ==> insert into table2 values (1, 'Joe');
1 row created.
08:54:38 ==> insert into table2 values (1, 'Gray');
1 row created.
08:54:38 ==> insert into table2 values (2, 'Tyson');
1 row created.
08:54:39 ==> insert into table2 values (1, 'Greme');
1 row created.
08:54:39 ==> insert into table2 values (2, 'Anthony');
1 row created.
08:54:39 ==> insert into table2 values (3, 'Andy');
1 row created.
08:54:40 ==> begin
08:54:47 2 for c1 in (select key1, Last_name from table1)
08:54:47 3 loop
08:54:47 4 dbms_output.put( c1.key1||' '||c1.last_name);
08:54:47 5 for c2 in (select First_name from table2 where key2 = c1.key1)
08:54:47 6 loop
08:54:47 7 dbms_output.put(' '||c2.first_name);
08:54:47 8 end loop;
08:54:47 9 dbms_output.put_line(' ');
08:54:47 10 end loop;
08:54:48 11 end;
08:54:49 12 /
1 John Joe Gray Greme
2 Mike Tyson Anthony
3 Bichel Andy
PL/SQL procedure successfully completed.
|
|
|
|
| Re: SQL Query [message #20686 is a reply to message #20674] |
Thu, 13 June 2002 14:16 |
sridhar
Messages: 119
Registered: December 2001
|
Senior Member |
|
|
Well, this is SQL forum, so here is the SQL solution for the same,
P.S Please note that this method must know about number
of first_name occurances. I am making an assumption here that each person may have upto two first names.
**It works for the data you have shown as sample**
SELECT key,
last_name || ' ' ||
max(decode(rn, 1, first_name, null))
|| ' ' ||
max(decode(rn, 2, first_name, null)) full_name
FROM
(SELECT a.key1 key, a.last_name, b.first_name, dense_rank() over
(PARTITION BY a.last_name order by b.first_name) rn
FROM table1 a, table2 b
WHERE a.key1 = b.key2)
GROUP BY key, last_name;
Magic!!
SriDHAR
|
|
|
|
|
|
| Re: SQL Query [message #20695 is a reply to message #20686] |
Fri, 14 June 2002 06:16 |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
It is a nifty solution, but unfortunately, you feel free to make assumptions about the data. This is a dangerous practice. And in fact, your solution does NOT work for the data presented because you made an assumption that was wrong. For Key1, you should show "1 John Joe Gray Greme" and your solution shows "1 John Gray Greme".
I'm sure you could modify it to get this result, but would it handle four first names? Thirty? A hundred?
So I am back to the fact that native SQL does not readily support the requirements without making assumptions about the data, which is not a good idea. In PL/SQL, the solution is straightforward, but apparently this is not an option. So I don't see an easy answer. At least, you haven't provided it.
|
|
|
|
| Re: SQL Query [message #20700 is a reply to message #20686] |
Fri, 14 June 2002 08:07 |
sridhar
Messages: 119
Registered: December 2001
|
Senior Member |
|
|
Jon,
I did not notice that there are three first names.
But the SQL could be tweaked accordingly,
SELECT key,
last_name || ' ' ||
max(decode(rn, 1, first_name, null))
|| ' ' ||
max(decode(rn, 2, first_name, null))
|| ' ' ||
max(decode(rn, 3, first_name, null)) full_name
FROM
(SELECT a.key1 key, a.last_name, b.first_name, dense_rank() over
(PARTITION BY a.last_name order by b.first_name) rn
FROM table1 a, table2 b
WHERE a.key1 = b.key2)
GROUP BY key, last_name;
Now let us talk about the rest.
Questions in this forum are specific to their problems.
Also I am giving the user eough idea about what solution does. By saying that I am writing the query for only two first names, I am simplifying the coding, Why not? When people ask a question about their problem on their real tables, our solution can base the solution on example tables like emp and dept and example data, the intent there is to show them how to solve their own problem by demonstrating the IDEA.
"but would it handle four first names? Thirty? A hundred?"
Yes, why not? We can even write a SQL for unknown number of columns. As per your argument, if user wants a logic for 100 columns, you would sit and type 100 columns in your SELECT for him? I mean how stupid is that? We can always give him the concept and leave the typing part to him. You are saying that, it is not good enough?
"At least, you haven't provided it"
What is that I did not prove?
Finally, user asked for a SQL solution in SQL forum.
I thought it would be appropriate to give a SQL solution, I do not understand what made you mad?
The fact that you admitted that you do not know how to do it in SQL in your original posting? Sorry buddy, better luck next time.
Have a good day,
SriDHAR
|
|
|
|
| Re: SQL Query [message #20703 is a reply to message #20686] |
Fri, 14 June 2002 08:45 |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
I SAID you could easily tweak it for three...but having done that would it work for four? NO. Tweaking it for four, would it work for thirty? NO. Tweaking if for thirty, would it work for a hundred? NO. Tweaking it for a hundred, would it work for "n"? NO. I didn't want it for 100, I want it for "n". You didn't sense a pattern emerging from the questions? How stupid is THAT???
If you have a solution for n, where is it? That is what you did not provide (not "prove").
I understood that this is a SQL forum. I said I didn't have a solution in native SQL but did have one in PL/SQL, if that was an option, as it is for many in this forum.
I admitted that I did not know how to do this in SQL for n. You have not demonstrated that you do, either, but you want to pretend you do by simply making assumptions. You are either new to this or work in an academic setting where you can just "assume" real problems away.
So....BUDDY (yet another false assumption)...better luck next time to YOU!
|
|
|
|
| Re: SQL Query, addendum [message #20704 is a reply to message #20703] |
Fri, 14 June 2002 11:27 |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
|
Look back through the postings and find who was the first to use the word "stupid"...and it was directed at me ("you would .... How stupid is that?"). And you think I am the one creating a hostile environment? I respectfully disagree.
|
|
|
|
| Re: SQL Query [message #20706 is a reply to message #20703] |
Fri, 14 June 2002 12:02 |
Chris Powers
Messages: 2
Registered: June 2002
|
Junior Member |
|
|
Well, this is SQL forum, so here is the SQL solution for the same,
......
Magic!!
SriDHAR
So, what you are saying is that you developed all this heat based on the above? I do not know if it is me, but I got to tell ya, do not see how "Magic !!" word could upset you. I am with you on Sri saying "Well," a little bit, but If I were you I would not get so upset on that and start calling names. You were literally abusing the other person, his code, where as Sri did not even comment your solution so far, he does not seem bothered to me as much as you did although you delivered many comments on his code.
This is a discussion board, people come with several solutions, person who asked for help would pick which ever he likes. Giri already did TRASH yours if you did not notice. If I am to pick the solution on this problem, I see Sri's solution to be far more superior and appropriate to the given spec. I have right to select which ever I like, don't I? You won't be getting upset on me now, Are you? If you are upset, you know what my answer would be? "Well, it is a SQL forum, there is a PL/SQL forum right next door, take your solution over there".
Take it easy, you must learn to take some heat when you participate on discussion boards and not to blow it out of proportion.
I wish to drop this now, even if you come back with a page full of your story, I am not going to say anything back, so save it.
rgds,
Chris.
|
|
|
|
| Re: SQL Query [message #20721 is a reply to message #20703] |
Sat, 15 June 2002 04:30 |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
Well, of course, its your choice if you don't want to respond, but I want to.
No, not "all this heat" was generated by the above. That started it because he was condescending. I KNEW it was a SQL forum; I said I didn't have a solution (and so far I haven't seen one). I disagree that Giri "TRASH"ed my solution. He just very civilly said that PL/SQL was not an option. Fair enough...just trying to help. This is on an ORAFAQ.COM site, so many of us found it because we are ORAcle shops, where PL/SQL is an option - therefore, if I don't have a SQL solution, I offer a PL/SQL solution, in the spirit of being helpful. If it isn't helpful, as in this case, it can easily be ignored, and no offense is taken.
It really turned ugly when he called me stupid. You conveniently ignored that aspect. Since you don't plan on responding, I guess we'll never know if you condone that. I don't.
You have every right to accept whatever you want. In my opinion, neither one of us solved the problem in SQL. If you want to defend "solutions" that are dependent upon what the data looks like, feel free to do so, but I would love to debate that one in a public forum. I could do so with my brain tied behind my back! Developers that do that don't last long in my shop. Let's not forget that he stated unequivocally that his answer was correct given the data, but it wasn't. If you want to favor solutions that don't work over no solutions, well...okay. We disagree. Again, in my opinion, in either case you don't have a solution.
And he said in one response that he could write this to do an "unknown number of columns". I assume that what he meant by that is what I have called the "n" solution. But he hasn't shown it. Arrogance is not a good quality; its really inappropriate if you can't deliver.
Regardless of the technical aspect, though, the bottom line is this: If people treat me with respect, I will treat them with respect; if they don't, then expect the same from me, especially if you offer more heat than light.
|
|
|
|
| Re: SQL Query [message #20724 is a reply to message #20703] |
Sat, 15 June 2002 09:09 |
sridhar
Messages: 119
Registered: December 2001
|
Senior Member |
|
|
>> especially if you offer more heat than light.
Well, some people can offer both, here we go..
I am giving a solution for unknown number of columns,
SQL> select * from table1;
KEY1 LAST_NAME
---------- --------------------
1 Collins
2 Williams
3 Clark
SQL> select * from table2;
KEY2 FIRST_NAME
---------- --------------------
1 Kytes
1 John
2 Roath
2 Robert
3 Richard
3 Peter
SQL> SELECT 'SELECT key, last_name' str1 FROM DUAL
2 UNION ALL
3 SELECT '||' || ''' ''' || '|| max(decode(rn , ' || ROWNUM || ' , first_name, null))' str2
4 FROM table1 WHERE rownum <= (SELECT MAX(key1) FROM table1)
5 UNION ALL
6 SELECT 'FROM (SELECT a.key1 key, a.last_name, b.first_name,
7 row_number() over (PARTITION BY a.last_name order by b.key2) rn
8 FROM table1 a, table2 b WHERE a.key1 = b.key2)
9 GROUP BY key, last_name' str3 FROM DUAL
10 /
STR1
----------------------------------------------------------------------------------------------------
SELECT key, last_name
||' '|| max(decode(rn , 1 , first_name, null))
||' '|| max(decode(rn , 2 , first_name, null))
||' '|| max(decode(rn , 3 , first_name, null))
FROM (SELECT a.key1 key, a.last_name, b.first_name,
row_number() over (PARTITION BY a.last_name order by b.key2) rn
FROM table1 a, table2 b WHERE a.key1 = b.key2)
GROUP BY key, last_name
SQL> SELECT key, last_name
2 ||' '|| max(decode(rn , 1 , first_name, null))
3 ||' '|| max(decode(rn , 2 , first_name, null))
4 ||' '|| max(decode(rn , 3 , first_name, null))
5 FROM (SELECT a.key1 key, a.last_name, b.first_name,
6 row_number() over (PARTITION BY a.last_name order by b.key2) rn
7 FROM table1 a, table2 b WHERE a.key1 = b.key2)
8 GROUP BY key, last_name
9 /
KEY LAST_NAME||''||MAX(DECODE(RN,1,FIRST_NAME,NULL))||''||MAX(DECODE(RN,2,FIRST_NAME,NULL))||
---------- -----------------------------------------------------------------------------------------
1 Collins Kytes John
2 Williams Roath Robert
3 Clark Richard Peter
Does this deserve the word "Magic!" ?
If "Magic" bothers you how about "Yahoo!!!"
Take it easy buddy, no hard feelings,
I am just having fun.
SriDHAR
|
|
|
|
| Re: SQL Query [message #20740 is a reply to message #20703] |
Mon, 17 June 2002 04:42 |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
Sigh!! You don't get the concept of "n", do you? Yes, "yahoo" came to mind when I read this.
I'm tired of this. You said you could do this for an unknown number. Here is how your latest solution fails where the number of first names is nine:
08:14:45 ==> select * from table2 where key2 = 1;
KEY2 FIRST_NAME
--------- --------------------
1 Joe
1 Gray
1 Greme
1 Frank
1 Michael
1 Bob
1 Fred
1 Samuel
1 Larry
9 rows selected.
08:14:57 ==> SELECT key, last_name
08:15:07 2 ||' '|| max(decode(rn , 1 , first_name, null))
08:15:07 3 ||' '|| max(decode(rn , 2 , first_name, null))
08:15:07 4 ||' '|| max(decode(rn , 3 , first_name, null))
08:15:07 5 ||' '|| max(decode(rn , 4 , first_name, null))
08:15:07 6 ||' '|| max(decode(rn , 5 , first_name, null))
08:15:07 7 ||' '|| max(decode(rn , 6 , first_name, null))
08:15:07 8 full_name FROM (SELECT a.key1 key, a.last_name, b.first_name,
08:15:07 9 row_number() over (PARTITION BY a.last_name order by b.key2) rn
08:15:07 10 FROM table1 a, table2 b WHERE a.key1 = b.key2)
08:15:07 11 GROUP BY key, last_name;
KEY FULL_NAME
--------- -----------------------------------------------------------------------
1 John Joe Gray Frank Greme Michael Fred
2 Mike Tyson Anthony
3 Bichel Andy
Here is the result of your unknown number 'solution':
08:15:07 ==> SELECT 'SELECT key, last_name' str1 FROM DUAL
08:22:57 2 UNION ALL
08:22:57 3 SELECT '||' || ''' ''' || '|| max(decode(rn , ' || ROWNUM || ' , first_name, null))' str2
08:22:57 4 FROM table1 WHERE rownum <= (SELECT MAX(key1) FROM table1)
08:22:57 5 UNION ALL
08:22:57 6 SELECT 'FROM (SELECT a.key1 key, a.last_name, b.first_name,
08:22:57 7 row_number() over (PARTITION BY a.last_name order by b.key2) rn
08:22:57 8 FROM table1 a, table2 b WHERE a.key1 = b.key2)
08:22:57 9 GROUP BY key, last_name' str3 FROM DUAL;
STR1
------------------------------------------------------------------------------------------------------------------
SELECT key, last_name
||' '|| max(decode(rn , 1 , first_name, null))
||' '|| max(decode(rn , 2 , first_name, null))
||' '|| max(decode(rn , 3 , first_name, null))
||' '|| max(decode(rn , 4 , first_name, null))
FROM (SELECT a.key1 key, a.last_name, b.first_name,
row_number() over (PARTITION BY a.last_name order by b.key2) rn
FROM table1 a, table2 b WHERE a.key1 = b.key2)
GROUP BY key, last_name
6 rows selected.
08:22:58 ==> SELECT key, last_name
08:23:16 2 ||' '|| max(decode(rn , 1 , first_name, null))
08:23:16 3 ||' '|| max(decode(rn , 2 , first_name, null))
08:23:16 4 ||' '|| max(decode(rn , 3 , first_name, null))
08:23:17 5 ||' '|| max(decode(rn , 4 , first_name, null))
08:23:17 6 FROM (SELECT a.key1 key, a.last_name, b.first_name,
08:23:17 7 row_number() over (PARTITION BY a.last_name order by b.key2) rn
08:23:17 8 FROM table1 a, table2 b WHERE a.key1 = b.key2)
08:23:17 9 GROUP BY key, last_name;
KEY LAST_NAME||''||MAX(DECODE(RN,1,FIRST_NAME,NULL))||''||MAX(DECODE(RN,2,FIRST_NAME,NULL))||''||MAX(DECODE(
--------- --------------------------------------------------------------------------------------------------------
1 John Joe Gray Frank Greme
2 Mike Tyson Anthony
3 Bichel Andy
Even worse!!
Bob, Samuel and Larry are obviously missing in your latest solution and in your unknown solution, even more. Ever hear of testing?
So, Buster, you still haven't provided a solution - one you bragged you could. If you're going to be arrogant and condescending, you should at least be right! Do the words "better luck next time" ring a bell? But as I said, I'm tired of this...I won't be looking for a next time. End of discussion thread as far as I'm concerned.
|
|
|
|
| Re: SQL Query [message #20744 is a reply to message #20703] |
Mon, 17 June 2002 08:50 |
Allan Walter
Messages: 2
Registered: June 2002
|
Junior Member |
|
|
Sri,
If you are waiting for his feed back, I don't think he would come back, because the code works. To reconfirm the results, I tested the code at my end and it works like a charm.
SQL> SELECT * FROM TABLE1;
KEY1 LAST_NAME
---------- --------------------
1 x
2 y
SQL> SELECT * FROM TABLE2;
KEY2 FIRST_NAME
---------- ----------
1 a
1 b
1 d
1 c
1 e
1 f
1 g
1 h
1 i
1 j
1 k
1 l
1 m
1 n
1 o
1 p
2 aa
2 bb
18 rows selected.
SQL> SELECT 'SELECT key, last_name' str1 FROM DUAL
2 UNION ALL
3 SELECT '||' || ''' ''' || '|| max(decode(rn , ' || ROWNUM || ' ,
first_nam2
4 FROM table2 WHERE rownum <= (SELECT MAX(cnt) max_cnt FROM
5 (Select key2, count(*) cnt From table2 Group by key2))
6 UNION ALL
7 SELECT 'full_name FROM (SELECT a.key1 key, a.last_name,
b.first_name,
8 row_number() over (PARTITION BY a.last_name order by b.key2) rn
9 FROM table1 a, table2 b WHERE a.key1 = b.key2)
10 GROUP BY key, last_name' str3 FROM DUAL
11 /
STR1
--------------------------------------------------------------------------------
SELECT key, last_name
||' '|| max(decode(rn , 1 , first_name, null))
||' '|| max(decode(rn , 2 , first_name, null))
||' '|| max(decode(rn , 3 , first_name, null))
||' '|| max(decode(rn , 4 , first_name, null))
||' '|| max(decode(rn , 5 , first_name, null))
||' '|| max(decode(rn , 6 , first_name, null))
||' '|| max(decode(rn , 7 , first_name, null))
||' '|| max(decode(rn , 8 , first_name, null))
||' '|| max(decode(rn , 9 , first_name, null))
||' '|| max(decode(rn , 10 , first_name, null))
||' '|| max(decode(rn , 11 , first_name, null))
||' '|| max(decode(rn , 12 , first_name, null))
||' '|| max(decode(rn , 13 , first_name, null))
||' '|| max(decode(rn , 14 , first_name, null))
||' '|| max(decode(rn , 15 , first_name, null))
||' '|| max(decode(rn , 16 , first_name, null))
full_name FROM (SELECT a.key1 key, a.last_name, b.first_name,
row_number() over (PARTITION BY a.last_name order by b.key2) rn
FROM table1 a, table2 b WHERE a.key1 = b.key2)
GROUP BY key, last_name
18 rows selected.
SQL> SELECT key, last_name
2 ||' '|| max(decode(rn , 1 , first_name, null))
3 ||' '|| max(decode(rn , 2 , first_name, null))
4 ||' '|| max(decode(rn , 3 , first_name, null))
5 ||' '|| max(decode(rn , 4 , first_name, null))
6 ||' '|| max(decode(rn , 5 , first_name, null))
7 ||' '|| max(decode(rn , 6 , first_name, null))
8 ||' '|| max(decode(rn , 7 , first_name, null))
9 ||' '|| max(decode(rn , 8 , first_name, null))
10 ||' '|| max(decode(rn , 9 , first_name, null))
11 ||' '|| max(decode(rn , 10 , first_name, null))
12 ||' '|| max(decode(rn , 11 , first_name, null))
13 ||' '|| max(decode(rn , 12 , first_name, null))
14 ||' '|| max(decode(rn , 13 , first_name, null))
15 ||' '|| max(decode(rn , 14 , first_name, null))
16 ||' '|| max(decode(rn , 15 , first_name, null))
17 ||' '|| max(decode(rn , 16 , first_name, null))
18 full_name FROM (SELECT a.key1 key, a.last_name, b.first_name,
19 row_number() over (PARTITION BY a.last_name order by b.key2) rn
20 FROM table1 a, table2 b WHERE a.key1 = b.key2)
21 GROUP BY key, last_name
22 /
KEY
----------
FULL_NAME
--------------------------------------------------------------------------------
1
x a b d c e f g h i j k l m n o p
2
y aa bb
|
|
|
|
|
|
| Re: SQL Query - final note [message #20752 is a reply to message #20703] |
Tue, 18 June 2002 05:15 |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
I give credit where credit is due. Very creative solution. Even your initial effort, while incorrect, was creative; I think I originally used the word "nifty".
You are an excellent SQL developer, and as such, can add much value to this forum. However, if you care to be, you could be even more valuable if you are less arrogant and condescending. Here are some suggestions:
If you have a better suggestion, instead of putting someone else down, say, "Here's an idea. How about..." And instead of "Magic!", "Yahoo!", '"Shazaam!", "Hubba-hubba!" or "Damn, I'm good", try "Hope this helps" or just "HTH".
Try to avoid words like "stupid". Even though you say I misinterpretted - and I still doubt it - they are "red flag" words.
Also avoid "buddy", "pal", etc., especially when if you find yourself in another contentious exchange. People know sarcasm when they hear it. And if you think you're going to calm a situation by saying, "calm down, pal", you couldn't be farther from the truth and I think you know it. Nobody when they're angry wants to hear, "calm down, pal". However, "I'm sorry if I did something to upset you" usually gets good results.
All of that aside, congratulations on an excellent solution! But please do consider the rest of what I've said.
|
|
|
|
Goto Forum:
Current Time: Tue Apr 16 04:56:10 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
