More SQL help please! :o)

[judas]

This one's for all the SQL gurus out there...

Got the following SQL statement to count the number of companies that have attended events during a specific time period:

SELECT distinct CompanyName
FROM tblcompanies
inner join tbldelegates on tblcompanies.CompanyID = tbldelegates.CompanyID
inner join tblattendees on tbldelegates.DelegateID = tblattendees.DelegateID
inner join tblevents on tblattendees.EventID = tblevents.EventID
WHERE Month(tblevents.EventDate) >= 1
and Month(tblevents.EventDate) <= 3
and Year(tblevents.EventDate) = 2004
and tblcompanies.CompanyCounty LIKE '%'
and tblcompanies.CompanyObj2 LIKE '-1'

This works fine However (you knew this was coming, right?) I need to be able to exclude companies that have already attended events before. The SQL for this would be:

SELECT distinct CompanyName
FROM tblcompanies
inner join tbldelegates on tblcompanies.CompanyID = tbldelegates.CompanyID
inner join tblattendees on tbldelegates.DelegateID = tblattendees.DelegateID
inner join tblevents on tblattendees.EventID = tblevents.EventID
WHERE tblevents.EventDate < '2004-01-01'
and tblcompanies.CompanyCounty LIKE '%'
and tblcompanies.CompanyObj2 LIKE '-1'

Anyone have the slightest clue how to do this, cos I'm stumped...

Thanks

[john_p]

No idea if this is possible with your SQL system, but can't you do

SELECT * FROM x,y,z where x.field not in (select x.field from x,y,z ....)

[pdV6]

A really noddy way would be to simply exlude the 2nd list from the 1st:

SELECT distinct CompanyName
FROM tblcompanies
inner join tbldelegates on tblcompanies.CompanyID = tbldelegates.CompanyID
inner join tblattendees on tbldelegates.DelegateID = tblattendees.DelegateID
inner join tblevents on tblattendees.EventID = tblevents.EventID
WHERE Month(tblevents.EventDate) >= 1
and Month(tblevents.EventDate) <= 3
and Year(tblevents.EventDate) = 2004
and tblcompanies.CompanyCounty LIKE '%'
and tblcompanies.CompanyObj2 LIKE '-1'

AND tblCompanies.ID NOT IN

(
SELECT distinct CompanyName.ID
FROM tblcompanies
inner join tbldelegates on tblcompanies.CompanyID = tbldelegates.CompanyID
inner join tblattendees on tbldelegates.DelegateID = tblattendees.DelegateID
inner join tblevents on tblattendees.EventID = tblevents.EventID
WHERE tblevents.EventDate < '2004-01-01'
and tblcompanies.CompanyCounty LIKE '%'
and tblcompanies.CompanyObj2 LIKE '-1'
)

NB This assumes a unique identifier in the tblcompanies table called "ID" - substitute with whatever you're actually using.

There's probably a much more elegant solution to be had, but I'm quite busy at the moment

{edited to add:} Like john_p said!

>> Edited by pdV6 on Wednesday 3rd March 15:40

[judas]

Tried the NOT IN route and for some reason it throws up an error (containing no useful information) all the time. But as far as I know, NOT IN (subquery) is supported. It's MySQL 4 btw.

continues...

[john_p]

Ah OK. I didn't realise MySQL 4 could use 'in' but, checking the docs, it can. It doesn't like NOT IN though. You may need to do

SELECT s1 FROM t1 WHERE s1 <> ANY (SELECT s1 FROM t2);

www.mysql.com/doc/en/ANY_IN_SOME_subqueries.html


>> Edited by john_p on Wednesday 3rd March 15:51

[judas]

Grrrrr.... still not working. Same useless error message

Thanks for the help btw

Edited to add: The error message says You have an error on your SQL syntax etc

I don't think there is an error in the syntax - I think it gets its knickers in a twist when you try to subquery the same table(s) as in the main query. Had this kind of problem before...

>> Edited by judas on Wednesday 3rd March 16:09

[judas]

Update: turns out the version of MySQL I am running doesn't support subqueries The version that does is still in alpha I've installed it locally and everything does what it should but I can't run a mission critical app on alpha software

Can't think of any way of pulling this data out without either using subqueries or pulling out two recordsets and dumping them into arrays to compare using ASP, which will be very slow and clumsy.

D'oh!

[RichardD]

judas said:

... This works fine However (you knew this was coming, right?) I need to be able to exclude companies that have already attended events before. The SQL for this would be: SELECT distinct CompanyName FROM tblcompanies inner join tbldelegates on tblcompanies.CompanyID = tbldelegates.CompanyID inner join tblattendees on tbldelegates.DelegateID = tblattendees.DelegateID inner join tblevents on tblattendees.EventID = tblevents.EventID WHERE tblevents.EventDate < '2004-01-01' and tblcompanies.CompanyCounty LIKE '%' and tblcompanies.CompanyObj2 LIKE '-1' Anyone have the slightest clue how to do this, cos I'm stumped... Thanks

