• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 443
  • Last Modified:

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..

SELECT post
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!
0
jamesEE
Asked:
jamesEE
  • 4
  • 4
  • 2
1 Solution
 
dlarlickCommented:

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.

SELECT
  Table2.Post
FROM
  Table2 JOIN Table1 ON Table2.username = Table1.username AND Table2.Category = @SearchCategory
WHERE
   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.  

SELECT post
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.



0
 
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'
AND IF
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!
0
 
hesCommented:
Add () around the and

SELECT post
FROM dbo.Table2, dbo.Table1
WHERE post = 'category' AND (Address1 = 'area'  OR  Address2 = 'area'  OR  Address3 = 'area' OR  Address4 = 'area')
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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..

Address1=London
Category=Red
Post=London is Cool

Address=USA
Category=Blue
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!
0
 
hesCommented:
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')
0
 
dlarlickCommented:

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


0
 
dlarlickCommented:

>>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

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

  Table2
   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.
 
0
 
jamesEEAuthor Commented:
dlarlick - that is EXACTLY what is happening, I'm so pleased you get what's happening!

Ok.. Table1 has these fields..
username
firstname
lastname
address1
address2
address3
address4

Table2 has these fields...
username
category
post

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
0
 
dlarlickCommented:

   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

     WHERE
         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


   
0
 
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!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now