Ron, the first of your four queries work fine with the data you provided, but the fourth only names one table, "item", in the FROM clause, but there are two aliases. I could assume the "p" alias means item_process, but then there is no column called "pagename". Did you mean p.item_page? Also, a good way to post your tables and data are like below. Often, questions go unanswered because they require work on the part of the people trying to help. But it is Friday, so what the heck?

--Create the tables

IF OBJECT_ID('TempDB..#item','u') IS NOT NULL

DROP TABLE #item

GO

CREATE TABLE #item

(

item_id INT,

item_name VARCHAR(30)

PRIMARY KEY(item_id)

)

IF OBJECT_ID('TempDB..#item_process','u') IS NOT NULL

DROP TABLE #item_process

GO

CREATE TABLE #item_process

(

item_id INT,

item_page VARCHAR(20),

process1status INT,

process2status INT

)

IF OBJECT_ID('TempDB..#item_trans','u') IS NOT NULL

DROP TABLE #item_trans

GO

CREATE TABLE #item_trans

(

item_id INT,

item_page VARCHAR(20),

transstatus INT

)

--Populate them

INSERT INTO #item

SELECT 12,'My_book' UNION ALL

SELECT 15,'Another_book'

INSERT INTO #item_process

SELECT 12,'10_001',0,2 UNION ALL

SELECT 12,'10_002',2,0

INSERT INTO #item_trans

SELECT 12,'10_001',0 UNION ALL

SELECT 12,'10_002',1 UNION ALL

SELECT 12,'10_003',2