i need help with an specific select

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 2008 Forums

Transact-SQL (2008)

i need help with an specific select

Author

Topic

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-08-30 : 18:28:29

hi there

one more time i need some help

i have 2 tables and i need to compare both of them

first table called "today_cars"

CREATE TABLE [dbo].[today_cars](
[make] [varchar](50) NOT NULL,
[line] [varchar](50) NOT NULL
) ON [PRIMARY]

put some records on it

INSERT INTO [Pruebas].[dbo].[today_cars]
([make]
,[line])
select 'audi', 'r8'
union
select 'audi', 'Q7'

and the other table called "cars_washed"

CREATE TABLE [dbo].[cars_washed](
[make] [varchar](50) NULL,
[line] [varchar](50) NULL,
[currently_state] [varchar](50) NULL
) ON [PRIMARY]

puts some records on it

INSERT INTO [Pruebas].[dbo].[cars_washed]
([make]
,[line]
,[currently_state])
select 'audi', 'r8','washed'
union
select 'audi', 'Q7', 'not washed'

GO

now i want is
a conditional to let me know if all the cars on my table "today_cars" where make='audi' are already washed in my table "cars_washed"

if (all my cars from "today_cars" where make='audi' are already washed in my table "cars_washed" where make='audi' )

begin

"all cars wahsed"
end
else
begin
"some cars are not already washed#
end

many thanks in advanced for your help

kind regards

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-30 : 19:46:08

What you are describing can be done in a query like shown below - but I suspect there is more to what you are describing. For example, is there a one-to-one correspondence between rows in the two tables? Or, can there be some cars in today_cars table that are not in the cars_washed table? Or vice versa? Or multiple rows in the cars_washed table?

IF EXISTS
(
	SELECT * 
	FROM [Pruebas].[dbo].[today_cars] t
	INNER JOIN [Pruebas].[dbo].[cars_washed] w
		ON w.make = t.make AND w.line = t.line
	WHERE
		currently_state <> 'washed'
		AND t.make = 'audi'
)
SELECT 'Some cars are not washed'
ELSE SELECT 'All cars washed';

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-08-30 : 20:15:55

thanks sunitabeck for your reply

but you right it can there be some cars in today_cars table that are not in the cars_washed table,,

could you help me to solve the problem please

many thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 21:31:17

IF EXISTS(SELECT 1
FROM [Pruebas].[dbo].[today_cars] t
WHERE NOT EXISTS (SELECT 1 FROM [dbo].[cars_washed] WHERE [make] = t.[make] AND [line] = t.[line] AND [currently_state] <> 'washed')
)
SELECT 'Some cars are not washed'
ELSE 'All cars are washed'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-30 : 21:36:18

These are some possible queries that you could experiment with:

--- Lists all unwashed cars

SELECT
	make,
	line
FROM
	[Pruebas].[dbo].[today_cars] t
WHERE
NOT EXISTS
(
	SELECT * FROM [Pruebas].[dbo].[cars_washed] w
	WHERE 
		w.make = t.make 
		AND w.line = t.line
		AND w.currently_state = 'washed'
)


--- prints out a message if there are unwashed cars, including
--- those that may not be in the cars_washed table
IF EXISTS 
(
	SELECT
		*
	FROM
		[Pruebas].[dbo].[today_cars] t
	WHERE
	NOT EXISTS
	(
		SELECT * FROM [Pruebas].[dbo].[cars_washed] w
		WHERE 
			w.make = t.make 
			AND w.line = t.line
			AND w.currently_state = 'washed'
	)
)
SELECT 'Some cars are not washed'
ELSE SELECT 'All cars washed';

-- this just lists all the cars, including those that are not in the cars_washed table indicating their status.  You could use this as a subquery and take the counts to see how many of each kind.
SELECT
	t.make,
	t.line,
	COALESCE(w.currently_state,'Not in cars_washed table') AS current_state
FROM
	[Pruebas].[dbo].[today_cars] t
	LEFT JOIN [Pruebas].[dbo].[cars_washed] w ON
		w.make = t.make 
		AND w.line = t.line

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-08-30 : 22:47:05

what a great code!!!

many many thanks a lot sunitabeck and visakh16

thanks for helping the newbie people, many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 10:17:28

you're wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources