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 2000 Forums
 Transact-SQL (2000)
 Query trouble... MAX and join problem?

Author  Topic 

julianfraser
Starting Member

19 Posts
Posted - 2005-10-27 : 15:16:55
I've been having trouble with a query that i need to update within a SPROC i have. Here is the original query that works...

SELECT rt.propertyID, MAX(photoID) AS 'photoID', title, property_type, address1, address2, town, county, postcode, price, bedrooms, bathrooms, file_extension
FROM #ResultsTable rt
LEFT JOIN LM001_property_photo pp ON pp.propertyID = rt.propertyID
WHERE (row_num >= @row_start) AND (row_num <= @row_end)
GROUP BY rt.propertyID, title, property_type, address1, address2, town, county, postcode, price, bedrooms, bathrooms, file_extension


When requesting the additional column "thumb_height" all the photos linked to each property are included in the result set and not just the top 1.


SELECT rt.propertyID, MAX(photoID) AS 'photoID', pp.thumb_height, title, property_type, sale_statusID, address1, address2, town, county, postcode, price, bedrooms, bathrooms, file_extension
FROM #ResultsTable rt
LEFT JOIN LM001_property_photo pp ON pp.propertyID = rt.propertyID
WHERE (row_num >= @row_start) AND (row_num <= @row_end)
GROUP BY rt.propertyID, pp.thumb_height, title, property_type, sale_statusID, address1, address2, town, county, postcode, price, bedrooms, bathrooms, file_extension


Please could someone provide me with the info i need to get this query to work properlt... I've been stuck on this for a while now.

Thanks,
Julian.

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2005-10-27 : 17:45:00
Can we assume that you only want the thumb_hight for the photoID that is the max(photoid)?

if so there are a lot of ways to go about it. without seeing the big picture its hard to know which will be most efficient but maybe something like this:

SELECT	rt.propertyID
	, MAX(photoID) AS 'photoID'
	, pp.thumb_height
	, title
	, property_type
	, sale_statusID
	, address1
	, address2
	, town
	, county
	, postcode
	, price
	, bedrooms
	, bathrooms
	, file_extension 
FROM #ResultsTable rt
LEFT JOIN (--this is a derived table with the thumb_height of for the max(photoid)
	select	p.thumb_height
		,p.propertyid
		--,<any other columns you want from LM001_property_photo>
	from	(
		select max(z.photoID) as photoID
			,z.propertyid
		from	LM001_property_photo z
		where	z.propertyid = rt.propertyid
		) mx
	join	LM001_property_photo p
			on p.propertyid = mx.propertyid
			and p.photoid = mx.photoid
	) pp
		ON pp.propertyID = rt.propertyID
WHERE (row_num >= @row_start) AND (row_num <= @row_end)
GROUP BY rt.propertyID
	, pp.thumb_height
	, title
	, property_type
	, sale_statusID
	, address1
	, address2
	, town
	, county
	, postcode
	, price
	, bedrooms
	, bathrooms
	, file_extension


If this doesn't work or isn't what you need, you should probable post the DDL/DML so we can get it right.

Be One with the Optimizer
TG
Go to Top of Page

julianfraser
Starting Member

19 Posts
Posted - 2005-11-03 : 13:15:32
Thanks for the input... here is the new query that is sitting in the SPROC, but now when running it I get the error message "The column prefix 'rt' does not match with a table name or alias name used in the query."

Is this something to do with the derived table trying to reference a parent table... I'm really having trouble getting my head round this subject... any articles i could benefit from reading?

Thanks again for the help,
Julian.


