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 2005 Forums
 Transact-SQL (2005)
 JOIN with multiple where

Author  Topic 

cjjubb
Starting Member

5 Posts
Posted - 2008-06-20 : 11:30:42
Hi all.

I am currently trying to achieve the following

I have the following two tables

TABLE 1 - USERS

Ident | Name
--------------------
34 | Dave
35 | Adam
36 | Lucy
37 | Tom


TABLE 2 - PROFILE_DATA

PDID | Owner | Name | Value
-----------------------------------------------
660 | 34 | AreaType | Rural
661 | 34 | Country | United Kingdom
662 | 35 | AreaType | Rural
663 | 35 | Country | United States
664 | 36 | AreaType | Rural
665 | 36 | Country | United Kingdom
666 | 37 | AreaType | City
667 | 37 | Country | United Kingdom



I basically want to SELECT all the users from the database who are based in a 'Rural' location and live in the 'United Kingdom'.

In this case this would be 'Dave' and 'Lucy'.


I am aware I must create a JOIN between USERS.IDENT and PROFILE_DATA.IDENT and then execute two 'WHERE' clauses.

However I am unsure how to go about doing this.


Any help would be grately appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-20 : 11:38:38
[code]SELECT u.Name
FROM USERS u
INNER JOIN (SELECT Owner,
MAX(CASE WHEN Name='AreaType' THEN Value ELSE NULL END) AS AreaType,
MAX(CASE WHEN Name='Country' THEN Value ELSE NULL END) AS Country
FROM PROFILE_DATA
GROUP BY Owner) o
ON o.Owner=u.Ident
WHERE o.Areatype='Rural'
AND o.Country='United Kingdom'[/code]
Go to Top of Page

Van
Constraint Violating Yak Guru

462 Posts
Posted - 2008-06-20 : 11:47:22
Homework...
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts
Posted - 2008-06-20 : 11:51:26
Yup, Visakh's query might get him in trouble with his prof if he has to explain it, better use this one!

SELECT a.Ident,a.[Name]
FROM
users a
INNER JOIN
profile_data b
ON
a.ident = b.Owner
INNER JOIN
profile_data c
ON
b.owner = c.Owner
WHERE b.[value] = 'United Kingdom' and c.[value] = 'Rural'
Go to Top of Page

cjjubb
Starting Member

5 Posts
Posted - 2008-06-20 : 12:49:00
Many Thanks jimf, that query appears to work.

However, I now have a follow up question.

I have added some numeric data to the PROFILE_DATA table as follows

TABLE 1 - USERS

Ident | Name
--------------------
34 | Dave
35 | Adam
36 | Lucy
37 | Tom


TABLE 2 - PROFILE_DATA

PDID | Owner | Name | Value
-----------------------------------------------
660 | 34 | AreaType | Rural
661 | 34 | Country | United Kingdom
662 | 35 | AreaType | Rural
663 | 35 | Country | United States
664 | 36 | AreaType | Rural
665 | 36 | Country | United Kingdom
666 | 37 | AreaType | City
667 | 37 | Country | United Kingdom
668 | 34 | Age | 31
669 | 34 | Weight | 211
670 | 35 | Age | 22
671 | 35 | Weight | 198
672 | 36 | Age | 56
673 | 36 | Weight | 278
674 | 37 | Age | 29
675 | 37 | Weight | 304


If I wanted to find those with a weight LESS THAN 250 (in this case Dave and Adam) I come across a problem.

All of the users are returned as they all have an AGE less than 250!

Is there a way to modify jimfs query to make use of PROFILE_DATA.Name to prevent this happening.

Once again I would be grateful for any help regarding this issue.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-20 : 12:56:33
quote:
Originally posted by cjjubb

Many Thanks jimf, that query appears to work.

However, I now have a follow up question.

I have added some numeric data to the PROFILE_DATA table as follows

TABLE 1 - USERS

Ident | Name
--------------------
34 | Dave
35 | Adam
36 | Lucy
37 | Tom


TABLE 2 - PROFILE_DATA

PDID | Owner | Name | Value
-----------------------------------------------
660 | 34 | AreaType | Rural
661 | 34 | Country | United Kingdom
662 | 35 | AreaType | Rural
663 | 35 | Country | United States
664 | 36 | AreaType | Rural
665 | 36 | Country | United Kingdom
666 | 37 | AreaType | City
667 | 37 | Country | United Kingdom
668 | 34 | Age | 31
669 | 34 | Weight | 211
670 | 35 | Age | 22
671 | 35 | Weight | 198
672 | 36 | Age | 56
673 | 36 | Weight | 278
674 | 37 | Age | 29
675 | 37 | Weight | 304


If I wanted to find those with a weight LESS THAN 250 (in this case Dave and Adam) I come across a problem.

All of the users are returned as they all have an AGE less than 250!

Is there a way to modify jimfs query to make use of PROFILE_DATA.Name to prevent this happening.

Once again I would be grateful for any help regarding this issue.




SELECT u.Name
FROM  USERS u
INNER JOIN PROFILE_DATA pd
ON pd.Owner=u.Ident 
AND pd.Name='Weight'
AND pd.Value<250


suggest you to learn read about joins in books onilne and understand their usage. You would gain nothing by simply asking the answer without trying it yourselves and understanding.
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