A little help please.

[merq]

Hi Guys,

 

I have here what most would consider a pretty straight forward query:

 

SELECT things.thing_id
FROM things
    LEFT JOIN users USING ( thing_id )
WHERE things.inactive IS NULL
    AND users.user_id = 28
    OR users.user_id = 607
GROUP BY things.thing_id
ORDER BY things.stamp DESC , things.created DESC

 

My problem is. It returns two records. One 'thing' where both users are involved and another where only user #28 is involved.

 

Is there a way I can limit this query so that it only returns results if both users are involved?

 

Thanks,

merq

[kickstart]

Hi

 

Try this:-

 

SELECT things.thing_id
FROM things
INNER JOIN JOIN users a ON things.thing_id = a.thing_id
INNER JOIN JOIN users b ON things.thing_id = b.thing_id
WHERE things.inactive IS NULL
AND (a.user_id = 28
OR b.user_id = 607)
ORDER BY things.stamp DESC , things.created DESC

 

All the best

 

Keith

[merq]

Similar results:

 

The actual query (I wasn't at work when I made my first post) I just used was:

 

SELECT incidents.incident_id
  FROM incidents 
  INNER JOIN incident_students a ON incidents.incident_id = a.incident_id 
  INNER JOIN incident_students b ON incidents.incident_id = b.incident_id
WHERE incidents.inactive IS NULL 
  AND (a.student_id = 28 OR b.student_id = 607)
  ORDER BY date DESC , created DESC;

 

This now returns:

 

+-------------+
| incident_id |
+-------------+
|          16 | 
|          15 | 
|          15 | 
|          15 | 
|          15 | 
|          15 | 
+-------------+
6 rows in set (0.00 sec)

 

incident 16 is the one where only student #28 is involved. The only valid incident I need returned is #15.

 

Keep in mind this query is dynamically built as well, so I'm not sure I would like to keep adding more and more JOIN's dynamically. There has to be a better way.

 

Thanks for the help.

[kickstart]

Hi

 

Sorry, my mistake.

 

Change the OR to an AND.

 

All the best

 

Keith

[merq]

Thanks! That works.

 

Now just to figure how to add these JOINS dynamically. Is this the only way you know of?

 

It's just this entire query is actually much larger and dynamically created. I can use this JOIN method, but it doesn't look real neat.

 

Thanks much for your help though.

[kickstart]

Hi

 

Be easy enough to generate on the fly with something like:-

 

SELECT incidents.incident_id
  FROM incidents 
  INNER JOIN incident_students a ON incidents.incident_id = a.incident_id AND a.student_id = 28
  INNER JOIN incident_students b ON incidents.incident_id = b.incident_id AND b.student_id = 607
WHERE incidents.inactive IS NULL 
  ORDER BY date DESC , created DESC;

 

Just keep adding INNER JOINs.

 

However a bit iffy about doing that many joins.

 

Possibly use something like this

 

SELECT incidents.incident_id, COUNT(DISTINCT student_id) AS StudentIncidentCount
FROM incidents
INNER JOIN incident_students ON incidents.incident_id = incident_students.incident_id
WHERE incidents.inactive IS NULL 
AND incident_students.student_id IN (28,607)
HAVING StudentIncidentCount = 2
ORDER BY date DESC , created DESC;

 

Check the count is the same as the number of student ids

 

All the best

 

Keith

[merq]

Ah... thanks. That looks much cleaner.

 

There could be any number of students in one of these incidents, so yeah, this looks a much nicer solution and is working.

 

Thanks again. Awesome board!

[fenway]

To be fair, the number of JOINs won't be a problem -- it's a single index-key lookup, should be instant -- but the HAVING can be a problem, triggers a filesort, etc.