help with a sql query

Question

Post reply

help with a sql query

alison

SSC Veteran

Points: 288
More actions
January 31, 2005 at 11:23 pm

#183960

Hi
I'm going to give a simple example of what I'm trying to do and I'll make it more complicated for my purposes .
Say you had a table of properties, and associated with each property is a buyer and/or seller. Buyer and seller tables have first_name and surname fields. However, each property doesn't have to have a buyer or seller assigned (i.e they don't have to be added immediately or ever). So my property table has a field for buyer_id and seller_id (both of which can be null)
I'm trying (in a single dynamic sql query on a web page) to select all the properties where either the buyer or seller's first_name or surname contains a search term, e.g. Billie.
Something along the lines of [ assume there's only a buyer called Billie entered]
select t1.id, t2.first_name, t3.first_name as seller from properties as t1 LEFT OUTER JOIN buyer as t2 ON t1.buyer_id = t2.id LEFT OUTER JOIN sellers as t3 ON t1.seller_id = t3.id
This works and returns null for empty seller slots and Billie for the buyer. However, if I try:
select t1.id, t2.first_name, t3.first_name as seller from properties as t1 LEFT OUTER JOIN buyer as t2 ON t1.buyer_id = t2.id and (t2.first_name like '%Billie%' or t2.surname like '%Billie') LEFT OUTER JOIN sellers as t3 ON t1.seller_id = t3.id and (t3.first_name like '%Billie%' or t3.surname like '%Billie')
I get null values for all the contact details.
Is there any way to do this type of query in one select statement or should I be looking at doing two queries and joining the two results together?
Thanks for your help.
Regards, Alison

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

alison SSC Veteran Points: 288 More actions · Answer 1

Hi (me again )

I thought maybe I'd better explain how I'm imagining my query to work:

Select from property where

(if buyer_id is not null join to buyer table and see if contact details match search query)

and

(if seller_id is not null join to seller table and see if contact details match search query)

if either of the above match - return one result with the details else ignore and move on.

Hope that helps make more sense. Maybe I'm trying to be too complicated for SQL Server in a dynamic query and I'm just not experienced enough in SQL Server to work out if I can do this.

Thanks again for your help.

Regards, Alison

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 2

When you're dealing with outer joins, it becomes very important where you place your criteria - in the ON clause or in the WHERE clause. Depending on placement, the actual query answers entirely different questions.

For your question, I think you want to look for Billie in the WHERE clause.. Try this out:

select t1.id, t2.first_name, t3.first_name as seller

from properties as t1

LEFT OUTER JOIN buyer as t2

ON t1.buyer_id = t2.id

LEFT OUTER JOIN sellers as t3

ON t1.seller_id = t3.id

where (t2.first_name like '%Billie%' or t2.surname like '%Billie')

or (t3.first_name like '%Billie%' or t3.surname like '%Billie')

/Kenneth

noeld SSC Guru Points: 96590 More actions · Answer 3

Adding to Kenneth,

When you filter on the "ON" you still have values on the outer( in this case "properties" ) table. When Filtered on the where you are efectively removing missmatches on the whole result set.

For more examples go here or here

HTH

* Noel

alison SSC Veteran Points: 288 More actions · Answer 4

Thanks for that. Got it working so that it returns Billie

Didn't realise you could use joined tables in the where clause, thought it was on the ON bit only.

Good to know.

Thanks again.

Regards, Alison

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

One of the beauties of SQL is how flexible it is. You can (practically) use 'anything anywhere', with very few exceptions. Naturally, this agility also comes with responsibility - take it too far, and the blessing soon becomes a curse instead...

ie it's not too difficult to loose track if you nest too deep or become too 'creative'

/Kenneth