This is some SQL from something I created to import from Sage to SQL. Sage has a table "STOCK" and SQL "tblProducts." By using the right join with an IS NULL condition it will list all records in tblStock that do not exist in tblProducts. By using one between your tblattendees and tblevents you may list all attendees that have not been to events.

SELECT STOCK.STOCK_CODE, TblProducts.ProductID
FROM TblProducts RIGHT JOIN STOCK ON TblProducts.PartNo = STOCK.STOCK_CODE
WHERE (((TblProducts.ProductID) Is Null));

HTH and I have not got wrong end of stick.

[judas]

I've got a solution using the IS NULL method, but that too requires a subquery to filter the data I want compare - I'm not comparing against all company names, just those that meet the same criteria as the main query except for the date of the event. Does that make sense?

Thanks though

[PetrolTed]

SELECT companyId, eventDate WHERE "in the period" INTO attendanceLog

SELECT companyId, eventDate WHERE "before that period" INTO previousAttendances

SELECT * from attendanceLog LEFT JOIN previousAttendances ON companyId where previousAttendances.companyId IS NULL

[fatsteve]

judas said:

Update: turns out the version of MySQL I am running doesn't support subqueries The version that does is still in alpha I've installed it locally and everything does what it should but I can't run a mission critical app on alpha software Can't think of any way of pulling this data out without either using subqueries or pulling out two recordsets and dumping them into arrays to compare using ASP, which will be very slow and clumsy. D'oh!

Judas,

WRT the first query, can should be able to use set expressions (rather than NOT IN - woefully poor performance).

Try:


SELECT distinct CompanyName
FROM tblcompanies
inner join tbldelegates on tblcompanies.CompanyID = tbldelegates.CompanyID
inner join tblattendees on tbldelegates.DelegateID = tblattendees.DelegateID
inner join tblevents on tblattendees.EventID = tblevents.EventID
WHERE Month(tblevents.EventDate) >= 1
and Month(tblevents.EventDate) <= 3
and Year(tblevents.EventDate) = 2004
and tblcompanies.CompanyCounty LIKE '%'
and tblcompanies.CompanyObj2 LIKE '-1'
minus
SELECT distinct CompanyName
FROM tblcompanies
inner join tbldelegates on tblcompanies.CompanyID = tbldelegates.CompanyID
inner join tblattendees on tbldelegates.DelegateID = tblattendees.DelegateID
inner join tblevents on tblattendees.EventID = tblevents.EventID
WHERE tblevents.EventDate < '2004-01-01'
and tblcompanies.CompanyCounty LIKE '%'
and tblcompanies.CompanyObj2 LIKE '-1'


You'll need to ensure the column list is the same in both queries (which it is - CompanyName)

>> Edited by fatsteve on Thursday 4th March 12:39

[judas]

Interesting idea, Ted - thanks! Of course, MySQL only supports SELECTing INTO a dumpfile, not another table.

My options are getting smaller and smaller all the time. Guess it's arrays for now then.

[fatsteve]

judas said:

Interesting idea, Ted - thanks! Of course, MySQL only supports SELECTing INTO a dumpfile, not another table. My options are getting smaller and smaller all the time. Guess it's arrays for now then.

Bugger!, if you can't do CTAS (create table as select) then perhaps you could try creating a views for your subqueries. Same query as ted is suggesting but create views rather than tables.

[shep]

Could you put Year(tblevents.EventDate) as one of the select fields then group by company name giving you company name and a number representing how many years are represented. You can therefore assume that if this number is greater than 1 you have a year record other than 2004 and the company should be excluded.

Does that make any sense at all?

[PetrolTed]

judas said:

Interesting idea, Ted - thanks! Of course, MySQL only supports SELECTing INTO a dumpfile, not another table.

Not true... (my syntax was wrong though)

An extract from my own code (names chanced to protect the innocent)...

REPLACE INTO recipients (email) SELECT emailAddr FROM phMembers etc.

[judas]

Thanks Ted - that seems to do the job!

Is there any way of automatically clearing the contents of the temporary table before dumping the new contents in, or it a case of doing a DELETE FROM [temptable] before doing anything else?

Muchios gracias!

[PetrolTed]

Nope, you need to do a DELETE FROM table first.

[judas]

OK - it's all working now! But... it's returning a different number of records than using subqueries Nearly there, nearly there...


Update: nope - the two queries were slightly different. All fixed now!

Woohoo!

Many thanks to everyone for the help!

>> Edited by judas on Thursday 4th March 14:55

[TheHobbit]

PetrolTed said:

Nope, you need to do a DELETE FROM table first.

This is the fastest way to clear a table as it basically drops it and recreates it empty.

[wiggy001]

TheHobbit said:

PetrolTed said: Nope, you need to do a DELETE FROM table first. This is the fastest way to clear a table as it basically drops it and recreates it empty.

TRUNCATE TABLE <tablename>, if supported, would be quicker but is not a logged transaction.