I'm trying to work out a sql statement which will return any/all posts from Table2 when a search is run against the 'category' field in Table 2, and it matches up with the fields: address1, address2,

Hello - please help!

I have a SQL database with two tables.

Table1 contains user information (Fields: username, address1, address2, address3 etc)

Table2 contains posts from those users (Fields: username, category and post).

I'm trying to work out a sql statement which will return any/all posts from Table2 when a search is run against the 'category' field in Table 2, and it matches up with the fields: address1, address2, address3 in Table1. This is what I have so far..

FROM dbo.Table2, dbo.Table1
WHERE post = 'category' AND Address1 = 'area'  OR  Address2 = 'area'  OR  Address3 = 'area' OR  Address4 = 'area'

So - you see, I only want posts returned when the category matches up with what is inputted for address1, 2 and 3.

What I have so far this doesn't work because it returns posts based on cateogry - but regardless of whether the address1,2 or 3 fields also match up.

I hope this makes sense, I've tried to explain as best I can. Any help would be muchly appreciated - I've been trying to figure this out for ages!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


try something like this,  I am taking a shot rewritting the statement based on your explanation.  I am assuming that you only want addresses where the username is common in both tables.

  Table2 JOIN Table1 ON Table2.username = Table1.username AND Table2.Category = @SearchCategory
   Table1.Address1 = @Area OR
   Table1.Address2 = @Area OR
   Table1.Address3 = @Area

Your statement, Not sure why you would do a cartesian product here.  Your problem statement does not seem to warrant this type of join.  (see my assumption about username above).  In addition I would put parens "(" around your address block to ensure proper order of precedence.  

FROM dbo.Table2, dbo.Table1
WHERE post = 'category' AND Address1 = 'area'  OR  Address2 = 'area'  OR  Address3 = 'area' OR  Address4 = 'area'

If you could post sample four or five lines of sample data, the output you get now and the expected output I would have a better understanding of your problem.

jamesEEAuthor Commented:
Hi, thanks so much for your reply - you've definatley grasped my problem - although the output does not need to be dependant on where the username is common in both tables.

I'm afraid since I've played around with it some more (still doesnt work tho!) and the code has changed - so I dont want to confuse you by including code which looks different to my original posting - but below I've written exactly what needs to happen...

Two variables are entered on my user form: Category and Address.

The address variable needs to look up 3 columns (Address1, Address2, Address3 - within Table1) and (should it find a result) it looks up whatever was entered into 'Category' and (should it match here too) return a result within 'Post'.

Example - if someone entered the category as 'red' and the address as 'london'...

IF dbo.Table2.Category = 'red'
dbo.Table1.Address1 = 'london'
OR dbo.Table1.Address2 = 'london'
OR dbo.Table1.Address3 = 'london'
THEN return dbo.Table2.post.

Clearly, this code doesnt work - but this is what I need it to do!
Add () around the and

FROM dbo.Table2, dbo.Table1
WHERE post = 'category' AND (Address1 = 'area'  OR  Address2 = 'area'  OR  Address3 = 'area' OR  Address4 = 'area')
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jamesEEAuthor Commented:
Thanks hes, I just tried what you suggested....

It returns the post from the correct Category - but is not being dependant on a match in one of the Address fields (remember, it needs to lookup fields: Address1, Address2 and Address3 for the address variable).

Example: Here are two of my records..

Post=London is Cool

Post=USA rules

The result I get when entering Address=London and Category=Blue is "USA Rules"

This shouldn't happen! It is finding a match REGARDLESS of the address variable.. it should only resolve "USA Rules" when Address=USA and Category=Blue...  Arg!
Select A.post from
Inner Join dbo.Table2 A dbo.Table2 B ON
A.username = B.username AND
A.Category = 'category' AND
(B.Address1 = 'area'  OR  B.Address2 = 'area'  OR  B.Address3 = 'area' OR  B.Address4 = 'area')

  I stand by my original response,  "hes" and I resolve to the same statement.


>>although the output does not need to be dependant on where the username is common in both tables.

What is the common key between the two tables then?

  You are performing a cartesian product.  Which is why you are returning the results you are getting

  username, address1, address2, address3
   1              London
   2              USA
   3              Canada

   username, category, post
   1              Red         London is Cool
   2              Blue        USA Rules
   3              Green      Canada is Cold

   Assuming the preceding a cartesian product would result as

  London   Red      London is Cool
  London   Blue     USA Rules
  London   Green   Canada is Cold
  USA       Red      London is Cool
  USA       Blue      USA Rules
  USA       Green   Canada is Cold
  Canada  Red      London is Cool
  Canada  Blue      USA Rules
  Canada  Green   Canada is Cold
Category = Blue and Address USA will restrict the record complex to the following result set

   London    Blue  USA Rules
   USA        Blue  USA Rules
   Canada   Blue  USA Rules
You must change to a join on a key value for a proper association from Table1 to Table2.
jamesEEAuthor Commented:
dlarlick - that is EXACTLY what is happening, I'm so pleased you get what's happening!

Ok.. Table1 has these fields..

Table2 has these fields...

I understand what you mean about a join on a key value in both tables (username, in this case) - but my search critea is to search a match for 'Category' (in Table2) AND a match from within either address1, 2, 3 or address4 (in Table1).
If a match is found for those TWO critea, only then - whatever relating to 'Category' in 'post' is returned (from Table2).

Surely this must be possible? I hope so.. or am I going about this completely wrong?
Thanks again for gettin back to me

   James -

     You CAN NOT join your tables in this fashion and retrieve what you are requesting.

   >> FROM dbo.Table2, dbo.Table1

    1.  Join on the key value user name this creates a proper record complex and does not match up categories to addresses that are incorrect.

    Joining on the username would create the following record complex
    London   Red      London is Cool
    USA       Blue      USA Rules
    Canada  Green   Canada is Cold

    Must Say JOIN {tablename} ON {tablename.Column} = {tablename.Column}
    >>Table2 JOIN Table1 ON Table2.username = Table1.username

    2.  Apply your restriction

         Table2.Category = 'Blue' AND
         (Table1.Address1 = 'USA' OR
          Table1.Address2 = 'USA' OR
          Table1.Address3 = 'USA OR
          Table1.Address4 = 'USA')

    This will return

     USA   Blue   USA Rules


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jamesEEAuthor Commented:
OH MY GOD!!! You are a genious! That has worked PERFFECTLY! Excatly what I wanted! Thankyou so much.

Sorry you only get 250 points - I tried to make it 500, but it said I didnt have enough points myself to make it that much...

REALLY appreciate it!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.