Select Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select Statement

Author

Topic

mshsilver
Posting Yak Master

112 Posts

Posted - 2009-05-19 : 14:59:36

Hi

I'm having a mental block, talking it through might point out my stupidity.

The query below pulls 1273 results, what i need to do is get the results this query doesn't get which is 3 records. Those 3 records exist in the wce_ilr table. i want to insert them into the wce_contact table.

So i have to say where wce_ilr.edrs does not exist in wce_contact display the records. Could someone point me in the right direction.

Thanks alot.


SELECT     MAX(wce_ilr.EDRS) AS EDRS, MAX(wce_contact.COMPANY) AS COMPANY
FROM         wce_ilr INNER JOIN
                      wce_contact ON wce_ilr.EDRS = wce_contact.EDRS COLLATE database_default
WHERE     (NOT (wce_ilr.EDRS IS NULL))
GROUP BY wce_ilr.EDRS
ORDER BY EDRS

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-19 : 15:12:22

Maybe:

SELECT     
	MAX(wce_ilr.EDRS) AS EDRS, 
	MAX(wce_contact.COMPANY) AS COMPANY
FROM
	wce_ilr 
LEFT OUTER JOIN
    wce_contact 
    ON wce_ilr.EDRS = wce_contact.EDRS COLLATE database_default
WHERE 
	wce_contact.EDRS IS NULL
GROUP BY 
	wce_ilr.EDRS
ORDER BY 
	EDRS

mshsilver
Posting Yak Master

112 Posts

Posted - 2009-05-19 : 15:20:52

Great that did it. Thanks a lot.

However i have hit a bit of a problem :-) becuase there are rows with no edrs number (nulls) the result of the query brings back one row with a null edrs.

I need to insert the results into a table (wce_contact) using the edrs number as the uniqueid....

Is there anyway of telling the select query to not display the one null row? or maybe the insert statement to ignore it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 03:15:25

do you mean this?


SELECT     
	MAX(wce_ilr.EDRS) AS EDRS, 
	MAX(wce_contact.COMPANY) AS COMPANY
FROM
	wce_ilr 
LEFT OUTER JOIN
    wce_contact 
    ON wce_ilr.EDRS = wce_contact.EDRS COLLATE database_default
WHERE 
	wce_contact.EDRS IS NULL
AND wce_ilr.EDRS IS NOT NULL
GROUP BY 
	wce_ilr.EDRS
ORDER BY 
	EDRS

mshsilver
Posting Yak Master

112 Posts

Posted - 2009-05-22 : 04:43:14

That was it, thank you very much. I have so much still to learn :-) Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 04:47:14

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources