Please ehlp me to simplify this sql query, and please don't laugh too hard, this stuff is tricky and after weeks this works but i'm pretty sure it is **** ugly.

Here is the query from Dreamweaver

SELECT DISTINCT
tblcustomers.LocationsID, tblcustomers.CompanyName,
tbllocations.LocationsID, tbllocations.LocationName,
tblcatagories.CatagoriesID, tblcatagories.CatagoryName
FROM
tblcustomers, tbllocations, tblcatagories
WHERE
tblcustomers.LocationsID = tbllocations.LocationsID AND
tblcustomers.CatagoriesIDA = tblcatagories.CatagoriesID AND
tblcustomers.AccountStatus !='offline'
UNION SELECT DISTINCT
tblcustomers.LocationsID, tblcustomers.CompanyName,
tbllocations.LocationsID, tbllocations.LocationName,
tblcatagories.CatagoriesID, tblcatagories.CatagoryName
FROM
tblcustomers, tbllocations, tblcatagories
WHERE
tblcustomers.LocationsID = tbllocations.LocationsID AND
tblcustomers.CatagoriesIDB = tblcatagories.CatagoriesID AND
tblcustomers.AccountStatus !='offline'
UNION SELECT DISTINCT
tblcustomers.LocationsID, tblcustomers.CompanyName,
tbllocations.LocationsID, tbllocations.LocationName,
tblcatagories.CatagoriesID, tblcatagories.CatagoryName
FROM
tblcustomers, tbllocations, tblcatagories
WHERE
tblcustomers.LocationsID = tbllocations.LocationsID AND
tblcustomers.CatagoriesIDC = tblcatagories.CatagoriesID AND
tblcustomers.AccountStatus !='offline'
UNION SELECT DISTINCT
tblcustomers.LocationsID, tblcustomers.CompanyName,
tbllocations.LocationsID, tbllocations.LocationName,
tblcatagories.CatagoriesID, tblcatagories.CatagoryName
FROM
tblcustomers, tbllocations, tblcatagories
WHERE
tblcustomers.LocationsID = tbllocations.LocationsID AND
tblcustomers.CatagoriesIDD = tblcatagories.CatagoriesID AND
tblcustomers.AccountStatus !='offline'
UNION SELECT DISTINCT
tblcustomers.LocationsID, tblcustomers.CompanyName,
tbllocations.LocationsID, tbllocations.LocationName,
tblcatagories.CatagoriesID, tblcatagories.CatagoryName
FROM
tblcustomers, tbllocations, tblcatagories
WHERE
tblcustomers.LocationsID = tbllocations.LocationsID AND
tblcustomers.CatagoriesIDE = tblcatagories.CatagoriesID AND
tblcustomers.AccountStatus !='offline'
UNION SELECT DISTINCT
tblcustomers.LocationsID, tblcustomers.CompanyName,
tbllocations.LocationsID, tbllocations.LocationName,
tblcatagories.CatagoriesID, tblcatagories.CatagoryName
FROM
tblcustomers, tbllocations, tblcatagories
WHERE
tblcustomers.LocationsID = tbllocations.LocationsID AND
tblcustomers.CatagoriesIDF = tblcatagories.CatagoriesID AND
tblcustomers.AccountStatus !='offline'
UNION SELECT DISTINCT
tblcustomers.LocationsID, tblcustomers.CompanyName,
tbllocations.LocationsID, tbllocations.LocationName,
tblcatagories.CatagoriesID, tblcatagories.CatagoryName
FROM
tblcustomers, tbllocations, tblcatagories
WHERE
tblcustomers.LocationsID = tbllocations.LocationsID AND
tblcustomers.CatagoriesIDG = tblcatagories.CatagoriesID AND
tblcustomers.AccountStatus !='offline'
UNION SELECT DISTINCT
tblcustomers.LocationsID, tblcustomers.CompanyName,
tbllocations.LocationsID, tbllocations.LocationName,
tblcatagories.CatagoriesID, tblcatagories.CatagoryName
FROM
tblcustomers, tbllocations, tblcatagories
WHERE
tblcustomers.LocationsID = tbllocations.LocationsID AND
tblcustomers.CatagoriesIDH = tblcatagories.CatagoriesID AND
tblcustomers.AccountStatus !='offline'
UNION SELECT DISTINCT
tblcustomers.LocationsID, tblcustomers.CompanyName,
tbllocations.LocationsID, tbllocations.LocationName,
tblcatagories.CatagoriesID, tblcatagories.CatagoryName
FROM
tblcustomers, tbllocations, tblcatagories
WHERE
tblcustomers.LocationsID = tbllocations.LocationsID AND
tblcustomers.CatagoriesIDI = tblcatagories.CatagoriesID AND
tblcustomers.AccountStatus !='offline'
UNION SELECT DISTINCT
tblcustomers.LocationsID, tblcustomers.CompanyName,
tbllocations.LocationsID, tbllocations.LocationName,
tblcatagories.CatagoriesID, tblcatagories.CatagoryName
FROM
tblcustomers, tbllocations, tblcatagories
WHERE
tblcustomers.LocationsID = tbllocations.LocationsID AND
tblcustomers.CatagoriesIDJ = tblcatagories.CatagoriesID AND
tblcustomers.AccountStatus !='offline'
ORDER BY
LocationName, CatagoryName, CompanyName

