SQL union and not in clause

Question

2.00/5 (1 vote)

See more:

hi,

I have table

SQL

CREATE TABLE supportContacts 
	(
     id int auto_increment primary key, 
     type varchar(20), 
     details varchar(30),
      code_id int
    );

INSERT INTO supportContacts
(type, details,code_id)
VALUES
('Email0', '0admin@sqlfiddle.com',1033),
('Email1', '1admin@sqlfiddle.com',1031),
('Email2', '2admin@sqlfiddle.com',1033),
('Email3', '3admin@sqlfiddle.com',1031),
('Email5', '5admin@sqlfiddle.com',1032),
('Email6', '6admin@sqlfiddle.com',1032),
('Twitter4', '4@sqlfiddle',1033);

when i ran this query

SQL

Select type from supportContacts where code_id=1033
union 
(
  Select type from supportContacts where code_id
  not in (Select type from supportContacts where code_id=1033)
)

output is
TYPE
Email0
Email2
Twitter4
Email1
Email3
Email5
Email6

I want to replace this query with join's how can i do that.

Thanks

Posted 3-Dec-13 7:27am

Member 8128040

Updated 3-Dec-13 7:44am

pradiprenushe

v2

Add a Solution

Comments

ZurdoDev 3-Dec-13 13:51pm

It's the same table. Why would you want to join?

Member 8128040 3-Dec-13 14:06pm

actaully ryan this is the sample its a subquery which is used inside a big query which i have to improve for performance.

ZurdoDev 3-Dec-13 14:08pm

OK. But explain in English what you are trying to do and then we can help.

RaisKazi 3-Dec-13 13:52pm

I doubt your sub-query is working.

Member 8128040 3-Dec-13 14:09pm

may be there's some problem at ur sql engine...
its a simple wat's wrong in this...

[no name] 3-Dec-13 13:55pm

Try first to use everywehere aliases and qualified names. From my expierience a lot of sql parsers have problems using the same tablename in different contexts.

Select a.type from supportContacts a where a.code_id=1033
union
(
Select b.type from supportContacts b where b.code_id
not in (Select c.type from supportContacts c where c.code_id=1033)
)

This only to check your problem. Anyway your select statement does not make a lot of sense,

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 8128040 · Accepted Answer · 2013-12-03T08:00:00

Solution 1

i got d answer without any join
finally what i want data which comes as per d order of "query" before union clause
so i just use order by and when

Select type from supportContacts
order by
case when code_id=1033 then 1 end desc

please suggest is i m wrong....

Posted 3-Dec-13 8:00am

Member 8128040

Updated 3-Dec-13 8:03am

v2

Comments

CHill60 3-Dec-13 16:22pm

Not sure if this is meant to be a solution or a further question as I'm struggling to understand your words. If this is meant as an addition to your question then use the Improve Question link then delete this solution - by posting a solution you have made your question drop out of the list of unanswered questions, therefore fewer people will see it.
A word of advice ... "d" is very hard for non-English speakers to understand ... use "the"