Jump to content


Photo

Table join question


  • Please log in to reply
2 replies to this topic

#1 elmpp

elmpp
  • New Members
  • Pip
  • Newbie
  • 2 posts
  • LocationLondon

Posted 24 September 2003 - 01:22 PM

I have this question to answer as part of a job application, so i would be very grateful if i could get some help!!

The question in question is number 2c at this link:

http://www.freecarad...napper_test.pdf

I know this is a bit cheeky, but I\'m desparate to get this done. Is this possible within MySQL (i\'m trying it with that). I\'ve tried comparing the tables with an
if(SUBSCRIPTION.paperid=PAPER.paperid, \'True\', \'False\')
but can\'t seem to just get the 5 returned rows. Help!!!!!!!!!!!!
:x
When theres always biscuits in the tin, wheres the fun in biscuits.

#2 gizmola

gizmola
  • Administrators
  • Advanced Member
  • 4,664 posts
  • LocationLos Angeles, CA USA

Posted 25 September 2003 - 07:55 AM

select papername, IF(personid is null,\'False\', \'True\') as subscribedfrom paperleft outer join subscription on paper.paperid=subscription.paperidand subscription.personid =1order by papername

The important things:

-use left outer join to get a row whether or not Jacque is subscribed
-use AND to only get rows from subscription that are Jacque (personid =1). Since a person can only be subscribed to a paper once, this means that we either get a personid of 1, or NULL in the personid column.
-use IF on personid to test for NULL (ie a paper that Jacque is not a subscriber to)


Don\'t say I never did anything for ya :wink:
Now if you get the job, will I be getting a kickback from you?

#3 elmpp

elmpp
  • New Members
  • Pip
  • Newbie
  • 2 posts
  • LocationLondon

Posted 25 September 2003 - 10:34 AM

Thanks v. much. That means my application is finished. If i get that job I\'ll...
When theres always biscuits in the tin, wheres the fun in biscuits.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users