Complicated Query

[claireWXY]

Hi! I have been asked to sort a table into a patricular order to be used with a mail merge, however, I have no idea where to start with the query!

The table currently consists of Schoolname, Address, Subject.

Single schools can have multiple subjects. For example,

 SELECT * WHERE schoolname='The Streetly School'

, may return 4 rows, all the same except for the 'Subject' column, which could contain 'Mathematics', 'English', 'Science', and another 'Mathematics' - for good measure.

Now for the query:

I want to sort the table by schools, based on what subjects they have.
For instance, I want the address of a school that has english maths and science, but does not have art, is this possible?

I've tried

WHERE subject='mathematics'
OR WHERE subject='English'
OR WHERE subject='science'

But of course it gets all schools that have EITHER of those subjects, not schools that have all 3 subjects.

Thanks for your time in reading this essay!

[subash]

Can you place the table structure of the table and why you have used 3 WHERE command in the query?


Subash :)

[ronverdonk]

Hi! I have been asked to sort a table into a patricular order to be used with a mail merge, however, I have no idea where to start with the query!

The table currently consists of Schoolname, Address, Subject.

Single schools can have multiple subjects. For example,

 SELECT * WHERE schoolname='The Streetly School'

, may return 4 rows, all the same except for the 'Subject' column, which could contain 'Mathematics', 'English', 'Science', and another 'Mathematics' - for good measure.

Now for the query:

I want to sort the table by schools, based on what subjects they have.
For instance, I want the address of a school that has english maths and science, but does not have art, is this possible?

I've tried

WHERE subject='mathematics'
OR WHERE subject='English'
OR WHERE subject='science'

But of course it gets all schools that have EITHER of those subjects, not schools that have all 3 subjects.

Thanks for your time in reading this essay!

Since you have only 1 column for the subject, how do you want to select on the existence of 4 different values? That is impossible, unless you have all these values ('mathematics', 'English', 'science' and 'art') stored in one string in one column with name 'subject' (like column content ='mathematics, science, art, English'). Do you?

Ronald :cool:

[vpmurdan]

For each criteria, you'll have to add the table in the FROM clause

for example, the your mats, eng , science query:

 
select a.schoolname
from school a, school b, school c
where 
a.schoolname = b.schoolname
and a.schoolname=c.schoolname
and a.subject = 'mathematics'
and b.subject = 'english'
and c.subject = 'science'
 
 

[claireWXY]

Hi guys, I think ronverdonk hit the nail on the head... They come up as individual rows, so this method may be impossible. Is there anyway of having the first record appear, then additional columns show up on the same row?

Allow me to explain....

I have one table called 'orders'. Orders is pretty awesome, It holds the name and address, the unique key is the 'OrderEntryId' column.

I have another table called 'productorders', this has the individual products ordered in one order on seperate rows. It's unique key is the 'ProductOrderEntryId' although it does contain the same 'OrderEntryId' column that corresponds to the order.

So 'orders' looks like this:

OrderEntryId |  School Name | Address | 
 
1211                The School      England

'productsorders' looks like this

ProductOrderEntryId | OrderEntryId | Subject |
 
234                               1211           English
235                               1211           Maths
236                               1211           Science

Could I do some sort of join that will make my table look like this:

SELECT orders.orderentryid, orders.schoolname, orders.address, productorders.subject#1, products.subject#2, products.subject#3, products.subject#n
SOME JOIN COMMAND 
WHERE orders.orderentryid='1211' ;
 
OrderEntryId |  School Name | Address |  Subject#1 | Subject#2 | subject#3| 
1211               The School      England    English        Maths       Science

Effectively making rows into additional columns?

I found THIS, but as I don't have an equivelent to the 'location'... Couldn't figure out how to implement it.

Any more help guys???

THANKS TO EVERYONE WHO'S REPLIED SO FAR!! I REALLY DO APPRECIATE YOUR TIME AND EFFORT!

[vpmurdan]

Hi!. didn't you check my post at no: 4. Do you have any problem running it?

Hi guys, I think ronverdonk hit the nail on the head... They come up as individual rows, so this method may be impossible. Is there anyway of having the first record appear, then additional columns show up on the same row?

Allow me to explain....

I have one table called 'orders'. Orders is pretty awesome, It holds the name and address, the unique key is the 'OrderEntryId' column.

I have another table called 'productorders', this has the individual products ordered in one order on seperate rows. It's unique key is the 'ProductOrderEntryId' although it does contain the same 'OrderEntryId' column that corresponds to the order.

So 'orders' looks like this:

OrderEntryId |  School Name | Address | 
 
1211                The School      England

'productsorders' looks like this

ProductOrderEntryId | OrderEntryId | Subject |
 
234                               1211           English
235                               1211           Maths
236                               1211           Science

Could I do some sort of join that will make my table look like this:

SELECT orders.orderentryid, orders.schoolname, orders.address, productorders.subject#1, products.subject#2, products.subject#3, products.subject#n
SOME JOIN COMMAND 
WHERE orders.orderentryid='1211' ;
 
OrderEntryId |  School Name | Address |  Subject#1 | Subject#2 | subject#3| 
1211               The School      England    English        Maths       Science

Effectively making rows into additional columns?

I found THIS, but as I don't have an equivelent to the 'location'... Couldn't figure out how to implement it.

Any more help guys???

THANKS TO EVERYONE WHO'S REPLIED SO FAR!! I REALLY DO APPRECIATE YOUR TIME AND EFFORT!

[claireWXY]

Hellooo

I don't know if I understand it.
There aren't seperate tables for schools, or for subjects.

[vpmurdan]

Hellooo

I don't know if I understand it.
There aren't seperate tables for schools, or for subjects.

WXY run the query I gave at post #4 and see what it gives.

There is only 1 table, which I call 3 times in the query using different alias. This has the same effect as if I was calling 3 different tables.