matching multiple records from a table

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)

matching multiple records from a table

Author

Topic

RichHamilton
Starting Member

4 Posts

Posted - 2010-05-05 : 05:57:25

I'm sure this must be something simple..

Basically I have a property table (SaleProperty)

then I have custom fields (CustomFieldInstanceValue)

in my SELECT statement, I want to be able to search my multiple custom fields, so I have sent a new table for the query as XML (##XmlTable ).

This all works great, except if I tick 3 check boxes so I have 3 records sent in the xml table it will match to the property table if any 1 of those values is present in the CustomFieldInstanceValue.

This is how my SQL looks:
INNER JOIN
CustomFieldInstanceValue AS cfiv
ON
sp.SalePropertyID = cfiv.OwnerID

INNER JOIN
##XmlTable AS a
ON cfiv.customfieldinstanceid = a.customfieldinstanceid
AND cfiv.optionlookupgroupid = a.optionlookupgroupid

But I just need to change it so that all records sent in the XML table must match the CustomFieldInstanceValue table.

Should i be using some type of Group By statment?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-05 : 07:21:40

yup you need to use group by statement with LEFT JOIN and compare distinct count from CustomFieldInstanceValue against ##XmlTable. they will be equal in cases where you'vr perfect match (all values match)

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

RichHamilton
Starting Member

4 Posts

Posted - 2010-05-05 : 08:21:09

Thanks Visakh
Is there any chance of a quick example so I can see how the Query would look?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-05 : 14:56:34

if tables are table1 and table2 and linking col is col1 then


SELECT t1.IDCol
FROM table1 t1
LEFT JOIN table2 t2
On t2.col1 = t1.col1
GROUP BY t1.IDCol
HAVING COUNT(DISTINCT t1.col1) = COUNT(DISTINCT t2.col1)

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

RichHamilton
Starting Member

4 Posts

Posted - 2010-05-06 : 03:58:36

Thanks very much for your help.
I will try this.

RichHamilton
Starting Member

4 Posts

Posted - 2010-05-06 : 06:07:33

ok, I've tried this within my Query.. the rest of my query is rather large and complicated (but works well).. but when I include my new JOIN it gives me an error:

SELECT....

FROM
SaleProperty AS sp

LEFT JOIN CustomFieldInstanceValue AS cfiv
On sp.SalePropertyID = cfiv.OwnerID
GROUP BY sp.SalePropertyID
HAVING COUNT(DISTINCT sp.SalePropertyID) = COUNT(DISTINCT cfiv.OwnerID)

INNER JOIN
##XmlTable AS a
ON cfiv.customfieldinstanceid = a.customfieldinstanceid
AND cfiv.optionlookupgroupid = a.optionlookupgroupid

bUT i'M NOW GETTING AN ERROR
An error occured while trying to retrieve a sale property System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'INNER'.
Incorrect syntax near the keyword 'AND'.
Incorrect syntax near the keyword 'AND'.
Line 86: Incorrect syntax near ')'.
Line 86: Incorrect syntax near ')'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-06 : 13:38:39

you need to put it in a derived table

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources