[SOLVED] help with this query .. almost working

[severndigital]

here's what i got

SELECT b.bowlName, DATE_FORMAT( b.bowlDate, '%a %m/%d/%Y' ) AS bowlDate,
DATE_FORMAT( b.closing, '%m/%d/%Y %h:%i:%s%p' ) AS bowlClose,
ent.status AS userStatus
FROM bowlList b
LEFT JOIN bowlEntries ent ON b.Id = ent.Id
WHERE b.complete = '0'
AND ent.userId = '$user_id'

 

it almost works, but i want the list to display items that are in the bowlList table that are not in the bowlEntries table.

 

right now it works only if the $user_id is in the bowlEntries list. .. i thought a LEFT JOIN would give me the results i needed, but i won't give me the items that are NOT in the bowlEntries field.

 

any help would be great.

 

Thanks in advance,

-C

[anthylon]

I think I understand your problem. But your tables/fields are confusing me . So as I am in rush I will give you another example so you can understand the point:

 

Table:

table_a

table_b

 

SELECT table_a.a1, table_a.a2, table_b.b1 
FROM table_a
WHERE table_a.id not in (SELECT table_b.id FROM table_b)

 

This way you will get only results where ID from table_a is not present in table_b.

 

But I assume there is a better way to make this works as this query will be pretty slow . Anyway I hope this will help you.

[aschk]

A quicker way would be to perform it without the subquery, and instead using a LEFT JOIN on NULL.

e.g.

 

SELECT b.bowlName
      ,DATE_FORMAT( b.bowlDate, '%a %m/%d/%Y' ) AS bowlDate
      ,DATE_FORMAT( b.closing, '%m/%d/%Y %h:%i:%s%p' ) AS bowlClose
      ,ent.status AS userStatus
FROM bowlList b
LEFT JOIN bowlEntries ent ON b.Id = ent.Id
WHERE ent.Id IS NULL

[severndigital]

thanks for the examples, but it was my fault for not explaining better so i'll give it another go

 

tables:

table_1  - list of events

table_2  - list of event with user_id and the users status for the event

userList  - list of userIds

 

so ... i want to display the status of the user if they have completed an event which would be listed in table_2. table_1 is the list of events.

 

The end result should look like this

 

EventName      EventDate          userStatus

 

 

The query needs to do the following

1. list all the events from table_1

2. go into table_2 and see if the event_id has an entry with the user_id

3. show the status of the event for that user from table_2

 

The final result would look sort of like this

 

Event1      3/20/2008            Submitted

Event2      3/21/2008            Not Started

Event3      3/22/2008            Incomplete

Event4      3/23/2008            Not Started

 

And so on.

 

The query i had was almost working, but it would only work when the user had some activity on the event. Obviously if they did not start working on the event, there would be no entry in table_2, which was why I thought a left join would work.

 

Thanks again for try to understand and taking a stab at it. I hope this helps clarify.

 

-C

 

 

 

 

 

 

 

 

 

[anthylon]

A quicker way would be to perform it without the subquery, and instead using a LEFT JOIN on NULL.

e.g.

 

SELECT b.bowlName
      ,DATE_FORMAT( b.bowlDate, '%a %m/%d/%Y' ) AS bowlDate
      ,DATE_FORMAT( b.closing, '%m/%d/%Y %h:%i:%s%p' ) AS bowlClose
      ,ent.status AS userStatus
FROM bowlList b
LEFT JOIN bowlEntries ent ON b.Id = ent.Id
WHERE ent.Id IS NULL

 

LOL this is good. It is simple but has sense - much faster of course.

 

Thank you for this - very very useful - I never thought that way. :-\