Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 15th, 2003, 07:13 AM
Registered User
 
Join Date: Oct 2003
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help with SQL Query

Hi guys

I've written the following SQL query but not sure if its as optimum as it could be. Its running in Access 2000. My company inspects mobile phones and I'm trying to get a list of all the duplicates from a particular job.

-------------------------------------------------------------------
SELECT Customer, Phone_Make, Imei, Date, Job_Ref, Pallet
FROM tbl_Imei AS i, tbl_Pallets AS j, tbl_Jobs AS k, tbl_Phones AS l
WHERE i.PalletID=j.PalletID
      AND j.JobID=k.JobID
      AND k.PhoneID=l.PhoneID
      AND Imei IN
                  (SELECT a.Imei FROM tbl_Imei AS a, tbl_Pallets AS b
                   WHERE a.Imei IN
                                   (SELECT Imei FROM tbl_Imei GROUP BY Imei HAVING COUNT(*) > 1)
                   AND b.JobID=[Forms]![frm_Jobs]![JobID]
                   AND a.PalletID=b.PalletID
                   GROUP BY a.Imei)
ORDER BY Imei, Date, Pallet;
-------------------------------------------------------------------
relationship diagram http://www.kamruz.pwp.blueyonder.co.uk/rels.gif

Its quite slow, but the numbers involved are quite big. Some jobs have 10 Pallets, each with 500 Units (=5000 per job) and we have had jobs before with 10,000 units so numbers will start to mount up soon. I've tried to restrict numbers by having a different DB for each type of phone.

I think the problem lies partly with this, the fact that I'm looking through the same table so many times and it is MS Access (but I may be wrong on all these points).

Any suggestions much appreciated.

Cheers
Kam
 
Old October 15th, 2003, 08:52 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

I'm not sure I understand what you are looking for. You say you are looking for 'duplicates', but duplicate what? That innermost SELECT statement finds duplicate values of 'Imei', but what does that have to do with a particular job?

Access is not the smartest DB in the world, and it may be that it is evaluating that query many many times.



Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old October 15th, 2003, 08:25 PM
Registered User
 
Join Date: Oct 2003
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Jeff

Sorry, should have been clearer. I'm looking for duplicate Imei's (an Imei being a 15 digit unique serial number for each phone, stored as text in the DB). Reason I need to find these is to do with VAT reg's and previously traded stock. We often find our own stock itself back to us (no point in paying VAT on the same good's twice right?).

The Select statement I built followed this logic:
1. Get a list of all duplicates in table
2. Then check if any Imei's in job match those of duplicates
3. Then get all details of these Imei's in recordset

I understand Access is a bit rubbish (I'm an ASP web developer and not an applications developer so this is the best way I could think of doing it, generally I have no need for such large recordsets). The seperate queries themselves work fine, this whole thing also works fine AS LONG AS there is a low number of records.

I was hoping there was a smarter way of evaluting this.

If anything is still unclear I'll try and explain better (I can see it in my head but finding it difficult to explain it better... sorry)


Many thanks
Kam
 
Old October 16th, 2003, 10:38 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

I think you are close. I approach this sort of thing in steps. First, you are only interested in duplicate 'Imei' values. Your innermost query correctly finds the duplicate 'Imei' values:
Code:
SELECT Imei FROM tbl_Imei GROUP BY Imei HAVING COUNT(*) > 1
Next, let's construct a query which obtains the desired information you wish to report from your tables, but without regard to duplicates or JobID selection:
Code:
SELECT Customer, Phone_Make, Imei, Date, Job_Ref, Pallet
FROM Jobs INNER JOIN Phones ON Jobs.PhoneID=Phones.PhoneID
    INNER JOIN Pallets ON Jobs.JobID=Pallets.JobID
    INNER JOIN Imei ON Pallets.PalletID=Imei.PalletID
(Forgive me from stripping off the 'tbl_' prefix from your table names - it's something that for some reason Access folks like to add to their table names and it drives me nuts :))
I prefer to use the 'infix' or 'new' style JOIN syntax.

Now, if you added a WHERE clause to the above query which selected based on JobID you'd get your desired data, though still for everything for that job and not just the duplicate 'Imei' values.

Which should give you a hint.

If a table existed which contained the results of the duplicates query, then you would simply JOIN that table in with the others on the above query and that would give you what you want. In SQL Server, you can used what's called a derived table, that is, I can directly include the duplicate-finding SELECT statement in the second query's FROM clause and treat it as though it were a table:
Code:
SELECT Customer, Phone_Make, Imei, Date, Job_Ref, Pallet
FROM Jobs INNER JOIN Phones ON Jobs.PhoneID=Phones.PhoneID
    INNER JOIN Pallets ON Jobs.JobID=Pallets.JobID
    INNER JOIN Imei ON Pallets.PalletID=Imei.PalletID
    INNER JOIN
        (SELECT Imei FROM tbl_Imei GROUP BY Imei HAVING COUNT(*) > 1) AS Dups
            ON Dups.Imei=Imei.Imei
And this would give me the requested data, just for the duplicates. Add a WHERE clause selecting the desired JobID and then ORDER things appropriately, and you are done.

Unfortunately, I don't think you can use derived tables in Access. (Access is brain-dead). The above could also be done using a view instead of a derived table, and a view can be used in Access - that is, a stored query.

So, if you define another Access query which is the duplicate finding inner query, you can JOIN it as I have JOINed the derived table above.

Now, This query is certainly simpler than your original, but whether it is faster only trying it will tell...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old October 19th, 2003, 07:21 AM
Registered User
 
Join Date: Oct 2003
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Jeff

Thanks for the feedback. Never understood JOINs before (therefore always done it with WHERE's). I had to nest the Joins for Access to acdcept it, it was good practice and has helped me understand the the damn thing a little better. I don't think its had much impact on speed but least its a little easier to understand (and I've gained a bit of knowledge).

thanks again

Kam

----------------------------------------------------------------------
SELECT
    tbl_Jobs.Customer,
    tbl_Phones.Phone_Make,
    tbl_Imei.Imei,
    tbl_Jobs.Date,
    tbl_Jobs.Job_Ref,
    tbl_Pallets.Pallet

FROM tbl_Phones
INNER JOIN
    (tbl_Jobs INNER JOIN
        (tbl_Pallets INNER JOIN
            (tbl_Imei INNER JOIN
                (SELECT Imei FROM tbl_Imei GROUP BY Imei HAVING COUNT(*) > 1) AS Dups
            ON tbl_Imei.Imei = Dups.Imei)
        ON tbl_Imei.PalletID = tbl_Pallets.PalletID)
    ON tbl_Jobs.JobID = tbl_Pallets.PalletID)
ON tbl_Phones.PhoneID = tbl_Jobs.PhoneID





Similar Threads
Thread Thread Starter Forum Replies Last Post
sql query i need seearam MySQL 7 November 30th, 2008 03:14 AM
Output Query to txt file from SQL Query everest SQL Server 2005 4 November 22nd, 2007 01:49 AM
SQL Query!! dpkbahuguna Beginning VB 6 5 October 12th, 2007 12:39 AM
Help with SQL query sattaluri Access 2 August 11th, 2006 09:26 AM
SQL query PinkyCat Classic ASP Databases 3 March 11th, 2005 01:41 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.