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 Go to next message
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 #191041 is a reply to message #191035] Mon, 04 September 2006 04:44 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

First of all what is the need of joining the tables here??

and whats the kind of output you are looking for if you have the data like this??

Col1 Col2
A data
B data




Thks & Rgds
venkat.
Re: Do I need a join? [message #191044 is a reply to message #191035] Mon, 04 September 2006 04:46 Go to previous messageGo to next message
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 #191049 is a reply to message #191044] Mon, 04 September 2006 04:57 Go to previous messageGo to next message
Tom Dunne
Messages: 13
Registered: August 2006
Location: Dublin, Ireland
Junior Member
Edit: oops double post. Monday is just not going well for me Embarassed

[Updated on: Mon, 04 September 2006 05:00]

Report message to a moderator

Re: Do I need a join? [message #191050 is a reply to message #191044] Mon, 04 September 2006 04:59 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #191061 is a reply to message #191060] Mon, 04 September 2006 05:42 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Smile NP
Re: Do I need a join? [message #191064 is a reply to message #191056] Mon, 04 September 2006 05:51 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

then try this with the scripts I gave you in the previous mail


SELECT a.id, a.qualification
FROM members a, (SELECT id FROM members WHERE qualification = 'A' ) b
WHERE a.id = b.id


Thks & Rgds
Venkat.
icon6.gif  Re: Do I need a join? [message #191065 is a reply to message #191035] Mon, 04 September 2006 05:54 Go to previous messageGo to next message
venkatbollu
Messages: 53
Registered: April 2005
Location: Bangalore
Member

oopssssss....

u already got the query
Re: Do I need a join? [message #191066 is a reply to message #191035] Mon, 04 September 2006 05:56 Go to previous message
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.
Previous Topic: Create a function
Next Topic: connect by prior?
Goto Forum:
  


Current Time: Thu Dec 08 18:32:43 CST 2016

Total time taken to generate the page: 0.09224 seconds