Inner Joins...

[Extension]

Hi,

I need some help with one query. I have two WHERE statements referring to the same table and obviously it's ignoring the second statement. Any help would be appreciated.

Data

__USER Table__
| ID    | NAME 	       | Prof     | UID   | LNID  |
| 9902 | Brian Chapman | Engineer | 2023  | 1032  |

__INFO Table__
| ID  | Type       | RW_ID   | Name       |
| 877 | University | 2023    | Lethbridge |
| 845 | Language   | 1032    | English    |

Query

SELECT FROM 
	USER, 
	INFO 
WHERE 0=0 
	and (INFO.TYPE = 'University' and INFO.RW_ID = USER.UID)
	and (INFO.TYPE = 'Language' and INFO.RW_ID = USER.LNID)

Thank you in advance.

 

[jebry]

Hi!

Try this:

WHERE 0=0
and (INFO.TYPE = 'University' and INFO.RW_ID = USER.UID)
or (INFO.TYPE = 'Language' and INFO.RW_ID = USER.LNID)

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Golom]

SELECT * FROM 
    [USER] INNER JOIN INFO 
    ON INFO.RW_ID = [USER].UID
WHERE INFO.TYPE IN ('University', 'Language')

Your second and third WHERE clauses are in conflict. There is no value of "INFO.TYPE" that is simultaneously equal to both 'University' and 'Language'.

You haven't specified any fields in the SELECT clause.

Also "User" is a reserved word in Access.

 

[lespaul]

and here's a great link on Joins you should read.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Extension]

jerbry
This won't work as I will get twice the amount of records.


Golom
I'm working in Oracle right now so I can use USER as the table name. Sorry for not mentionning.

Your query won't work because the University "Type" as to be matched with the USER.UID while the Language "Type" as to be matched with USER.LNID

Thank you for your help. Really appreciated. You guys are pretty quick...

 

[lespaul]

what do you want the results to look like? a single record with both University and Language or two records on with university and one with language?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Extension]

lespaul
One record with both University and Language

 

[lespaul]

SELECT USER.*, I1.Type, I2.Type FROM USER
INNER JOIN INFO I1 ON USER.UID = I1.RW_ID
INNER JOIN INFO I2 ON USER.LNID = I2.RW_ID

will return
9902 Brian Chapman Engineer 2023 1032 University Language

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi