Using AND in INNER JOIN

[RodCramp]

I have 3 x tables LOG, SERVICES, SERVICELINKS.
LOG is used to write new records to.
SERVICES has a list of items that a LOG record may/may not have any number of references to.
Referencing is done by using table SERVICELINKS.
SERVICELINKS includes LOG_ID & SERVICES_ID.

I'm attempting to create a search form which allows users to enter multiple (up to 3) SERVICES_ID.
If multiple SERVICES_ID are selected they can choose if it is an AND or OR link between each Service.

The OR search is straight forward, having an issue with the AND side.

SELECT DISTINCT LOG.*
FROM Log INNER JOIN SERVICELINKS ON LOG.LOG_ID = SERVICELINKS.LOG_ID
WHERE WhereString (Variable which is generated, example OR is below)
ORDER BY LOG.LOG_DATE;

WhereString working sample for OR;
(((SERVICELINKS.SERVICES_ID)=364)) OR (((SERVICELINKS.SERVICES_ID)=264))

The solution should resolve multiple SERVICE_ID's that have the same LOG_ID.

Thanks in advance for any help given.

 

[Guus2005]

The description of your problem is not clear. But you can use IN

WHERE SERVICELINKS.SERVICES_ID IN (364, 264, 164)

HTH

 

[SimonB1978]

Hi,

I'm assuming that the combination of LOG_ID and SERVICES_ID is unique in SERVICESLINKS.

If so, the following query should do what you want:

SELECT DISTINCT *
FROM LOG
WHERE LOG_ID IN (SELECT LOG_ID FROM
(SELECT SERVICELINKS.LOG_ID, Count(SERVICELINKS.LOG_ID) AS CNT_LOG_ID
FROM SERVICELINKS
WHERE SERVICELINKS.SERVICES_ID=364 Or SERVICELINKS.SERVICES_ID=264
GROUP BY SERVICELINKS.LOG_ID
HAVING Count(SERVICELINKS.LOG_ID)=2))

You can then add another OR in the WHERE clause and change the number in the HAVING accordingly.

There might be a simpler way of doing it but that's all I found...

Hope that helps,

Simon B.

 

[RodCramp]

Thanks guys, SimonB1978 the result is exactly what I'm after.

Only issue now is that with > 80k records in SERVICELINKS it takes about 2 minutes to run, and I haven't even tried running it with the back-end on a network drive as yet.

Will try cloning recordset & comparing the 2(3) sets, am hoping this will deliver a quicker search result.

Thanks again for your input.

Rod

 

[Banana]

I'm not sure why we need this:

SELECT DISTINCT * 
FROM LOG 
WHERE LOG_ID IN ...

When we could just accomplish the same thing with this:

SELECT *
FROM SERVICELINKS
WHERE SERVICELINKS.SERVICES_ID=364 Or SERVICELINKS.SERVICES_ID=264
GROUP BY SERVICELINKS.LOG_ID
HAVING Count(SERVICELINKS.LOG_ID)=2));

If I'm missing a reason why there has to be a DISTINCT as well two nested subqueries, please explain how data is not coming out as expected.

 

[RodCramp]

Thanks Banana, this won't result in the requirement to filter the records in the main (LOG) table.
The SERVICELINKS table lists LOG_ID's & SERVICES_ID's. - The desire is to have a result from LOG.* table where any specific LOG_ID in table SERVICELINKS has 2 SERVICE_ID's satisfying the search request (264 & 364 as eg.)

SERVICELINKS table looks something like this;
- Filtered Table LOG with LOG_ID's 3 & 4 would be the result in this eg.
LOG_ID SERVICES_ID
1 264
1 150
2 364
2 150
3 120
3 364
3 264
4 264
4 364
4 150

 

[Banana]

I see.


What about this?

SELECT l.*
FROM LOG l
INNER JOIN
(SELECT LOG_ID
FROM SERVICELINKS
WHERE SERVICELINKS.SERVICES_ID=364 Or SERVICELINKS.SERVICES_ID=264
GROUP BY SERVICELINKS.LOG_ID
HAVING Count(SERVICELINKS.LOG_ID)=2) s
ON l.LOG_ID = s.LOG_ID;

Does that give you what you want?

 

[RodCramp]

Banana,
You're oddly bent, yellow with brown spots & squishy on the inside...
But a genius!
Results looking good & are FAST.
My only problem now is comprehending your mushy work… and trying to include other WHERE parameters allowed within the larger sort form… but that’s for another day.

Thanks,
Rod

 

[Banana]

Here's a quick rundown:

1) DISTINCT is going to be slower. Same would apply if we used GROUP BY. It's just nature of how they work. However, we want to avoid cases where we don't actually have to use either.

2) Joining is usually much more better optimized and will perform better than a subquery with IN or EXISTS.


Basically what I did was dump the result into a subquery so you could count for SERVICE_ID and return only LOG_ID. I'm going to pretend for the example you found five records of LOG_ID where they had certain SERVICE_ID more than twice. Thus that is the result. We then join this table with the original LOG ID which then filter out all LOG_ID, leaving us with only five LOG_IDs. It's basically same thing as the subquery with IN() but as I said, engines usually are optimized to use joins over subqueries (not that subqueries doesn't have their day)

That said, I just realized... if LOG_ID can be duplicated in the service table, it *may* be necessary to do a DISTINCT to avoid duplicate rows being created because of join returning more than one matches for same LOG_ID.

EDIT: Scratch that. You already have a GROUP BY, so the LOG_ID should be already distinct anyway so no need for DISTINCT. (Which goes to say that with regards to #1, you definitely do not need to do both a DISTINCT & GROUP BY. That would be just painful) But do make sure you don't get duplicates row due to multiple LOG ID being returned just to be safe. I'd hate it to have led you down a false path ending in lot of hurts & pains.

 

[RodCramp]

Great, LOG_ID is duplicated, but never with the same SERVICES_ID in the SERVICELINK Table, don't see any issues as yet requiring use of DISTINCT.
Thanks again.

 

[Banana]

Just to be 100% sure; did you note my edit when I realized that you already had GROUP BY and that we really never need a query with both DISTINCT and GROUP BY?

Glad it worked out for you, though.