Jump to content

SQL (Structured Query Language) Help needed


woopidoo2

Recommended Posts

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.

carsignatureis3.jpg

[http://woopidoo2.deviantart.com]

[Tip.it Moderator from Dec 10, 2006 to 03 Sep, 2008]

Link to comment
Share on other sites

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

  • Never trust anyone. You are always alone, and betrayal is inevitable.
  • Nothing is safe from the jaws of the decompiler.

Link to comment
Share on other sites

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:

> SELECT * FROM users WHERE clue > 0;

0 rows returned

There's no place like 127.0.0.1

There are only 10 types of people

in this world: those who understand

binary and those who don't.

This statement is false.

$DO || ! $DO ; try

try: command not found

Link to comment
Share on other sites

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!!';

  • Never trust anyone. You are always alone, and betrayal is inevitable.
  • Nothing is safe from the jaws of the decompiler.

Link to comment
Share on other sites

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 :)

carsignatureis3.jpg

[http://woopidoo2.deviantart.com]

[Tip.it Moderator from Dec 10, 2006 to 03 Sep, 2008]

Link to comment
Share on other sites

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!!"));

[hide=Drops]

  • Dragon Axe x11
    Berserker Ring x9
    Warrior Ring x8
    Seercull
    Dragon Med
    Dragon Boots x4 - all less then 30 kc
    Godsword Shard (bandos)
    Granite Maul x 3

Solo only - doesn't include barrows[/hide][hide=Stats]

joe_da_studd.png[/hide]

Link to comment
Share on other sites

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 :)

carsignatureis3.jpg

[http://woopidoo2.deviantart.com]

[Tip.it Moderator from Dec 10, 2006 to 03 Sep, 2008]

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.