Dont know why the query isnt working

[zammit1234]

Hi all,

Im trying to put together a query to get the results for a csv file.

I have two tables that im trying to get data from student_login and student_profile.

the query that im trying to do is this.

check that the user has an active account( from the table student_login) and then using the students_id check that there isnt a profile.

below is there query I’m trying … its working but looping the same names over and over

[php]
SELECT
student_login.student_id,
student_login.active,
student_login.email,
student_profile.student_id,
student_profile.percent
FROM
student_login,student_profile
WHERE
student_login.active = ‘1’
AND
student_profile.student_id != student_login.student_id

[/php]

any help would be great thanks

[m11]

Try adding this to the end of the query

GROUP BY student_login.student_id

[zammit1234]

HI thanks for the reply, this didn’t work it displayed all the users that have completed profile.

All i need it to do is check that the user hasn’t created a profile( so a row doesn’t exists with there user_id in)

In table student_login they have a user ID
when they create a profile it adds this id to a column called “user_id” so if there an easier way to check if there is an id matching the id from student_login.

Thanks

Ian

[m11]

If I understand correctly now, I think you would be better suited using a LEFT JOIN. For example:

SELECT `student_login`.`student_id`, `student_login`.`active`, `student_login`.`email`, `student_profile`.`student_id`, `student_profile`.`percent` FROM `student_login` LEFT JOIN `student_profile` ON `student_profile`.`student_id` = `student_login`.`student_id` WHERE `student_login`.`active` = '1';

What this should do is give you NULL values on student_profile.student_id, student_profile.percent if there is no match from the LEFT JOIN ON student_profile.student_id = student_login.student_id

So, if they have created a profile, the values will be there, otherwise they will be NULL.

[m11]

Another option, you could probably use a subquery.

SELECT * FROM `student_login` WHERE `student_id` NOT IN (SELECT `student_id` FROM `student_profile`)

[zammit1234]

Thanks a lot the subquery worked perfectly !!! I need to learn about LEFT JOIN and subquerys etc as ive not seen this before.

Cheers Ian

[wilson382]

Goog Job Matt