I have tried to format the code to make it easier to read on this site, I hope I have not made it harder to use though.

If you need to see what this does it is for dynamic dropdown lists on my site here http://www.higuide.co.za.

Please let me know if more info is required, if you ot to the end of the code without giving up thank-you.

I've never seen a query that long . If you can list your schema and how and what you want extrapolated I'm positive I could simplify it for you and also explain how I do it.

SELECT DISTINCT
tblcustomers.LocationsID, tblcustomers.CompanyName,
tbllocations.LocationsID, tbllocations.LocationName,
tblcatagories.CatagoriesID, tblcatagories.CatagoryName
FROM
tblcustomers, tbllocations, tblcatagories
WHERE

tblcustomers.LocationsID = tbllocations.LocationsID AND

tblcustomers.AccountStatus !='offline' AND

(
tblcustomers.CatagoriesIDA = tblcatagories.CatagoriesID OR
tblcustomers.CatagoriesIDB = tblcatagories.CatagoriesID OR
tblcustomers.CatagoriesIDC = tblcatagories.CatagoriesID OR
tblcustomers.CatagoriesIDD = tblcatagories.CatagoriesID OR
tblcustomers.CatagoriesIDE = tblcatagories.CatagoriesID OR
tblcustomers.CatagoriesIDF = tblcatagories.CatagoriesID OR
tblcustomers.CatagoriesIDG = tblcatagories.CatagoriesID OR
tblcustomers.CatagoriesIDH = tblcatagories.CatagoriesID OR
tblcustomers.CatagoriesIDI = tblcatagories.CatagoriesID OR
tblcustomers.CatagoriesIDJ = tblcatagories.CatagoriesID
)

ORDER BY
LocationName, CatagoryName, CompanyName

That Should Work

Jamie

In looking at this query, I can't help but get the feeling that the schema of your database could be improved on as well. I'd be happy to take a look for you and see if we can't improve things a bit more for you.

Originally Posted by JamieLewis

SELECT DISTINCT
tblcustomers.LocationsID, tblcustomers.CompanyName,
tbllocations.LocationsID, tbllocations.LocationName,
tblcatagories.CatagoriesID, tblcatagories.CatagoryName
FROM
tblcustomers, tbllocations, tblcatagories
WHERE

tblcustomers.LocationsID = tbllocations.LocationsID AND

tblcustomers.AccountStatus !='offline' AND

(
tblcustomers.CatagoriesIDA = tblcatagories.CatagoriesID OR
tblcustomers.CatagoriesIDB = tblcatagories.CatagoriesID OR
tblcustomers.CatagoriesIDC = tblcatagories.CatagoriesID OR
tblcustomers.CatagoriesIDD = tblcatagories.CatagoriesID OR
tblcustomers.CatagoriesIDE = tblcatagories.CatagoriesID OR
tblcustomers.CatagoriesIDF = tblcatagories.CatagoriesID OR
tblcustomers.CatagoriesIDG = tblcatagories.CatagoriesID OR
tblcustomers.CatagoriesIDH = tblcatagories.CatagoriesID OR
tblcustomers.CatagoriesIDI = tblcatagories.CatagoriesID OR
tblcustomers.CatagoriesIDJ = tblcatagories.CatagoriesID
)

ORDER BY
LocationName, CatagoryName, CompanyName

That Should Work

Jamie

I am in shock, this worked perfectly and you did it in how long maybe 10 minutes, what took me 4 days to think up and about another 2 to actually et any where near the correct result that is amazing. Well maybe I should stop what i'm doing and go look for a manual labour job somewhere, cause I do not know how your brain must be wired to have even figured out what I was trying to achieve.

Thanks and my page load time seems quicker too.

Thanks again

Originally Posted by flann

In looking at this query, I can't help but get the feeling that the schema of your database could be improved on as well. I'd be happy to take a look for you and see if we can't improve things a bit more for you.

I would love for you to take a look at it if you would not mind, thanks for the offer, maybe you could pm me or email me direct and let me know how i can give you the information or if you could just log-in to me and take a look at how it could be improved

Thanks for all the help, for the second time in one evening.

Originally Posted by scorpioserve

I am in shock, this worked perfectly and you did it in how long maybe 10 minutes, what took me 4 days to think up and about another 2 to actually et any where near the correct result that is amazing. Well maybe I should stop what i'm doing and go look for a manual labour job somewhere, cause I do not know how your brain must be wired to have even figured out what I was trying to achieve.

Thanks and my page load time seems quicker too.

Thanks again

hehe, no problem, it was simply a case of grouping the conditions.

Jamie

aaaww i think Jamies blushing! (kiddin') hehe