• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

SQL help

LEGEND ,
Jul 15, 2006 Jul 15, 2006

Copy link to clipboard

Copied

I need some help with a SQL statement.

I have 2 tables in a database. One is 'team' and the other is 'users'. The
share the common key 'username'

What I would like is to query both tables using a join and then output only
the username that do not appear in both lists.

For example:

Team: Users:
Bob Bob
Tammy Tammy
Joe Joe
Mike

The results would only be Mike's information.

Please help!

--
Wally Kolcz
Developer / Support


TOPICS
Advanced techniques

Views

298

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advisor ,
Jul 15, 2006 Jul 15, 2006

Copy link to clipboard

Copied

You did not specify which DB you are using and that is very important for this kind of query.

SQL like this will work for quality or STANDARDS COMPLIANT DB's:

SELECT
U.UserName,
T.UserName
FROM
USERS U
FULL OUTER JOIN
TEAM T ON U.UserName = T.UserName
WHERE
U.UserName IS NULL
OR
T.UserName IS NULL

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jul 15, 2006 Jul 15, 2006

Copy link to clipboard

Copied

If your data base supports a full outer join you might get by with one statement something like:

select nvl(a.username,b.username) from team a full outer join users b on a.username = b.username
where a.username is null or b.username is null

Otherwise you will need to do something like one of the following:

(select username from team
minus
select username from users)
union
(select username from users
minus
select username from team)


select a.username from team a left outer join users b on a.username = b.username
where b.username is null
union
select b.username from users b left outer join team a on b.username = a.username
where a.username is null

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jul 15, 2006 Jul 15, 2006

Copy link to clipboard

Copied

I am using a MySQL 5 database, if that helps.


Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jul 15, 2006 Jul 15, 2006

Copy link to clipboard

Copied

Thanks you.

I don't know if it matters, but I just want the list from the 'users' table
and never from the 'team' table.

It is to allow team members that are not assigned to a project to be added
at a different time.


Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jul 15, 2006 Jul 15, 2006

Copy link to clipboard

Copied

After reading the MySQL site, will this work to only show the results in
table 1 that do not exist in table 2?

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE
table2.id IS NULL


Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advisor ,
Jul 15, 2006 Jul 15, 2006

Copy link to clipboard

Copied

LATEST
Yes, it should.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation