Multi-table MySQL help needed

Crussell

Hi all,

I need to compose a query which will interrogate 2 tables (user and pictures). It needs to find all the entries in user which have no corresponding entry in the pictures table (searching on user_id - a common column), then it needs to ascertain if the date column in user is between two values and finally it needs to check that the value of the trust_assigned field in user is equal to zero.

In summary - all fields from USER between two dates and which have a null value for trust_assigned which do NOT have a corresponding entry in PICTURES (based on the values of the user_id field.

The query I came up with looks like this:
select user.* from user left join pictures on user.user_id=pictures.user_id where (pictures.user_id=NULL) and (users.lastmod between '2002-12-31 00:00:00' and '2003-02-19 00:00:00') and (user.trust_assigned = 0)

but it doesn't seem to yield any results... 🙁

Can anyone shed any light on why it might not be working ?

Cheers

Chris

denimderek

try this instead:

select user.* from user left join pictures on user.user_id=pictures.user_id where (pictures.user_id IS NULL) and (users.lastmod between '2002-12-31 00:00:00' and '2003-02-19 00:00:00') and (user.trust_assigned = 0)

Note that I changed pictures.user_id=NULL to pictures.user_id IS NULL

Crussell

Ah works a treat! thanks for that 🙂

smanandhar

Hi, Sorry I am not posting a solution, I have problem instead.

I have two tables, ginfo and experience. Fields in 'ginfo' are ID[int auto_increment], GName, ExpYear, Training and Image. experience has GName,Mountain,Alt,Summit and Attempt.

I have set it as ginfo.ID=experience.GName

how do I link these two tables and list the data of experience corresponding to the ID of ginfo?

Sorry, my english might be not understandable. However, if any of could be of some help, I had appreciate a lot.

thanks

denimderek

I think what you're asking MAY be

SELECT * FROM ginfo, experience WHERE ginfo.ID=experience.GName;