JOIN with multiple WHERE

[cjjubb]

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.

[rmiao]

Why design table PROFILE_DATA that way?

[ld_be]

Hello


I have a solution by using table PROFILE_DATA twice and renaming it.

SELECT USERS.Name
FROM USERS
INNER JOIN PROFILE_DATA AS PROFILE_DATA_1
ON USERS. Ident = PROFILE_DATA_1.Owner
AND PROFILE_DATA_1.Value = 'Rural'
INNER JOIN PROFILE_DATA AS PROFILE_DATA_2
ON USERS. Ident = PROFILE_DATA_2.Owner
AND PROFILE_DATA_2.Value = 'United Kingdom'

Maybe this can help you?


Greetings
ld_be

[ld_be]

Hello


This is a solution with more controls of table PROFILE_DATA.

SELECT USERS.Name
FROM USERS
INNER JOIN PROFILE_DATA AS PROFILE_DATA_1
ON USERS. Ident = PROFILE_DATA_1.Owner
AND PROFILE_DATA_1.Name = 'AreaType'
AND PROFILE_DATA_1.Value = 'Rural'
INNER JOIN PROFILE_DATA AS PROFILE_DATA_2
ON USERS. Ident = PROFILE_DATA_2.Owner
AND PROFILE_DATA_2.Name = 'Country'
AND PROFILE_DATA_2.Value = 'United Kingdom'


Greetings
ld_be

[ld_be]

Same thread at
http://forums.databasejournal.com/sh...ad.php?t=48598