[RESOLVED] join help

mfacer

Hey guys... im trying to get out some data and join two tables.

table1 - processes

processID	name
====================
1.01	feed fish
1.02	feed dog
2.01	do shopping
2.02	clean house

table2 - results

processID	userid	answer
======================
1.01	321	3
1.02	321	2
1.01	435	1	
2.02	435	7
2.01	665	4

I need to do a join to get the data from table 2 (results) but also lookup the process name... so my results should be (by user - where say userid = 321)

export: userID = 321

process	your answer
=================
feed fish	3
feed dog	2

at the mo, I can get the first results ok but for some reason when I join, I get far too many results back!! here's the code I'm using

SELECT
pr.name as name,
an.answer as answer
FROM answers an, processes pr
WHERE an.processID = pr.processID
AND an.userID=321

i was expecting 2 results for example, but i get hundreds....

[edit]
basically, all I need to do is lookup the NAME of the processID for each record in the answers table!!
[/edit]

ahundiak

SELECT
pr.name as name,
an.answer as answer
FROM answers an
LEFT JOIN processes pr ON pr.processID = an.processID
WHERE an.userID=321

mfacer

that is almost what I've tried since posting... but it actually returns thousands of records!! (I aborted at 15,000 before!)

there are thousands of processes, and thousands of answers.... I just know know why its not working!!

eg: the following returns 45 records

SELECT processID, answer FROM answers WHERE userid='321' AND processID='2.01'

(there are actually many answers by processID)

if I then add in a bit to try and get the NAME of the processID, it returns thousands!! (2745 to be exact!)

SELECT
an.answer,
an.processID,
pr.name
FROM answers an
LEFT JOIN processes pr
ON pr.processID = an.processID
WHERE an.userID='321' AND re.processID='2.01'

ahundiak

Strange. What database are you using? And is processId unique in the process table?

This must be a typo:
WHERE an.userID='321' AND re.processID='2.01'
Are you in fact using
WHERE an.userID='321' AND an.processID='2.01'
Or
WHERE an.userID='321' AND pr.processID='2.01'
The latter will cause problems.

How many records does
SELECT name FROM process WHERE processID = '2.01';
yield? If more than one record is returned then your table data is messed up. I'm guessing that you get 61 records.

mfacer

ahundiak wrote:
How many records does
SELECT name FROM process WHERE processID = '2.01';
yield? If more than one record is returned then your table data is messed up. I'm guessing that you get 61 records.

hmm....yes, I get 61!

It's not great design i know.. but each "user" has their own set of processes. I think we're eventually going to standardise the list, but for now, this is the way it is. :bemused:

you made me realise that I need another WHERE clause... so I added

AND pr.userid = "321" and its now returning the right number of rows!! thanks for the help guys 🙂