Complicated sql query: properly joining four different tables

I have 4 tables: users, userpreference, userinfo, useredu

the last three tables use "id" as a foreign key referencing the table 'users' :

Query to be formulated:

i need to find the "top music among all single females who go to MSU"开发者_如何学Go

noting that MSU could also be 'Minnesota State University"

i have this query so far but it is not producing the correct results?

select userpreference.preferencevalue as 'Music', COUNT(*) as 'SingleFemaleCount'from users, userpreference, userinformation
where users.Id = userinformation.Id
and users.Id = userpreference.Id
and userpreference.Id = userinformation.Id
and users.Gender = 'female'
and userinformation.informationvalue = 'single'
and usereducation.school like 'msu%' OR like 'minnesota state%'
and userpreference.preferencetype = 'music' GROUP BY preferencevalue ORDER BY      COUNT(distinct users.Id) DESC limit 10

---

It might be as simple as you need some parenthesis in your where clause:

(usereducation.school like 'msu%' OR like 'minnesota state%')

Otherwise, the OR will be of lower precedence than the adjacent ANDs.

EDIT: 2011-03-06

Below, I have formatted the code to make it a little easier to read, and also moved the userinformation and usereducation checking into exists() clauses. The reason I am doing this is because if a user has more than 1 userinformation or usereductionat row matching your criteria, it would affect the count() aggregate.

select
    userpreference.preferencevalue as 'Music',
    COUNT(*) as 'SingleFemaleCount'

from users, userpreference
where users.Gender = 'female'
  and userpreference.Id = users.Id
  and userpreference.preferencetype = 'music'

  and exists
    (select *
    from userinformation
    where userinformation.Id = users.Id
      and userinformation.informationvalue = 'single')

  and exists
    (select *
    from usereducation
    where usereducation.Id = users.Id
      and (usereducation.school like 'msu%' OR like 'minnesota state%'))

GROUP BY userpreference.preferencevalue
ORDER BY COUNT(*) DESC limit 10

Another thing to check is that (usereducation.school like 'msu%' OR like 'minnesota state%') indeed finds all MSU records. If the result set is not too huge, would run a select distinct school from usereducation to check and see that sure you are getting all records.

Lastly, I sort of prefer to use the join syntax as follows:

select
    userpreference.preferencevalue as 'Music',
    COUNT(*) as 'SingleFemaleCount'

from users
inner join userpreference on userpreference.Id = users.Id
where users.Gender = 'female'
  and userpreference.preferencetype = 'music'

  and exists
    (select *
    from userinformation
    where userinformation.Id = users.Id
      and userinformation.informationvalue = 'single')

  and exists
    (select *
    from usereducation
    where usereducation.Id = users.Id
      and (usereducation.school like 'msu%' OR like 'minnesota state%'))

GROUP BY userpreference.preferencevalue
ORDER BY COUNT(*) DESC limit 10

I realize I totally changed your query, but hey this is homework, right :)

---

Dana is correct. You also need to add usereducation to the from clause:

from users, userpreference, userinformation, usereducation

and join it to users.

---

Ok, once you have usereducation added in the "FROM" and it's joined to the users table (you shouldn't need to join the other tables, but if you want, have at it). Try the below.

In a query, if you want to look for a column to equal one value or another in the WHERE clause, you'll need to list the columns twice:

AND (usereducation.school LIKE 'msu%' OR usereducation.school LIKE 'minnesota state%')