3 quick queries

[chudson007@hotmail.com]

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]

Re: 3 quick queries

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@hotmail.com]

Re: 3 quick queries

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]

Re: 3 quick queries

On 4 Apr 2005 08:48:30 -0700, chudson007@hotm ail.com wrote:
[color=blue]
>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[/color]

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]

Re: 3 quick queries

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@hotmail.com]

Re: 3 quick queries

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@hotmail.com]

Re: 3 quick queries

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]

Re: 3 quick queries

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@hotmail.com]

Re: 3 quick queries

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