Home » SQL & PL/SQL » SQL & PL/SQL » Do I need a join?
| Do I need a join? [message #191035] |
Mon, 04 September 2006 04:29  |
Tom Dunne
Messages: 13
Registered: August 2006
Location: Dublin, Ireland
|
Junior Member |
|
|
Hi All,
firstly, great forum, I've learned so much since I joined up here a few weeks back.
My question: I am trying to query a table to retrurn data if a column has 'A' in it or if there are two rows, one with and 'A' and the other with a 'B'. For example:
Col1 Col2
A data
or
Col1 Col2
A data
B data
I reckon I am looking for a self-join (am I?), but I don't seem to be able to get the correct syntax. Here's what I have:
SELECT tb1.col1, tb1.col2
FROM mytable tb1, mytable tb2
WHERE tb1.col1='A' or (tb1.col1='A' and tb2.col1='B')
Any help appreciated.
|
|
|
|
|
|
| Re: Do I need a join? [message #191044 is a reply to message #191035] |
Mon, 04 September 2006 04:46  |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
I get the feeling that you are not supplying the whole story as the following would return the results based on your example:
select col1, col2
from mytable
where col1 in ('A','B');
|
|
|
|
|
|
| Re: Do I need a join? [message #191050 is a reply to message #191044] |
Mon, 04 September 2006 04:59 |
Tom Dunne
Messages: 13
Registered: August 2006
Location: Dublin, Ireland
|
Junior Member |
|
|
| JSI2001 wrote on Mon, 04 September 2006 10:46 |
I get the feeling that you are not supplying the whole story as the following would return the results based on your example:
select col1, col2
from mytable
where col1 in ('A','B');
|
That will return rows that have only A, only B or A and B. I am looking for rows that have either A, or both A and B. They can't have just B on their own.
Some background: It's a table to track member's qualifications. So I am looking for members that have an 'A' qualification (therefore there will be one row returned for this member) or members who have both an 'A' qualification and a 'B' qualification (therefore two rows returned for this member). Members having just a 'B' qualification are to be excluded.
Does that make it any clearer?
@venkat: I am not sure if I need a join here, I suppose I am asking if I do or not.
|
|
|
|
| Re: Do I need a join? [message #191051 is a reply to message #191049] |
Mon, 04 September 2006 05:00 |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
| Quote: |
Does that make it any clearer?
|
No.
Please supply create table and insert scripts to represent the data that you are working with. Also supply an example of the data that you expect to be returned.
Thanks
|
|
|
|
| Re: Do I need a join? [message #191055 is a reply to message #191035] |
Mon, 04 September 2006 05:11 |
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
|
Member |
|
|
ok Tom
then try this query
CREATE TABLE members(id VARCHAR2(10), qualification VARCHAR2(3));
INSERT INTO members( id,qualification) VALUES(12, 'A');
INSERT INTO members( id,qualification) VALUES(12, 'B');
INSERT INTO members( id,qualification) VALUES(13, 'A');
INSERT INTO members( id,qualification) VALUES(14, 'B');
COMMIT;
SELECT *
FROM members
WHERE qualification = 'A'
OR (qualification IN ('A', 'B'));
If you want he data of a particular member then add the id as the first condition in the where clause like:
SELECT *
FROM members
WHERE id =12
AND (qualification = 'A' OR (qualification IN ('A', 'B')));
Thks & Rgds
Venkat.
[Updated on: Mon, 04 September 2006 05:56] Report message to a moderator |
|
|
|
| Re: Do I need a join? [message #191056 is a reply to message #191051] |
Mon, 04 September 2006 05:12 |
Tom Dunne
Messages: 13
Registered: August 2006
Location: Dublin, Ireland
|
Junior Member |
|
|
Ok, table is like this:
Member_No. Mem_Seq Mem_Firstname Mem_Lastname Qualification
Sample data is as follows:
Member_No. Mem_Seq Mem_Firstname Mem_Lastname Qualification
123 9723 John Smith A
123 8103 John Smith B
124 9064 Mary McDoogle B
125 1023 Frank Murphy C
126 6092 Susan Doyle B
127 7032 Michael Mac A
So, I am looking for member 123 (as he has both A and B). I am also looking for member 127 (as he has just A).
But I am not looking for 124 and 126 (as they only have B).
It should return two rows for John Smith (as there are two qualifications) - that's not a problem, and one row for Michael Mac.
Any clearer?
[Updated on: Mon, 04 September 2006 05:16] Report message to a moderator |
|
|
|
| Re: Do I need a join? [message #191059 is a reply to message #191056] |
Mon, 04 September 2006 05:31 |
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
|
Senior Member |
|
|
OK, when I said "supply create table scripts and insert scripts" what I actually meant was
"supply create table scripts and insert scripts"
for example:
SQL> create table staff (Member_No number
, Mem_Seq number
, Mem_Firstnam varchar2(30)
, mem_last_name varchar2(30)
, qualification varchar2(1));
insert into staff values (123, 9723, 'John', 'Smith', 'A');
insert into staff values (123, 9223, 'John', 'Smith', 'B');
insert into staff values (124, 9223, 'Mary', 'Smith', 'B');
insert into staff values (125, 9223, 'Frank', 'Smith', 'C');
insert into staff values (126, 9223, 'Bungle', 'Smith', 'B');
insert into staff values (127, 9223, 'Zippy', 'Smith', 'A');
insert into staff values (125, 9223, 'Frank', 'Smith', 'B');
It saves me from having to do it myself. and you'll find that normally, it will get you a much much quicker response.
anyway, try
select * from staff
where member_no in (select member_no from staff
where qualification='A');
MEMBER_NO MEM_SEQ MEM_FIRS MEM_LAST Q
---------- ---------- -------- -------- -
123 9723 John Smith A
123 9223 John Smith B
127 9223 Zippy Smith A
Basically, all you are intersted in is returning all rows for any member has 'A' as a qualification. So all you need to to is get a list of all the people who have an 'A' and use that as the operand of the where clause (the subquery does that)
Jim
|
|
|
|
| Re: Do I need a join? [message #191060 is a reply to message #191059] |
Mon, 04 September 2006 05:37 |
Tom Dunne
Messages: 13
Registered: August 2006
Location: Dublin, Ireland
|
Junior Member |
|
|
| JSI2001 wrote on Mon, 04 September 2006 11:31 |
OK, when I said "supply create table scripts and insert scripts" what I actually meant was
"supply create table scripts and insert scripts"
|
I apologise, noted for future.
|
|
|
|
|
|
|
|
|
|
| Re: Do I need a join? [message #191066 is a reply to message #191035] |
Mon, 04 September 2006 05:56 |
Tom Dunne
Messages: 13
Registered: August 2006
Location: Dublin, Ireland
|
Junior Member |
|
|
Yeah, got it working, thanks to both of you.
In hindsight, I think I was completely over-complicating this scenario. Oh well, that's why I am in the newbies section.
|
|
|
|
Goto Forum:
Current Time: Thu Dec 05 19:29:48 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
NP