create view

I have 2 tables:

table1

batch ID

a 1
a 2
a 3
a 4
a 5
b 6
b y
b h

table2

batch ID PF

a 3 f
a 4 p
b y p

I'd like to create a view that if there's a 'f' in the PF field in table2 then the new field in the view (pf) for the whole batch is 'f'.

view:

batch ID PF

a 1 f
a 2 f
a 3 f
a 4 f
a 5 f
b 6 p
b y p
b h p

How can I do this in SQL Server?

Thanks in advance.

The example below will produce the results you have specified.

You didn't specify what should be included in the output if the 'PF' value is not 'f' - I have taken a guess at it being 'p'. You may have to tweak this in accordance with your requirements.

Chris

/*

DROP TABLE dbo.Table1

DROP TABLE dbo.Table2

CREATE TABLE dbo.Table1

(

Batch CHAR(1),

ID CHAR(1)

)

GO

CREATE TABLE dbo.Table2

(

Batch CHAR(1),

ID CHAR(1),

PF CHAR(1)

)

GO

INSERT INTO dbo.Table1(Batch, ID)

SELECT 'a', '1' UNION

SELECT 'a', '2' UNION

SELECT 'a', '3' UNION

SELECT 'a', '4' UNION

SELECT 'a', '5' UNION

SELECT 'b', '6' UNION

SELECT 'b', 'y' UNION

SELECT 'b', 'h'

GO

INSERT INTO dbo.Table2(Batch, ID, PF)

SELECT 'a', '3', 'f' UNION

SELECT 'a', '4', 'p' UNION

SELECT 'b', 'y', 'p'

GO

*/

CREATE VIEW dbo.MyView

AS

SELECT t1.Batch,

t1.ID,

CASE WHEN EXISTS (SELECT 1 FROM dbo.Table2 t2 WHERE t2.Batch = t1.Batch AND t2.PF = 'f')

THEN 'f'

ELSE 'p'

END AS [PF]

FROM dbo.Table1 t1

GO

SELECT *

FROM dbo.MyView

GO

|||

You don’t indicate if table1 joins table2 on batch only or on batch and ID. You don’t indicate if the only two values for PF are ‘f’ and ‘p’. The firstsolution assumes that the join is only on batch and the only PF values are p and f.

You will have to give more information if this isn’t true. I’ll leave it to you to CREATE VIEW.

CREATE TABLE dbo.table1 (batch varchar(1),ID varchar(1))

CREATE TABLE dbo.table2 (batch varchar(1),ID varchar(1),PF varchar(1))

INSERT INTO dbo.table1(batch,ID)VALUES('a','1')

INSERT INTO dbo.table1(batch,ID)VALUES('a','2')

INSERT INTO dbo.table1(batch,ID)VALUES('a','3')

INSERT INTO dbo.table1(batch,ID)VALUES('a','4')

INSERT INTO dbo.table1(batch,ID)VALUES('a','5')

INSERT INTO dbo.table1(batch,ID)VALUES('b','6')

INSERT INTO dbo.table1(batch,ID)VALUES('b','y')

INSERT INTO dbo.table1(batch,ID)VALUES('b','h')

INSERT INTO dbo.table2(batch,ID,PF)VALUES('a','3','f')

INSERT INTO dbo.table2(batch,ID,PF)VALUES('a','4','p')

INSERT INTO dbo.table2(batch,ID,PF)VALUES('b','y','p')

SELECT t1.batch

,t1.ID

,CASE (SELECT 'f' FROM dbo.Table2 t2 WHERE PF = 'f' AND t2.batch = t1.batch)

WHEN 'f' THEN 'f'

ELSE 'p'

END AS PF

FROM dbo.table1 t1

batchIDPF

a1f

a2f

a3f

a4f

a5f

b6p

byp

bhp

However if you need to join on batch and ID and there can be more values than p and f:

INSERT INTO dbo.table1(batch,ID)VALUES('a','1')

INSERT INTO dbo.table1(batch,ID)VALUES('a','2')

INSERT INTO dbo.table1(batch,ID)VALUES('a','3')

INSERT INTO dbo.table1(batch,ID)VALUES('a','4')

INSERT INTO dbo.table1(batch,ID)VALUES('a','5')

INSERT INTO dbo.table1(batch,ID)VALUES('b','6')

INSERT INTO dbo.table1(batch,ID)VALUES('b','y')

INSERT INTO dbo.table1(batch,ID)VALUES('b','h')

INSERT INTO dbo.table2(batch,ID,PF)VALUES('a','1','f')

INSERT INTO dbo.table2(batch,ID,PF)VALUES('a','2','x')

INSERT INTO dbo.table2(batch,ID,PF)VALUES('a','3','p')

INSERT INTO dbo.table2(batch,ID,PF)VALUES('a','4','f')

INSERT INTO dbo.table2(batch,ID,PF)VALUES('a','5','p')

INSERT INTO dbo.table2(batch,ID,PF)VALUES('b','6','p')

INSERT INTO dbo.table2(batch,ID,PF)VALUES('b','y','p')

INSERT INTO dbo.table2(batch,ID,PF)VALUES('b','h','r')

SELECT batch,ID, 'f' AS PF

FROM dbo.table1 t1

WHERE EXISTS (SELECT PF FROM dbo.table2 t3 WHERE PF = 'f' AND t3.batch = t1.batch )

UNION

SELECT t1.batch, t1.ID, t2.PF

FROM dbo.table1 t1

INNER JOIN dbo.table2 t2

ON t1.batch = t2.batch

AND t1.id = t2.ID

WHERE NOT EXISTS (SELECT PF FROM dbo.table2 t3 WHERE PF = 'f' AND t3.batch = t2.batch )

batchIDPF

a1f

a2f

a3f

a4f

a5f

b6p

bhr

byp

|||

You can do one of below:

select t1.batch, t1.ID

, (select min(t2.PF) from table2 as t2 where t2.batch = t1.batch)

from table1 as t1

-- Or

select t1.batch, t1.ID

, (select top 1 t2.PF from table2 as t2 where t2.batch = t1.batch

order by case t2.PF when 'f' then 0 else 1 end)

from table1 as t1