Remove record from query - 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)

Remove record from query

Author

Topic

philippes
Starting Member

3 Posts

Posted - 2012-09-20 : 12:47:01

Hello,

the following query:

SELECT SM.Cl_no, STC.stc_limit_in, SM.eq_no, SM.SM_code
FROM [CODE].[dbo].[Service_Multimedia] AS SM
JOIN [CODE].[dbo].[service_traffic_control] AS STC ON SM.SM_code = STC.se_code
WHERE SM.Cl_no ='B00627804'
AND SM.SM_Date_Fin_Fact IS NULL

gives me the following result:

Cl_no stc_limit_in eq_no SM_code
B00627804 107374182400 469605.000 BXL_IJ
B00627804 161061273600 0.000 BXL_II
B00627804 10737418240 675024.000 BXL_I0

How is it possbible to remove the record with the SM_code BXL_IJ if the SM_code BXL_II is present ?

Regards
Philippe

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-20 : 13:30:29

Add another condition to your WHERE clause as shown below:

...
       AND NOT EXISTS
       (
       		SELECT * FROM [CODE].[dbo].[Service_Multimedia] b
       		WHERE b.Cl_no = SM.Cl_no
       		AND b.SM_code = 'BXL_II'
       		AND SM.SM_Code = 'BXL_IJ'
       )

philippes
Starting Member

3 Posts

Posted - 2012-09-21 : 02:27:14

Thanks sunitabeck,

but the problem is that there is no order in the listing, so that sometimes the SM_code BXL_IJ is the first listed and sometimes it is the BXL_II

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 07:13:26

The query I posted yesterday should not depend on ordering. It is looking to see if there is a record with SM_code = 'BXL_II' to decide whether to include or exclude records with SM_code = 'BXL_IJ'.

If that is not what you are looking for, can you post some sample input and corresponding output that you are looking for that is representative .

philippes
Starting Member

3 Posts

Posted - 2012-09-21 : 08:54:12

Hi,

to be more specific, here's the whole query

SELECT
EQ.Cl_no,
sum(STC.stc_limit_in) as totalLimit,
sum(UMM.UMM_Total_In) as totalGB,
CL.Cl_client,
CL.Cl_langue
FROM [CODE].[dbo].[Equipement] AS EQ
JOIN [CODE].[dbo].[Service_Multimedia] AS SM ON EQ.Eq_no = SM.eq_no
JOIN [CODE].[dbo].[service_traffic_control] AS STC ON SM.SM_code=STC.se_code
JOIN [CODE].[dbo].[util_modem_mois] AS UMM ON UMM.eq_no=EQ.Eq_no
JOIN [CODE].[dbo].[Client] AS CL ON EQ.Cl_no=CL.Cl_no
WHERE SM.Cl_no NOT IN (
SELECT DISTINCT(CL.Cl_no)
FROM [CODE].[dbo].[Service_Multimedia]
WHERE SM.SM_code='LUX_IC'
OR SM.SM_code='LUX_ID'
OR SM.SM_code='BXL_IV'
OR SM.SM_code='BXL_IC'
OR SM.SM_code='BXL_IF')
AND UMM.UMM_Periode ='201208'
AND EQ.eq_inactif IS NULL
AND SM.SM_Date_Fin_Fact IS NULL
AND NOT EXISTS
(
SELECT * FROM [CODE].[dbo].[Service_Multimedia] b
WHERE b.Cl_no = SM.Cl_no
AND b.SM_code = 'BXL_II'
AND SM.SM_Code = 'BXL_IJ'
)
GROUP BY EQ.Cl_no,CL.Cl_client,CL.Cl_langue
ORDER BY EQ.Cl_no ASC

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-21 : 09:13:36

I am unable to follow the logic and requirements without some sample data that can be used to run against the query, but based on your original posting - that you want to exclude certain rows, may be this?

SELECT * FROM
(
	SELECT EQ.Cl_no,
		   SUM(STC.stc_limit_in)       AS totalLimit,
		   SUM(UMM.UMM_Total_In)       AS totalGB,
		   CL.Cl_client,
		   CL.Cl_langue
	FROM   [CODE].[dbo].[Equipement]   AS EQ
		   JOIN [CODE].[dbo].[Service_Multimedia] AS SM
				ON  EQ.Eq_no = SM.eq_no
		   JOIN [CODE].[dbo].[service_traffic_control] AS STC
				ON  SM.SM_code = STC.se_code
		   JOIN [CODE].[dbo].[util_modem_mois] AS UMM
				ON  UMM.eq_no = EQ.Eq_no
		   JOIN [CODE].[dbo].[Client]  AS CL
				ON  EQ.Cl_no = CL.Cl_no
	WHERE  SM.Cl_no NOT IN (SELECT DISTINCT(CL.Cl_no)
							FROM   [CODE].[dbo].[Service_Multimedia]
							WHERE  SM.SM_code = 'LUX_IC'
								   OR  SM.SM_code = 'LUX_ID'
								   OR  SM.SM_code = 'BXL_IV'
								   OR  SM.SM_code = 'BXL_IC'
								   OR  SM.SM_code = 'BXL_IF')
		   AND UMM.UMM_Periode = '201208'
		   AND EQ.eq_inactif IS NULL
		   AND SM.SM_Date_Fin_Fact IS NULL
	GROUP BY
		   EQ.Cl_no,
		   CL.Cl_client,
		   CL.Cl_langue
) SM
WHERE NOT EXISTS
       (
           SELECT *
           FROM   [CODE].[dbo].[Service_Multimedia] b
           WHERE  b.Cl_no = SM.Cl_no
                  AND b.SM_code = 'BXL_II'
                  AND SM.SM_Code = 'BXL_IJ'
       )
ORDER BY
       SM.Cl_no ASC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources