Can any one help, sql statement bel

[Guest_imported]

Can any one help, sql statement below produces an error

SELECT customer_id, customer_name, reg_no
FROM customer c1
WHERE EXISTS (SELECT * FROM customer c2 WHERE customer_id <> c2.customer_id AND customer_name = c2.customer_name AND customer.reg_no = c2.reg_no;

Cheers

Chas

 

[tlbroadbent]

You need a closing parenthesis.

SELECT customer_id, customer_name, reg_no
FROM customer c1
WHERE EXISTS (SELECT * FROM customer c2 WHERE customer_id <> c2.customer_id AND customer_name = c2.customer_name AND customer.reg_no = c2.reg_no);
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[Guest_imported]

Sorry i missed out the closing parenthesis. Still getting an error

 

[bperry]

Perhaps try this:

[tt]
SELECT customer_id, customer_name, reg_no
FROM customer c1
WHERE EXISTS
(SELECT * FROM customer c2
WHERE c2.customer_id <> c1.customer_id
AND c2.customer_name = c1.customer_name
AND c2customer.reg_no = c1.reg_no);
[/tt]

 

[bperry]

Ooooops, I made yet another typo in my previous response. Sorry about that.

SELECT customer_id, customer_name, reg_no
FROM customer c1
WHERE EXISTS
(SELECT * FROM customer c2
WHERE c2.customer_id <> c1.customer_id
AND c2.customer_name = c1.customer_name
AND c2.customer.reg_no = c1.reg_no);

 

[tlbroadbent]

&quot;Still getting an error&quot; isn't very helpful. I recommend that you tell us the exact wording of the error and the database system you use. I also recommend joining Tek-Teips rather than posting as a vistor.

It appears that you have not used the aliases consistenrtly through the query. For example; Custome.reg_no in teh sub-query shoul be c2.reg_no to match c1.reg_no.

SELECT customer_id, customer_name, reg_no
FROM customer c1
WHERE EXISTS
(SELECT * FROM customer c2
WHERE c2.customer_id <> c1.customer_id
AND c2.customer_name = c1.customer_name
AND c2.reg_no = c1.reg_no);
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[bperry]

hecktic:
tbroadbent's suggestion is an sql query. But it is intended for Microsoft SQL Server, and might fail in another rdbms (esp. because of the CASE construct.)

 

[bperry]

please ignore my last post. I'm in the wrong forum!!