SELECT rt.propertyID, MAX(photoID) AS 'photoID', pp.thumb_height, title, property_type, sale_statusID, address1, address2, town, county, postcode, price, bedrooms, bathrooms, file_extension
FROM #ResultsTable rt
LEFT JOIN (SELECT p.thumb_height, p.propertyID
FROM (
SELECT MAX(z.photoID) AS 'photoID', z.propertyid
FROM LM001_property_photo z
WHERE z.propertyID = rt.propertyID
) mx
JOIN LM001_property_photo p ON p.propertyid = mx.propertyid AND p.photoid = mx.photoid
) pp ON pp.propertyID = rt.propertyID
GROUP BY rt.propertyID, pp.thumb_height, title, property_type, sale_statusID, address1, address2, town, county, postcode, price, bedrooms, bathrooms, file_extension
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts
Posted - 2005-11-03 : 13:48:18
quote:
If this doesn't work or isn't what you need, you should probably post the DDL/DML so we can get it right.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG
Go to Top of Page

julianfraser
Starting Member

19 Posts
Posted - 2005-11-03 : 14:36:20
What's DDL or DML?
Go to Top of Page

julianfraser
Starting Member

19 Posts
Posted - 2005-11-03 : 14:43:59
Here is the code for the two main tables and the sproc.

Thanks,
Julian.


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__LM001_con__prope__6B24EA82]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[LM001_contact_log] DROP CONSTRAINT FK__LM001_con__prope__6B24EA82
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__LM001_inv__prope__5535A963]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[LM001_invoice_item] DROP CONSTRAINT FK__LM001_inv__prope__5535A963
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__LM001_pro__prope__5EBF139D]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[LM001_property_listing] DROP CONSTRAINT FK__LM001_pro__prope__5EBF139D
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__LM001_pro__prope__412EB0B6]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[LM001_property_photo] DROP CONSTRAINT FK__LM001_pro__prope__412EB0B6
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__LM001_pro__prope__3D5E1FD2]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[LM001_property_property_feature] DROP CONSTRAINT FK__LM001_pro__prope__3D5E1FD2
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__LM001_pro__prope__44FF419A]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[LM001_property_shortlist] DROP CONSTRAINT FK__LM001_pro__prope__44FF419A
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__LM001_pro__prope__6383C8BA]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[LM001_property_views] DROP CONSTRAINT FK__LM001_pro__prope__6383C8BA
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[update_property]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[update_property]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LM001_property]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LM001_property]
GO

