3 quick queries

[chudson007]

I want help with a couple of SQL queries.
I have two Tables Table A and Table B.
Both tables have the same two fields Name and Hobbies.
One Name can appear beside multiple hobbies in each table.

There are three queries I wish to run.
1) Find all the Hobbie and Name combinations in Table B not in Table A
only for Names that exist in Table A
2) Find all Hobbies and Name combinations in Table A not in Table B
3) Return all data in Table B that contains a Name that exists in Table
A

Regards,
Ciarán

[David Portas]

Looks like homework to me. Have you looked up some example queries and
tried to adapt them to your application? What have you tried so far?

--
David Portas
SQL Server MVP
--

[chudson007]

No its not homework, what I need to do is slightly different, but
thought if I got the answers to these three I'd be able to figure it
out.
I've been playing with inner and outer joins, but can't figure out what
I need to do.

Regards,
Ciarán

BTW what does MVP stand for?

[Hugo Kornelis]

On 4 Apr 2005 08:48:30 -0700, ch********@hotmail.com wrote:

I want help with a couple of SQL queries.
I have two Tables Table A and Table B.
Both tables have the same two fields Name and Hobbies.
One Name can appear beside multiple hobbies in each table.

There are three queries I wish to run.
1) Find all the Hobbie and Name combinations in Table B not in Table A
only for Names that exist in Table A
2) Find all Hobbies and Name combinations in Table A not in Table B
3) Return all data in Table B that contains a Name that exists in Table
A

Regards,
Ciarán

Hi Ciarán,

I guess the real question is why you store the same data in two tables.

Try if the following work. If not, then post table structure and sample
data as described here: www.aspfaq.com/5006.
1)
SELECT b.Hobbie, b.Name
FROM TableB AS b
WHERE EXISTS (SELECT *
FROM TableA AS a
WHERE a.Name = b.Name)

2)
SELECT a.Hobbie, a.Name
FROM TableA AS a
WHERE NOT EXISTS (SELECT *
FROM TableB AS b
WHERE b.Name = a.Name
AND b.Hobbie = a.Hobbie)

3) Same as 1.

Notes:
* 1 and 3 can also be done with an inner join, but you need some way to
prevent duplicates - either DISTINCT or a derived table
* 2 can also be done with an outer join.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[David Portas]

1 and 2:

SELECT A.name, A.hobby, B.name, B.hobby
FROM TableA AS A
FULL JOIN TableB AS B
ON A.name = B.name
AND A.hobby = B.hobby
WHERE A.name IS NULL
OR B.name IS NULL

3.
SELECT DISTINCT B.name, B.hobby
FROM TableA AS A
JOIN TableB AS B
ON A.name = B.name

Info on the Most Valuable Professional programme:
http://mvp.support.microsoft.com/

--
David Portas
SQL Server MVP
--

[chudson007]

I've tried those queries, but cannot get what I want.
Below are tables like the Tables I am using.

For Query1 I want to return
Name Hobby
Phil Boxing
Andy Boxing
Ciaran Boxing
For Query2 I want to return
Name Hobby
Phil Athletics
Andy Rugby
Ciaran Football

For Query3 I want to return
Name Hobby
Phil Football
Phil Rugby
Phil Athletics
Andy Football
Andy Rugby
Andy Athletics
Ciaran Football
Ciaran Rugby
Ciaran Athletics

TableA

Name Hobby
Phil Football
Phil Rugby
Andy Athletics
Andy Football
Ciaran Rugby
Ciaran Athletics
Phil Boxing
Andy Boxing
Ciaran Boxing
TableB
Name Hobby
Phil Football
Phil Rugby
Phil Athletics
Andy Football
Andy Rugby
Andy Athletics
Ciaran Football
Ciaran Rugby
Ciaran Athletics
Mark Football
Mark Rugby
Mark Athletics
Regards,
Ciarán

[chudson007]

I've tried those queries, but cannot get what I want.
Below are tables like the Tables I am using.
For Query1 I want to return
Name Hobby
Phil Boxing
Andy Boxing
Ciaran Boxing
For Query2 I want to return
Name Hobby
Phil Athletics
Andy Rugby
Ciaran Football
For Query3 I want to return
Name Hobby
Phil Football
Phil Rugby
Phil Athletics
Andy Football
Andy Rugby
Andy Athletics
Ciaran Football
Ciaran Rugby
Ciaran Athletics
TableA
Name Hobby
Phil Football
Phil Rugby
Andy Athletics
Andy Football
Ciaran Rugby
Ciaran Athletics
Phil Boxing
Andy Boxing
Ciaran Boxing
TableB
Name Hobby
Phil Football
Phil Rugby
Phil Athletics
Andy Football
Andy Rugby
Andy Athletics
Ciaran Football
Ciaran Rugby
Ciaran Athletics
Mark Football
Mark Rugby
Mark Athletics
Regards,
Ciarán

[David Portas]

I think you swapped the first two around from your original but thanks
for the examples. Try:

SELECT B.name, B.hobby
FROM TableB AS B
LEFT JOIN TableA AS A
ON A.name = B.name
AND A.hobby = B.hobby
WHERE A.hobby IS NULL
AND EXISTS
(SELECT *
FROM TableA
WHERE name = B.name)

SELECT A.name, A.hobby
FROM TableA AS A
LEFT JOIN TableB AS B
ON A.name = B.name
AND A.hobby = B.hobby
WHERE B.hobby IS NULL

My third query seems to produce the result you asked for (in a
different order maybe but you just need to add an ORDER BY clause if a
specific order is important). Try it again and let me know. Here's your
sample in code so that you can easily reproduce it. It's always best to
post your table structures and sample data as code so that others can
understand what your tables look like and test out possible solutions:

CREATE TABLE TableA (name VARCHAR(20) NOT NULL, hobby VARCHAR(20) NOT
NULL, PRIMARY KEY (name,hobby))

CREATE TABLE TableB (name VARCHAR(20) NOT NULL, hobby VARCHAR(20) NOT
NULL, PRIMARY KEY (name,hobby))

INSERT INTO TableA (name, hobby)
SELECT 'Phil', 'Football' UNION ALL
SELECT 'Phil', 'Rugby' UNION ALL
SELECT 'Andy', 'Athletics' UNION ALL
SELECT 'Andy', 'Football' UNION ALL
SELECT 'Ciaran', 'Rugby' UNION ALL
SELECT 'Ciaran', 'Athletics' UNION ALL
SELECT 'Phil', 'Boxing' UNION ALL
SELECT 'Andy', 'Boxing' UNION ALL
SELECT 'Ciaran', 'Boxing'

INSERT INTO TableB (name, hobby)
SELECT 'Phil', 'Football' UNION ALL
SELECT 'Phil', 'Rugby' UNION ALL
SELECT 'Phil', 'Athletics' UNION ALL
SELECT 'Andy', 'Football' UNION ALL
SELECT 'Andy', 'Rugby' UNION ALL
SELECT 'Andy', 'Athletics' UNION ALL
SELECT 'Ciaran', 'Football' UNION ALL
SELECT 'Ciaran', 'Rugby' UNION ALL
SELECT 'Ciaran', 'Athletics' UNION ALL
SELECT 'Mark', 'Football' UNION ALL
SELECT 'Mark', 'Rugby' UNION ALL
SELECT 'Mark', 'Athletics'

Hope this helps.

--
David Portas
SQL Server MVP
--

[chudson007]

Absolutely perfect.
Very much appreciated.
Thanks,
Ciarán