SQL (Structured Query Language) Help needed

[woopidoo2]

I know that I shouldnt be asking others do solve things for me, but this is an emergency.

 

 

 

I need some help in making this SQL query, I failed at the first attempt and I havent got a clue what the answer is.

 

Our whole class is having trouble with it actually, except for the few die-hard coders.

 

 

 

The information:

 

 

 

''Select the first name and Email adress from everyone with an income below 20.000.

 

On top of that, select the message-number, title and text from messages that belong to the theme 'fun!!'.

 

(Make sure that the different messages are next to eachother in one rule, behind the first name and email-adress from that person.)''

 

 

 

[First name], and [income] are from the database 'Persons'

 

[Message number], [title] and [text] are from the database 'Messages'

 

['Theme'] is from the database 'Newsletters'.

 

 

 

I will give you more information if you need it :)

 

 

 

Thanks in advance, if there's an SQL-expert out there.

[Jard_Y_Dooku]

I'm not sure I understand the question entirely. Do you have the database schemas or is this like a textbook question? If the former, I'll be able to get a query for you.

 

 

 

''Select the first name and Email adress from everyone with an income below 20.000." works out to this:

 

 

 

SELECT first_name, email_address FROM persons WHERE income <= 20;

 

 

 

but I think you want all this information in one query? I can't see how all the data relates with the information you've given, so I'll need more if you want a complete query

[acenator]

It's been a while since I did anything with SQL, but I have my trusty SQL Bible, so I might as well give it a shot (I assume that, by "20.000," you mean 20,000). :P

 

 

 

SELECT first_name, email_address from Persons where income<20000;

 

 

 

 

 

 

 

For the second part, I need a value that connects the two tables (Messages and Newsletters). I'm guessing it is the Message number and, if so, try this:

 

 

 

 

 

SELECT Message_number, title, text from Messages in (SELECT Message_number from Newsletters where Theme='fun!!');

 

 

 

 

 

Not sure about what's being asked in the parentheses, so good luck with that part. :thumbsup: :lol:

[Jard_Y_Dooku]

A bit in the reverse. The Messages table should have a newsletter_id field pointing to the newsletter that message belongs to. So try this:

 

 

 

SELECT Message_number, title, text, newsletter_id FROM Messages INNER JOIN Newsletters ON Messages.newsletter_id = Newsletter.id WHERE Newsletter.theme = 'fun!!';

[woopidoo2]

I uploaded the .mdb file:

 

 

 

http://rapidshare.de/files/47589494/kla ... d.mdb.html

 

 

 

The words are in dutch, here's the question again:

 

 

 

Select [voornaam] and [e-mail] from everyone with an income of 20.000.

 

Select [berichtnummer], [titel] and [tekst] from messages (berichten) that belong to the theme Fun!!.

 

 

 

(Make sure that the messages are next to eachother on one rule, behind the first name and email-adress from that person.)

 

 

 

I believe I have to bring 3 different tabels into 1 query, although I'm still lost on how to do this.

 

 

 

Thanks alot for the help so far :)

[JoeDaStudd]

The trouble is theres no fields shared between the personen table and any other table (that I can make out).

 

 

 

Edit this should work

 

SELECT personen.Voornaam, personen.[E-mailadres], personen.Inkomen, nieuwsberichten.berichtnummer, nieuwsberichten.titel, nieuwsberichten.tekst, nieuwsbrieven.thema

FROM personen, nieuwsbrieven INNER JOIN nieuwsberichten ON nieuwsbrieven.nieuwsbriefnr = nieuwsberichten.nieuwsbriefnr

WHERE (((personen.Inkomen)>20000) AND ((nieuwsbrieven.thema)="Fun!!"));

[woopidoo2]

The trouble is theres no fields shared between the personen table and any other table (that I can make out).

 

 

 

Edit this should work

 

SELECT personen.Voornaam, personen.[E-mailadres], personen.Inkomen, nieuwsberichten.berichtnummer, nieuwsberichten.titel, nieuwsberichten.tekst, nieuwsbrieven.thema

FROM personen, nieuwsbrieven INNER JOIN nieuwsberichten ON nieuwsbrieven.nieuwsbriefnr = nieuwsberichten.nieuwsbriefnr

WHERE (((personen.Inkomen)>20000) AND ((nieuwsbrieven.thema)="Fun!!"));

 

 

 

I think you solved it :D

 

 

 

I cant see what would be wrong with this query, and it works so I guess this is the right answer :)

 

Thanks alot for helping me out :)