CREATE TABLE [dbo].[LM001_property] (
[propertyID] [int] IDENTITY (1, 1) NOT NULL ,
[userID] [int] NOT NULL ,
[title] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (6000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[map_x] [int] NULL ,
[map_y] [int] NULL ,
[address1] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address2] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[town] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[county] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[postcode] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[country] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[tel] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[fax] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[mobile] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[price] [money] NULL ,
[price_termID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[bedrooms] [int] NOT NULL ,
[bathrooms] [int] NOT NULL ,
[property_typeID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[property_termID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[listing_typeID] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[sale_statusID] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[last_update] [smalldatetime] NOT NULL ,
[active] [bit] NOT NULL ,
[deleted] [bit] NOT NULL ,
[new_property_notify] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LM001_property] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[propertyID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LM001_property] ADD
CONSTRAINT [DF__LM001_pro__bedro__2E1BDC42] DEFAULT (0) FOR [bedrooms],
CONSTRAINT [DF__LM001_pro__bathr__2F10007B] DEFAULT (0) FOR [bathrooms],
CONSTRAINT [DF__LM001_pro__last___33D4B598] DEFAULT (getdate()) FOR [last_update],
CONSTRAINT [DF__LM001_pro__activ__34C8D9D1] DEFAULT (1) FOR [active],
CONSTRAINT [DF__LM001_pro__delet__35BCFE0A] DEFAULT (0) FOR [deleted]
GO

ALTER TABLE [dbo].[LM001_property] ADD
FOREIGN KEY
(
[country]
) REFERENCES [dbo].[LM001_countryoption] (
[countrycode]
),
FOREIGN KEY
(
[listing_typeID]
) REFERENCES [dbo].[LM001_listing_type] (
[listing_typeID]
),
FOREIGN KEY
(
[price_termID]
) REFERENCES [dbo].[LM001_property_price_term] (
[price_termID]
),
FOREIGN KEY
(
[property_typeID]
) REFERENCES [dbo].[LM001_property_type] (
[property_typeID]
),
FOREIGN KEY
(
[property_termID]
) REFERENCES [dbo].[LM001_property_term] (
[property_termID]
),
FOREIGN KEY
(
[sale_statusID]
) REFERENCES [dbo].[LM001_sale_status] (
[sale_statusID]
),
FOREIGN KEY
(
[userID]
) REFERENCES [dbo].[LM001_user] (
[userID]
)
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



CREATE TRIGGER update_property
ON LM001_property
FOR UPDATE
AS
UPDATE LM001_property SET last_update = GETDATE() WHERE propertyID IN (SELECT propertyID FROM inserted)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LM001_property_photo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LM001_property_photo]
GO

CREATE TABLE [dbo].[LM001_property_photo] (
[photoID] [int] IDENTITY (1, 1) NOT NULL ,
[propertyID] [int] NOT NULL ,
[photo_description] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[file_extension] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[list_order] [int] NOT NULL ,
[verified] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LM001_property_photo] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[photoID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LM001_property_photo] ADD
CONSTRAINT [DF__LM001_pro__verif__4222D4EF] DEFAULT (0) FOR [verified]
GO

ALTER TABLE [dbo].[LM001_property_photo] ADD
FOREIGN KEY
(
[propertyID]
) REFERENCES [dbo].[LM001_property] (
[propertyID]
)
GO



CREATE PROC LM001_ADMIN_search_notify_property
@where_clause varchar(6000),
@total_rows int OUTPUT
AS
DECLARE @sql_string varchar(8000)

BEGIN TRANSACTION GetDataSet

SELECT @sql_string = 'SELECT p.propertyID, p.title, pt.property_type, p.sale_statusID, p.address1, p.address2, p.town, p.county, p.postcode, p.price, p.bedrooms, p.bathrooms ' +
'FROM LM001_property p ' +
'INNER JOIN LM001_property_type pt ON pt.property_typeID = p.property_typeID ' +
'WHERE ' + @where_clause + ' ' +
'AND p.active = 1 AND p.deleted = 0 AND p.new_property_notify IS NULL' +
'AND (EXISTS (SELECT 1 FROM LM001_property_listing pl WHERE pl.propertyID = p.propertyID AND pl.listing_end > GETDATE()) OR EXISTS (SELECT 1 FROM LM001_property_listing pl WHERE pl.propertyID = p.propertyID AND pl.listing_end IS NULL)) AND EXISTS (SELECT 1 FROM LM001_property_listing pl WHERE pl.propertyID = p.propertyID AND pl.listing_live = 1)'

CREATE TABLE #ResultsTable
(
row_num int IDENTITY(1,1) PRIMARY KEY,
propertyID int,
title varchar(300),
property_type varchar(36),
sale_statusID char(3),
address1 varchar(60),
address2 varchar(60),
town varchar(60),
county varchar(60),
postcode varchar(16),
price money,
bedrooms int,
bathrooms int
)
IF @@ERROR <> 0
GOTO ErrorHandler

INSERT INTO #ResultsTable
EXEC (@sql_string)
IF @@ERROR <> 0
GOTO ErrorHandler

SELECT @total_rows = SCOPE_IDENTITY()

SELECT rt.propertyID, MAX(photoID) AS 'photoID', pp.thumb_height, title, property_type, sale_statusID, address1, address2, town, county, postcode, price, bedrooms, bathrooms, file_extension
FROM #ResultsTable rt
LEFT JOIN (SELECT p.thumb_height, p.propertyID
FROM (
SELECT MAX(z.photoID) AS 'photoID', z.propertyid
FROM LM001_property_photo z
WHERE z.propertyID = rt.propertyID
) mx
JOIN LM001_property_photo p ON p.propertyid = mx.propertyid AND p.photoid = mx.photoid
) pp ON pp.propertyID = rt.propertyID
GROUP BY rt.propertyID, pp.thumb_height, title, property_type, sale_statusID, address1, address2, town, county, postcode, price, bedrooms, bathrooms, file_extension
IF @@ERROR <> 0
GOTO ErrorHandler

DROP TABLE #ResultsTable

COMMIT TRANSACTION GetDataSet
RETURN 1

ErrorHandler:
ROLLBACK TRANSACTION GetDataSet
RETURN
GO
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2005-11-08 : 12:25:13
where do you get row_num from?

Is this SQL Server 2005?

And if you read my hint link, please state what the requirement is, in business terms,

Sample Data would also help, as well as what the expected result should be



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

julianfraser
Starting Member

19 Posts
Posted - 2005-11-22 : 00:52:24
Here is the code needed to replicate the tables, sprocs and sample data for this section of the database. Just to refresh, what i need to do is select all the properties from the property table that meet the search criteria and join the 1 property photo from the property_photo table with the lowest list_order, this should explain what i am trying to do... please email me with any questions to jfraser@fwesh.com or aim screenname absinthbliss... i really need to get this finished asap!

Thanks, Julian.




USE master
GO

DROP DATABASE JF01_proptest
GO

CREATE DATABASE JF01_proptest
GO

USE JF01_proptest
GO


CREATE TABLE LM001_user
(
userID int IDENTITY NOT NULL PRIMARY KEY,
username varchar(20) NOT NULL
)

CREATE TABLE LM001_listing_type
(
listing_typeID char(1) NOT NULL PRIMARY KEY,
listing_type varchar(36) NOT NULL
)

CREATE TABLE LM001_property_term
(
property_termID char(3) NOT NULL PRIMARY KEY,
property_term varchar(36) NOT NULL,
listing_typeID char(1) NULL FOREIGN KEY REFERENCES LM001_listing_type(listing_typeID),
list_order int NOT NULL
)

CREATE TABLE LM001_property_type
(
property_typeID char(3) NOT NULL PRIMARY KEY,
property_type varchar(36) NOT NULL,
list_order int NOT NULL
)

CREATE TABLE LM001_sale_status
(
sale_statusID char(3) NOT NULL PRIMARY KEY,
sale_status varchar(36) NOT NULL,
listing_typeID char(1) NULL FOREIGN KEY REFERENCES LM001_listing_type(listing_typeID),
list_order int NOT NULL
)

CREATE TABLE LM001_property
(
propertyID int IDENTITY NOT NULL PRIMARY KEY,
userID int NOT NULL FOREIGN KEY REFERENCES LM001_user(userID),
title varchar(300) NOT NULL,
description varchar(6000) NULL,
map_x int NULL,
map_y int NULL,
address1 varchar(60) NOT NULL,
address2 varchar(60) NULL,
town varchar(60) NOT NULL,
county varchar(60) NULL,
postcode varchar(16) NOT NULL,
tel varchar(20) NOT NULL,
fax varchar(20) NULL,
mobile varchar(20) NULL,
price money NULL,
bedrooms int NOT NULL DEFAULT 0,
bathrooms int NOT NULL DEFAULT 0,
property_typeID char(3) NOT NULL FOREIGN KEY REFERENCES LM001_property_type(property_typeID),
property_termID char(3) NOT NULL FOREIGN KEY REFERENCES LM001_property_term(property_termID),
listing_typeID char(1) NOT NULL FOREIGN KEY REFERENCES LM001_listing_type(listing_typeID),
sale_statusID char(3) NOT NULL FOREIGN KEY REFERENCES LM001_sale_status(sale_statusID),
last_update smalldatetime NOT NULL DEFAULT GETDATE(),
active bit NOT NULL DEFAULT 1,
deleted bit NOT NULL DEFAULT 0,
new_property_notify datetime NULL
)

CREATE TABLE LM001_property_photo
(
photoID int IDENTITY NOT NULL PRIMARY KEY,
propertyID int NOT NULL FOREIGN KEY REFERENCES LM001_property(propertyID),
photo_description varchar(60) NULL,
file_extension varchar(5) NOT NULL,
list_order int NOT NULL,
verified bit NOT NULL DEFAULT 0
)

CREATE TABLE LM001_property_listing
(
listingID int IDENTITY NOT NULL PRIMARY KEY,
propertyID int NOT NULL FOREIGN KEY REFERENCES LM001_property(propertyID),
listing_start smalldatetime NOT NULL DEFAULT GETDATE(),
listing_end smalldatetime NULL,
listing_live bit NOT NULL DEFAULT 1
)


INSERT INTO LM001_user (username) VALUES ('test01')
INSERT INTO LM001_user (username) VALUES ('test02')
INSERT INTO LM001_user (username) VALUES ('test03')

INSERT INTO LM001_listing_type (listing_typeID, listing_type) VALUES ('S', 'For Sale')
INSERT INTO LM001_listing_type (listing_typeID, listing_type) VALUES ('L', 'To Let')

INSERT INTO LM001_property_type (property_typeID, property_type, list_order) VALUES ('HDE', 'Detached House', 1)
INSERT INTO LM001_property_type (property_typeID, property_type, list_order) VALUES ('HSD', 'Semi-Detached House', 2)
INSERT INTO LM001_property_type (property_typeID, property_type, list_order) VALUES ('HTE', 'Terraced House', 3)
INSERT INTO LM001_property_type (property_typeID, property_type, list_order) VALUES ('HBW', 'Bungalow', 4)
INSERT INTO LM001_property_type (property_typeID, property_type, list_order) VALUES ('FMS', 'Maisonette', 5)
INSERT INTO LM001_property_type (property_typeID, property_type, list_order) VALUES ('FFL', 'Flat', 6)
INSERT INTO LM001_property_type (property_typeID, property_type, list_order) VALUES ('OTH', 'Other', 7)

INSERT INTO LM001_property_term (property_termID, property_term, listing_typeID, list_order) VALUES ('FRE', 'Freehold', 'S', 1)
INSERT INTO LM001_property_term (property_termID, property_term, listing_typeID, list_order) VALUES ('FSH', 'Shared Freehold', 'S', 2)
INSERT INTO LM001_property_term (property_termID, property_term, listing_typeID, list_order) VALUES ('LEA', 'Leasehold', 'S', 3)
INSERT INTO LM001_property_term (property_termID, property_term, listing_typeID, list_order) VALUES ('OTH', 'Other', NULL, 20)
INSERT INTO LM001_property_term (property_termID, property_term, listing_typeID, list_order) VALUES ('SH6', '6 Month Shorthold Assured', 'L', 4)

INSERT INTO LM001_sale_status (sale_statusID, sale_status, listing_typeID, list_order) VALUES ('FSL', 'For Sale', 'S', 1)
INSERT INTO LM001_sale_status (sale_statusID, sale_status, listing_typeID, list_order) VALUES ('UOF', 'Under Offer', 'S', 2)
INSERT INTO LM001_sale_status (sale_statusID, sale_status, listing_typeID, list_order) VALUES ('SLD', 'Sold', 'S', 3)
INSERT INTO LM001_sale_status (sale_statusID, sale_status, listing_typeID, list_order) VALUES ('OTH', 'Other', NULL, 20)
INSERT INTO LM001_sale_status (sale_statusID, sale_status, listing_typeID, list_order) VALUES ('TOL', 'To Let', 'L', 1)
INSERT INTO LM001_sale_status (sale_statusID, sale_status, listing_typeID, list_order) VALUES ('LET', 'Let', 'L', 2)

INSERT INTO LM001_property (userID, title, description, map_x, map_y, address1, address2, town, county, postcode, tel, fax, mobile, price, bedrooms, bathrooms, property_typeID, property_termID, listing_typeID, sale_statusID)
VALUES (1, 'TEST_01', 'TEST_01 Decription', 000001, 000001, '120 23rd Street', 'Unit D', 'Costa Mesa', 'CA', 'TN1 2PA', '949 378 1711', NULL, '949 378 1711', 200001, 1, 1, 'HDE', 'FRE', 'S', 'FSL')

INSERT INTO LM001_property (userID, title, description, map_x, map_y, address1, address2, town, county, postcode, tel, fax, mobile, price, bedrooms, bathrooms, property_typeID, property_termID, listing_typeID, sale_statusID)
VALUES (2, 'TEST_02', 'TEST_02 Decription', 000001, 000001, '120 23rd Street', 'Unit D', 'Costa Mesa', 'CA', 'TN1 2PA', '949 378 1711', NULL, '949 378 1711', 200002, 1, 1, 'HDE', 'FRE', 'S', 'FSL')

INSERT INTO LM001_property (userID, title, description, map_x, map_y, address1, address2, town, county, postcode, tel, fax, mobile, price, bedrooms, bathrooms, property_typeID, property_termID, listing_typeID, sale_statusID)
VALUES (1, 'TEST_03', 'TEST_03 Decription', 000001, 000001, '120 23rd Street', 'Unit D', 'Costa Mesa', 'CA', 'TN1 2PA', '949 378 1711', NULL, '949 378 1711', 200003, 1, 1, 'HDE', 'FRE', 'S', 'FSL')

INSERT INTO LM001_property (userID, title, description, map_x, map_y, address1, address2, town, county, postcode, tel, fax, mobile, price, bedrooms, bathrooms, property_typeID, property_termID, listing_typeID, sale_statusID)
VALUES (3, 'TEST_04', 'TEST_04 Decription', 000001, 000001, '120 23rd Street', 'Unit D', 'Costa Mesa', 'CA', 'TN1 2PA', '949 378 1711', NULL, '949 378 1711', 200004, 1, 1, 'HDE', 'FRE', 'S', 'FSL')

INSERT INTO LM001_property_photo (propertyID, photo_description, file_extension, list_order, verified) VALUES (1, 'TEST_01.1', '.jpg', 1, 1)
INSERT INTO LM001_property_photo (propertyID, photo_description, file_extension, list_order, verified) VALUES (1, 'TEST_01.2', '.jpg', 1, 1)
INSERT INTO LM001_property_photo (propertyID, photo_description, file_extension, list_order, verified) VALUES (1, 'TEST_01.3', '.jpg', 1, 1)
INSERT INTO LM001_property_photo (propertyID, photo_description, file_extension, list_order, verified) VALUES (1, 'TEST_01.4', '.jpg', 1, 1)
INSERT INTO LM001_property_photo (propertyID, photo_description, file_extension, list_order, verified) VALUES (1, 'TEST_01.5', '.jpg', 1, 1)
INSERT INTO LM001_property_photo (propertyID, photo_description, file_extension, list_order, verified) VALUES (2, 'TEST_02.1', '.jpg', 1, 1)
INSERT INTO LM001_property_photo (propertyID, photo_description, file_extension, list_order, verified) VALUES (2, 'TEST_02.2', '.jpg', 1, 1)
INSERT INTO LM001_property_photo (propertyID, photo_description, file_extension, list_order, verified) VALUES (2, 'TEST_02.3', '.jpg', 1, 1)
INSERT INTO LM001_property_photo (propertyID, photo_description, file_extension, list_order, verified) VALUES (3, 'TEST_03.1', '.jpg', 1, 1)
INSERT INTO LM001_property_photo (propertyID, photo_description, file_extension, list_order, verified) VALUES (4, 'TEST_04.1', '.jpg', 1, 1)
INSERT INTO LM001_property_photo (propertyID, photo_description, file_extension, list_order, verified) VALUES (4, 'TEST_04.2', '.jpg', 1, 1)
INSERT INTO LM001_property_photo (propertyID, photo_description, file_extension, list_order, verified) VALUES (4, 'TEST_04.3', '.jpg', 1, 1)
INSERT INTO LM001_property_photo (propertyID, photo_description, file_extension, list_order, verified) VALUES (4, 'TEST_04.4', '.jpg', 1, 1)
INSERT INTO LM001_property_photo (propertyID, photo_description, file_extension, list_order, verified) VALUES (4, 'TEST_04.5', '.jpg', 1, 1)

INSERT INTO LM001_property_listing (propertyID) VALUES (1)
INSERT INTO LM001_property_listing (propertyID) VALUES (2)
INSERT INTO LM001_property_listing (propertyID) VALUES (3)
INSERT INTO LM001_property_listing (propertyID, listing_live) VALUES (4,0)

GO


CREATE PROC LM001_ADMIN_search_notify_property
@where_clause varchar(6000),
@total_rows int OUTPUT
AS
DECLARE @sql_string varchar(8000)

BEGIN TRANSACTION GetDataSet

SELECT @sql_string = 'SELECT p.propertyID, p.title, pt.property_type, p.sale_statusID, p.address1, p.address2, p.town, p.county, p.postcode, p.price, p.bedrooms, p.bathrooms ' +
'FROM LM001_property p ' +
'INNER JOIN LM001_property_type pt ON pt.property_typeID = p.property_typeID ' +
'WHERE ' + @where_clause + ' ' +
'AND p.active = 1 AND p.deleted = 0 AND p.new_property_notify IS NULL ' +
'AND (EXISTS (SELECT 1 FROM LM001_property_listing pl WHERE pl.propertyID = p.propertyID AND pl.listing_end > GETDATE()) OR EXISTS (SELECT 1 FROM LM001_property_listing pl WHERE pl.propertyID = p.propertyID AND pl.listing_end IS NULL)) AND EXISTS (SELECT 1 FROM LM001_property_listing pl WHERE pl.propertyID = p.propertyID AND pl.listing_live = 1)'

CREATE TABLE #ResultsTable
(
row_num int IDENTITY(1,1) PRIMARY KEY,
propertyID int,
title varchar(300),
property_type varchar(36),
sale_statusID char(3),
address1 varchar(60),
address2 varchar(60),
town varchar(60),
county varchar(60),
postcode varchar(16),
price money,
bedrooms int,
bathrooms int
)
IF @@ERROR <> 0
GOTO ErrorHandler

INSERT INTO #ResultsTable
EXEC (@sql_string)
IF @@ERROR <> 0
GOTO ErrorHandler

SELECT @total_rows = SCOPE_IDENTITY()
IF @@ERROR <> 0
GOTO ErrorHandler

SELECT rt.propertyID, MAX(photoID) AS 'photoID', pp.thumb_height, title, property_type, sale_statusID, address1, address2, town, county, postcode, price, bedrooms, bathrooms, file_extension
FROM #ResultsTable rt
LEFT JOIN (SELECT p.thumb_height, p.propertyID
FROM (
SELECT MAX(z.photoID) AS 'photoID', z.propertyid
FROM LM001_property_photo z
WHERE z.propertyID = rt.propertyID
) mx
JOIN LM001_property_photo p ON p.propertyid = mx.propertyid AND p.photoid = mx.photoid
) pp ON pp.propertyID = rt.propertyID
GROUP BY rt.propertyID, pp.thumb_height, title, property_type, sale_statusID, address1, address2, town, county, postcode, price, bedrooms, bathrooms, file_extension
IF @@ERROR <> 0
GOTO ErrorHandler

DROP TABLE #ResultsTable

COMMIT TRANSACTION GetDataSet
RETURN 1

ErrorHandler:
ROLLBACK TRANSACTION GetDataSet
RETURN
GO



DECLARE @return int,
@row_total int

EXEC @return = LM001_ADMIN_search_notify_property
@where_clause = '(map_x >= 000001 AND map_y >= 000001) AND (map_x <= 000001 AND map_y <= 000001) AND (bedrooms BETWEEN 0 AND 8) AND (bathrooms BETWEEN 0 AND 8) AND p.listing_typeID = ''S''',
@total_rows = @row_total OUTPUT

SELECT @return AS 'RETURN VALUE', @row_total AS 'TOTAL ROWS'
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site