1,105,177 Community Members

query that joins two tables depending on third table

Member Avatar
adishardis
Junior Poster
101 posts since Jun 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Hi,

I have a 'persons' table, an 'answer' table and a 'token' table.

The token table contains matching unique identifiers from the persons table and the answers table.

In my query I want to join the persons table with the answers but I don't know how to proceed since they don't contain matching identifier and I would have to check the token table somehow in the query.

I did try to create a trigger in mysql admin to update the persons table with the corresponding token value but my query resulted in a "#1419 - You do not have the SUPER privilege". As this failed I gather that I have to create this query or is there a better solution?

Cheers
Adam

Member Avatar
pritaeas
mod_pritaeas
11,287 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,831 [?]
Skill Endorsements: 154 [?]
Moderator
Featured
Sponsor
 
0
 

If you can show the structure and some data, we can help you write the query.

Member Avatar
adishardis
Junior Poster
101 posts since Jun 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

I want to join everything from the persons table and anwers table.

Token table:
--------token---------unikid------
------aaaBBB324Cc-----777777

Persons table:
-----id--------unikid----------postnummer---------born--...........
-----1---------777777-------------19552--------1978-02-12 ..........

Answers table:
-----token-------question1a1-------question1a2-------question2------........
--aaaBBB324Cc---------Y----------------NULL--------------Y-----........

Cheers!
Adam

Member Avatar
pritaeas
mod_pritaeas
11,287 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,831 [?]
Skill Endorsements: 154 [?]
Moderator
Featured
Sponsor
 
2
 
SELECT * 
FROM Persons p, Token t, Answers a
WHERE p.unikid = t.unikid
AND t.token = a.token
Member Avatar
adishardis
Junior Poster
101 posts since Jun 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Brilliant!! Thanks alot!

Question Answered as of 1 Year Ago by pritaeas
Member Avatar
adishardis
Junior Poster
101 posts since Jun 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Could you help me rewrite the select statement if I want for example count all the occurrences of 'Y' in column question1a1 from the answers table AND get a date from the Persons table.

The date from the persons table should of course fullfill the condition you wrote above
FROM Persons p, Token t, Answers a WHERE p.unikid = t.unikid AND t.token = a.token)

Cheers
/Adam

Member Avatar
pritaeas
mod_pritaeas
11,287 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,831 [?]
Skill Endorsements: 154 [?]
Moderator
Featured
Sponsor
 
1
 

Something like this?

SELECT COUNT(*)
FROM Persons p, Token t, Answers a
WHERE p.datecolumn = 'yyyy-mm-dd'
AND p.unikid = t.unikid
AND t.token = a.token
AND a.question1a1 = 'Y'
GROUP BY a.question1a1

Next time better start a new discussion thread, and reference this one.

Member Avatar
adishardis
Junior Poster
101 posts since Jun 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Thank you!!

/Adam

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: