Re: sub queries
Nathan Coast wrote: Hi apologies if this is a dumb question but can you do subqueries in mysql? select count(*) as RES from ACL_USER_GROUP_ROLE as UGR where UGR.USER_ID =2 and UGR.ROLE_ID = (select ROLE_ID from ACL_ROLE where ROLE_NAME = 'projectmanager' ) this query fails, but the individual queries work fine select count(*) as RES from ACL_USER_GROUP_ROLE as UGR where UGR.USER_ID =2 and UGR.ROLE_ID = 3 and select ROLE_ID from ACL_ROLE where ROLE_NAME = 'projectmanager' cheers Nathan You need MySQl 4.1.x to do subqueries. HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sub queries
Hi Nathan, Subqueries are only available in MySQL as of version 4.1. On Fri, 29 Oct 2004, Nathan Coast wrote: Hi apologies if this is a dumb question but can you do subqueries in mysql? select count(*) as RES from ACL_USER_GROUP_ROLE as UGR where UGR.USER_ID =2 and UGR.ROLE_ID = (select ROLE_ID from ACL_ROLE where ROLE_NAME = 'projectmanager' ) this query fails, but the individual queries work fine select count(*) as RES from ACL_USER_GROUP_ROLE as UGR where UGR.USER_ID =2 and UGR.ROLE_ID = 3 and select ROLE_ID from ACL_ROLE where ROLE_NAME = 'projectmanager' cheers Nathan -- Nathan Coast Managing Director codeczar ltd mobile: (852) 9049 5581 email: mailto:[EMAIL PROTECTED] web:http://www.codeczar.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sub queries
- Original Message - From: electroteque [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 28, 2004 6:33 PM Subject: Sub queries Hi there, I have Mysql 4.1 on my development machine, I have been trying to test out if I am going to be able to do this. What I would like to do is return a one to many resultset but without the duplicated results in the first query. Hows is this going to be possible ? I would like to get all the records out of the second table from a key from the first table. I got this using 4.1.5 mysql select * from shotlist s limit 1 union select * from sources ss where ss.sourceID IN (select sourceID from shotlist s limit 1); ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' The keyword UNION should ensure that all duplicate rows are removed from the combined result set. If you use UNION ALL instead of UNION, the duplicates are left in the result set. I think the reason for the error message you are getting is that you have the 'limit 1' clause in the query twice, once in each select. You *may* be allowed to have the 'limit' clause in the subquery of the second SELECT although I doubt it, based on the text of the error message. More likely, you have to remove the 'limit' clause from the subquery. I think the only other place you can have it is after the last SELECT that is UNIONed together. Something like this: select * from shotlist s union select * from sources ss where ss.sourceID IN (select sourceID from shotlist s limit 1) limit 1; The final 'limit' clause affects the final result set, which is a combination of the result sets from both queries. Unfortunately, I don't have a 4.1.x system to try this on so I am strictly guessing based on my work with DB2. I have also notice union joins add one resultset after the other, how do you mix this in, or add that resultset as a column in the row instead of one after the other ? Also I am trying to push for 4.1 to be installed on the servers i build web apps on. When will be a possible date to say that gamma which is practically production quality, to actually say production quality ? heheh. Our systems guy will only trust it if it says that, god only knows that latest versions are always feature rich and bug fixed darn. I have no idea about this but others will probably have an idea when we can expect gamma code. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sub queries
electroteque wrote: Also I am trying to push for 4.1 to be installed on the servers i build web apps on. When will be a possible date to say that gamma which is practically production quality, to actually say production quality ? heheh. Our systems guy will only trust it if it says that, god only knows that latest versions are always feature rich and bug fixed darn. It's already production ready, apparently: http://www.mysql.com/news-and-events/press-release/release_2004_32.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sub queries
The keyword UNION should ensure that all duplicate rows are removed from the combined result set. If you use UNION ALL instead of UNION, the duplicates are left in the result set. Yeh right, funny, early versions of 4.0, UNION had in the docs this was only avail in 4.1, i am so sure i read that, and i'm pretty sure I tried union and didnt work and then I go back and it said in the docs it was avail in 4.0 + hehe. I think the reason for the error message you are getting is that you have the 'limit 1' clause in the query twice, once in each select. You *may* be allowed to have the 'limit' clause in the subquery of the second SELECT although I doubt it, based on the text of the error message. More likely, you have to remove the 'limit' clause from the subquery. I think the only other place you can have it is after the last SELECT that is UNIONed together. Something like this: select * from shotlist s union select * from sources ss where ss.sourceID IN (select sourceID from shotlist s limit 1) limit 1; The final 'limit' clause affects the final result set, which is a combination of the result sets from both queries. Ok what I was more after was select * from shotlist s union select * from sources ss where ss.sourceID IN (s.sourceID) so it gets the results of sourceID from the first table, it doesnt seem to like that. I want to prevent programatically having to do a second query and loop within the script hehehe. Unfortunately, I don't have a 4.1.x system to try this on so I am strictly guessing based on my work with DB2. Heh I currently have a DB2 jobby soon, doing jasper reports out of a system that uses db2, whats the syntax like, is it a pain ? I have no idea about this but others will probably have an idea when we can expect gamma code. I dont think this made it to the list from Mat Scales http://www.mysql.com/news-and-events/press-release/release_2004_32.html yippy. Well my binary of 4.1.7 didnt say gamma so ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sub queries
- Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, October 28, 2004 9:26 PM Subject: Re: Sub queries The keyword UNION should ensure that all duplicate rows are removed from the combined result set. If you use UNION ALL instead of UNION, the duplicates are left in the result set. Yeh right, funny, early versions of 4.0, UNION had in the docs this was only avail in 4.1, i am so sure i read that, and i'm pretty sure I tried union and didnt work and then I go back and it said in the docs it was avail in 4.0 + hehe. I think the reason for the error message you are getting is that you have the 'limit 1' clause in the query twice, once in each select. You *may* be allowed to have the 'limit' clause in the subquery of the second SELECT although I doubt it, based on the text of the error message. More likely, you have to remove the 'limit' clause from the subquery. I think the only other place you can have it is after the last SELECT that is UNIONed together. Something like this: select * from shotlist s union select * from sources ss where ss.sourceID IN (select sourceID from shotlist s limit 1) limit 1; The final 'limit' clause affects the final result set, which is a combination of the result sets from both queries. Ok what I was more after was select * from shotlist s union select * from sources ss where ss.sourceID IN (s.sourceID) so it gets the results of sourceID from the first table, it doesnt seem to like that. I want to prevent programatically having to do a second query and loop within the script hehehe. There are obviously many possible variations of your query; I just stated one that was pretty close to your original query. It's really not clear to me yet what you were trying to do so I just wanted to show you typical syntax. Unfortunately, I don't have a 4.1.x system to try this on so I am strictly guessing based on my work with DB2. Heh I currently have a DB2 jobby soon, doing jasper reports out of a system that uses db2, whats the syntax like, is it a pain ? It really depends on what you already know. I think DB2 is pretty easy to use but 've been using DB2 for 20 years; I don't know what you will think, because I don't know anything about you. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sub queries
There are obviously many possible variations of your query; I just stated one that was pretty close to your original query. It's really not clear to me yet what you were trying to do so I just wanted to show you typical syntax. Ok sorry ppl I should have given a typical example in my script. qsuedocode : select * from shotlist while row in result select * from sources where sourceID IN (row[sourceID]) while row in result append sources to string here end while output results to template row end while so it would be in a datagrid Title Sources my title heresource1, source2 etc ... If i were to do that in a normal join the rows would duplicate from a 1 to many. It really depends on what you already know. I think DB2 is pretty easy to use but 've been using DB2 for 20 years; I don't know what you will think, because I don't know anything about you. woah 20 years ? you are an SQL veteran then :) I'm still a green grasshopper of 5 years. Ok i'msure you dont know anything about me, and has made me suggest to everyone of a listee profilespage heheheh. Little bit about me, I am a PHP/Mysql/Java/Unix/Flash Actionscript 2 developer fora Tv station in Sydney building business level web applications. Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sub Queries in MYSQL and JOINS
James Raff wrote: I have MYSQL 3.23.32 on a Cobalt 550 platform. I see from the FAQ's that sub That's a *very* old version. The latest 3.23 is 3.23.58. The current production release is 4.0.20. You should consider upgrading. queries will not work on MYSQL 4.1. Is there a way to use JOIN statements instead or do these fail too. Did you try? JOINs certainly do work in 3.23. The manual offers some suggestions http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html. eg: SELECT some_ID from someTable NOT IN (Select some_ID from ANOTHERTABLE) SELECT some_ID FROM someTable s LEFT JOIN ANOTHERTABLE a ON s.some_ID = a.some_ID WHERE a.some_ID IS NULL; Or do I have to redesign my website! Any help appreciated Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sub queries in mysql?
* Jasper Bijl
Is there a way to do something like subqueries in one query?
Yes, JOIN can be used in many cases where you would think you need
sub-queries. A JOIN is also generally faster, according to:
URL: http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html
I have a customer table (Klant) with contacts (KlantKontakt) and a
address (Adres) table.
The address table keeps a record of each different address for a
customer including old addresses (to maintain history).
ok... and there is a date column or similar in the Adres table, to keep
track of which address is the last, I suppose. Let's call it 'FromDate'.
You could add a column in Adres:
state enum('active','inactive')
...and update the previous active address and set it to 'inactive' when you
insert a new one, but that would be redundant, because the latest always is
the active, right?
If I want to retrieve a list of customers with their newest address, I
have to do a max() to retreive the last address.
You should take a look at this:
URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html
The MAX-CONCAT trick can maybe solve your prolem?
I can do this in PHP in a seperate query but I like to do it in one
query on the mysql prompt.
Below is my (wrong) query:
SELECT
Klant.klantcode, Klant.naam,
KlantKontakt.persooncode, KlantKontakt.Achternaam,
Adres.straat, Adres.postcode, Adres.plaats
FROM Klant, KlantKontakt, Adres
WHERE SoortKlant = 'Prospect'
AND Klant.klantcode = KlantKontakt.klantcode
AND Adres.klantcode = Klant.klantcode
ORDER BY Klant.Naam
Is there any way to do this in MySQL in one query?
It's hard to see what's wrong with the query above... except, of course, it
does not select the latest address. :)
There is another approach (in addition to subqueries if you use 4.1, or
temporary tables, or the MAX-CONCAT trick). You could try using a self join.
The date column in the Adres table is called 'FromDate', at least in my
mind. :)
The same query as above, but with an additional join on the address:
SELECT
Klant.klantcode, Klant.naam,
KlantKontakt.persooncode, KlantKontakt.Achternaam,
Adres.straat, Adres.postcode, Adres.plaats
FROM Klant, KlantKontakt, Adres
LEFT JOIN Adres AS A2 ON
A2.klantcode = Klant.klantcode AND
A2.FromDate Adres.FromDate
WHERE SoortKlant = 'Prospect'
AND Klant.klantcode = KlantKontakt.klantcode
AND Adres.klantcode = Klant.klantcode
AND A2.klantcode IS NULL
ORDER BY Klant.Naam
We join any later address, if it's found we do _not_ want the row in our
result. This is achieved by testing if A2.klantcode IS NULL.
Warning: For each customer, the server will do a lookup on all later
addresses for each address... this will be very slow when you have very many
addresses for each customer. In your case, I would guess you rarly have more
than 10-15 address rows per customer, so you should be safe. If my
assumptions are wrong, and you have, say, 1000 address rows per customer,
the self join approach will probably be too slow. The server would have to
read 500.000 Adres rows for each customer (the first Adres would join to the
999 later, the second Adres would join to the 998 later, and so on).
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sub queries in mysql?
Roger,
Thank you for the explanation. I don't have Mysql 4.1 here, so I will
see if I can install this new version. But I will try the self join
suggestion to solve this thing.
Thank you very much for the quick response!
Jasper
-Original Message-
From: Roger Baklund [mailto:[EMAIL PROTECTED]
Sent: woensdag 13 augustus 2003 13:40
To: [EMAIL PROTECTED]
Cc: Jasper Bijl
Subject: Re: sub queries in mysql?
* Jasper Bijl
Is there a way to do something like subqueries in one query?
Yes, JOIN can be used in many cases where you would think you
need sub-queries. A JOIN is also generally faster, according to:
URL: http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html
I have a customer table (Klant) with contacts (KlantKontakt) and a
address (Adres) table. The address table keeps a record of each
different address for a customer including old addresses
(to maintain
history).
ok... and there is a date column or similar in the Adres
table, to keep track of which address is the last, I suppose.
Let's call it 'FromDate'.
You could add a column in Adres:
state enum('active','inactive')
...and update the previous active address and set it to
'inactive' when you insert a new one, but that would be
redundant, because the latest always is the active, right?
If I want to retrieve a list of customers with their newest
address, I
have to do a max() to retreive the last address.
You should take a look at this:
URL:
http://www.mysql.com/doc/en/example-Maximum-column-group-row.html
The MAX-CONCAT trick can maybe solve your prolem?
I can do this in PHP in a seperate query but I like to do it in one
query on the mysql prompt.
Below is my (wrong) query:
SELECT
Klant.klantcode, Klant.naam,
KlantKontakt.persooncode, KlantKontakt.Achternaam, Adres.straat,
Adres.postcode, Adres.plaats
FROM Klant, KlantKontakt, Adres
WHERE SoortKlant = 'Prospect'
AND Klant.klantcode = KlantKontakt.klantcode
AND Adres.klantcode = Klant.klantcode
ORDER BY Klant.Naam
Is there any way to do this in MySQL in one query?
It's hard to see what's wrong with the query above... except,
of course, it does not select the latest address. :)
There is another approach (in addition to subqueries if you
use 4.1, or temporary tables, or the MAX-CONCAT trick). You
could try using a self join. The date column in the Adres
table is called 'FromDate', at least in my mind. :)
The same query as above, but with an additional join on the address:
SELECT
Klant.klantcode, Klant.naam,
KlantKontakt.persooncode, KlantKontakt.Achternaam,
Adres.straat, Adres.postcode, Adres.plaats
FROM Klant, KlantKontakt, Adres
LEFT JOIN Adres AS A2 ON
A2.klantcode = Klant.klantcode AND
A2.FromDate Adres.FromDate
WHERE SoortKlant = 'Prospect'
AND Klant.klantcode = KlantKontakt.klantcode
AND Adres.klantcode = Klant.klantcode
AND A2.klantcode IS NULL
ORDER BY Klant.Naam
We join any later address, if it's found we do _not_ want the
row in our result. This is achieved by testing if
A2.klantcode IS NULL.
Warning: For each customer, the server will do a lookup on
all later addresses for each address... this will be very
slow when you have very many addresses for each customer. In
your case, I would guess you rarly have more than 10-15
address rows per customer, so you should be safe. If my
assumptions are wrong, and you have, say, 1000 address rows
per customer, the self join approach will probably be too
slow. The server would have to read 500.000 Adres rows for
each customer (the first Adres would join to the 999 later,
the second Adres would join to the 998 later, and so on).
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sub-queries
Hello. On Fri 2002-06-07 at 05:15:51 +0200, [EMAIL PROTECTED] wrote: [...] No, it's probably not difficult at all. It simply seems as if people do not understand exactly what you want. A communication problem, IMHO. Kevin was also kind enough to point this out to me, and I have to agree with both of you. As I did explain to Kevin in private however, I do have very Never mind. I was just trying to explain why nobody came up with the right solution at once. If anyone feel like taking 10 minutes of their time, and explaining Well, probably more than 10 minutes. ;-) exactly what the query does, I'll appreciate it allot. It may help me understand the basic logic behind the structure of the query, and aid me in the future when I may need to execute such queries again (although, I'm honestly hoping that when such a time comes, MySQL will support sub-queries). Okay. Let's see the query again, a bit reformatted: SELECT mh.HostID, mh.HostDescription FROM monitorhosts mh LEFT JOIN monitorhostgroupdetails mhgd ON mh.HostID = mhgd.HostID AND mhgd.HostGroupID = 2 WHERE mhgd.HostID IS NULL AND mh.CompanyID = 1; First, I assume that it is known that a normal JOIN (written with ',') builds a cross product of the two tables, i.e. build pairs of each record from the first table with each record of the second table. Then, you normally have something like WHERE mh.HostID = mhgd.HostID which only chooses those pairs, which have matching HostIDs. A LEFT JOIN does the same, but for all records of the left (=first) table, which have no match in the right table, it will insert NULL for the right table values. I.e. if you have (from above) LEFT JOIN ... ON mh.HostID = mhgd.HostID you will get the result from a normal JOIN (all pairs for which mh.HostID = mhgd.HostID is true) and all remaining records from monitorhosts (all for whose HostID was no record in monitorhostgroupdetails) paired with NULL values for the columns of monitorhostgroupdetails. Another way to see this is to take all records from the left table and pair them with either the matching records from the right table or with NULL values if record matched. An additional mhgd.HostGroupID = 2 in the ON clause will only consider a pair valid, if mhgd.HostGroupID = 2 (as in a normal join) and for all non-fitted records of the left table it pairs them with NULL values again. This means, we now get NULL values for all (former) pairs which have mhgd.HostGroupID != 2. Now the WHERE clause can be applied. mhgd.HostID IS NULL now chooses all records, which have NULL values for the right table, i.e. all pairs, which had no match on the condition mh.HostID = mhgd.HostID AND mhgd.HostGroupID = 2, this means all records of the left table, for which there was no matching HostID in mhgd which also was in mhgd.HostGroupID = 2. The latter is a different wording for ... mh.HostID NOT IN ( SELECT mhgd.HostID FROM monitorhostgroupdetails mhgd WHERE mhgd.HostGroupID = 2 ) Which should look familiar to you. ;-) mh.CompanyID = 1 restricts the result to only the company in question, of course. Of course, the RDBMS (here MySQL) optimizes how it retrieves the pairs you want. But the above is the underlying logic of how it works. Hope that helped, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sub-queries
I'm sorry to disappoint you, but subqueries are NOT supported int any MySQL version, yet. You can read the MySQL manual about it. You can find there when and how it will be implemented. Bye Daniel At 21:39 2002.06.06. +0200, you wrote: Lo all, are sub-queries supported on mysql-max 3.23.49 ?? If they are, what's wrong with the following statement? SELECT monitorhosts.HostID FROM monitorhosts WHERE monitorhosts.HostID NOT IN (SELECT HostID FROM monitorhostgroupdetails WHERE monitorhostgroupdetails.HostGroupID='1'); mysql complains about a syntax error right at the begining of the second select... ERROR 1064: You have an error in your SQL syntax near 'SELECT HostID FROM monitorhostgroupdetails WHERE monitorh' at line 4 ty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sub-queries
Fair enough (and also what I thought) Does anyone have any idea how I can implement the below in a similar fashion then? I have a list of items, and a list of groups. I want to retrieve all the items from a table that is not in a specific group... - Original Message - From: Kiss Dániel [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 9:47 PM Subject: Re: sub-queries I'm sorry to disappoint you, but subqueries are NOT supported int any MySQL version, yet. You can read the MySQL manual about it. You can find there when and how it will be implemented. Bye Daniel At 21:39 2002.06.06. +0200, you wrote: Lo all, are sub-queries supported on mysql-max 3.23.49 ?? If they are, what's wrong with the following statement? SELECT monitorhosts.HostID FROM monitorhosts WHERE monitorhosts.HostID NOT IN (SELECT HostID FROM monitorhostgroupdetails WHERE monitorhostgroupdetails.HostGroupID='1'); mysql complains about a syntax error right at the begining of the second select... ERROR 1064: You have an error in your SQL syntax near 'SELECT HostID FROM monitorhostgroupdetails WHERE monitorh' at line 4 ty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sub-queries
SELECT monitorhosts.HostID FROM monitorhosts, monitorhostgroupdetails WHERE monitorhosts.HostID = monitorhostgroupdetails.HostID AND monitorhostgroupdetails.HostGroupID != '1'); Gruss Sabine Chris Knipe wrote: Lo all, are sub-queries supported on mysql-max 3.23.49 ?? If they are, what's wrong with the following statement? SELECT monitorhosts.HostID FROM monitorhosts WHERE monitorhosts.HostID NOT IN (SELECT HostID FROM monitorhostgroupdetails WHERE monitorhostgroupdetails.HostGroupID='1'); mysql complains about a syntax error right at the begining of the second select... ERROR 1064: You have an error in your SQL syntax near 'SELECT HostID FROM monitorhostgroupdetails WHERE monitorh' at line 4 ty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: sub-queries
Select monitorhosts.HostID from monitorhosts left join monitorhostgroupdetails on monitorhostgroupdetails.HostID = monitorhosts.HostID where monitorhostgroupdetails.HostGroupID !='1'; * * Cal Evans * Journeyman Programmer * Techno-Mage * http://www.calevans.com * -Original Message- From: Chris Knipe [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 2:52 PM To: [EMAIL PROTECTED] Subject: Re: sub-queries Fair enough (and also what I thought) Does anyone have any idea how I can implement the below in a similar fashion then? I have a list of items, and a list of groups. I want to retrieve all the items from a table that is not in a specific group... - Original Message - From: Kiss Dániel [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 9:47 PM Subject: Re: sub-queries I'm sorry to disappoint you, but subqueries are NOT supported int any MySQL version, yet. You can read the MySQL manual about it. You can find there when and how it will be implemented. Bye Daniel At 21:39 2002.06.06. +0200, you wrote: Lo all, are sub-queries supported on mysql-max 3.23.49 ?? If they are, what's wrong with the following statement? SELECT monitorhosts.HostID FROM monitorhosts WHERE monitorhosts.HostID NOT IN (SELECT HostID FROM monitorhostgroupdetails WHERE monitorhostgroupdetails.HostGroupID='1'); mysql complains about a syntax error right at the begining of the second select... ERROR 1064: You have an error in your SQL syntax near 'SELECT HostID FROM monitorhostgroupdetails WHERE monitorh' at line 4 ty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sub-queries
*MWAH*!!! Thanks a million, tested and working beautifully Can't believe in two days I didn't think of this... -- me - Original Message - From: Sabine Richter [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 10:14 PM Subject: Re: sub-queries SELECT monitorhosts.HostID FROM monitorhosts, monitorhostgroupdetails WHERE monitorhosts.HostID = monitorhostgroupdetails.HostID AND monitorhostgroupdetails.HostGroupID != '1'); Gruss Sabine Chris Knipe wrote: Lo all, are sub-queries supported on mysql-max 3.23.49 ?? If they are, what's wrong with the following statement? SELECT monitorhosts.HostID FROM monitorhosts WHERE monitorhosts.HostID NOT IN (SELECT HostID FROM monitorhostgroupdetails WHERE monitorhostgroupdetails.HostGroupID='1'); mysql complains about a syntax error right at the begining of the second select... ERROR 1064: You have an error in your SQL syntax near 'SELECT HostID FROM monitorhostgroupdetails WHERE monitorh' at line 4 ty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sub-queries
Ok, I spoke to soon... And this is starting to drive me up the walls now... i.e. getting REALLY irritating and frustrating. mysql DESCRIBE monitorhosts; +--+--+--+-+-+-- --+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+-- --+ | HostID | tinyint(4) | | PRI | NULL| auto_increment | | CompanyID| tinyint(4) | | MUL | 0 | | +--+--+--+-+-+-- --+ 2 rows in set (0.29 sec) mysql DESCRIBE monitorhostgroupdetails; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | HostGroupDetailID | mediumint(9) | | PRI | NULL| auto_increment | | HostGroupID | mediumint(9) | | MUL | 0 || | HostID| mediumint(9) | | MUL | 0 || +---+--+--+-+-++ 3 rows in set (0.02 sec) mysql Now, here's the catch The HostID field from monitorhosts, is a itemID for a server entry. This server entry is unique, can be in one, multiple or even in no group AT ALL. monitorhostgroupdetails maps a hostID to a groupID, and consist of a unique ID (i.e. only one unique hostID is allowed in one GroupID) All the statements I got so far, list only the hostID if they are in a group... Not if they aren't in a group AT ALL. monitorhosts table data: | 1 | 1 | | 7 | 1 | From this, I can see that I have HostID 1 7, assigned to CompanyID 1. monitorhostgroupdetails table data: |51 | 1 | 1 | Here, I can see HostID 1 belongs to HostGroup 1. The results on all the various queries I tried and that's been suggested... Select monitorhosts.HostID from monitorhosts left join monitorhostgroupdetails on monitorhostgroupdetails.HostID = monitorhosts.HostID where monitorhostgroupdetails.HostGroupID !='1'; This returns no data from the monitorhosts table, even through we have HostID which isn't in a group. When HostGroupID is changed to 2, HostID 1 is returned (because it is in the hostgroup table), but hostid 7 which isn't in a group, is still left out of the equasion. SELECT monitorhosts.HostID, monitorhosts.HostDescription FROM monitorhosts, monitorhostgroupdetails WHERE monitorhosts.HostID = monitorhostgroupdetails.HostID AND monitorhostgroupdetails.HostGroupID != '1' AND monitorhosts.CompanyID='1'; Nothing is returned. My gut tells me the same happens as with the previous query. In otherwords, the queries will *only* return any hostID data, if the host is in the monitorhostgroupdetails table, which, isn't going to be right. A host is registered in the database, and only certain hosts, under certain curcumstances is grouped. Basically, what I want to do now, is that when a client modifies the servers assigned in a group, I only want to list the servers which is NOT allready in that specific group. Is this possible, or am I really going to have to use PHP arrays and compare arrays with hundreds of thousands of values in them?? *deep sigh* - Original Message - From: Chris Knipe [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 10:16 PM Subject: Re: sub-queries *MWAH*!!! Thanks a million, tested and working beautifully Can't believe in two days I didn't think of this... -- me - Original Message - From: Sabine Richter [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 10:14 PM Subject: Re: sub-queries SELECT monitorhosts.HostID FROM monitorhosts, monitorhostgroupdetails WHERE monitorhosts.HostID = monitorhostgroupdetails.HostID AND monitorhostgroupdetails.HostGroupID != '1'); Gruss Sabine Chris Knipe wrote: Lo all, are sub-queries supported on mysql-max 3.23.49 ?? If they are, what's wrong with the following statement? SELECT monitorhosts.HostID FROM monitorhosts WHERE monitorhosts.HostID NOT IN (SELECT HostID FROM monitorhostgroupdetails WHERE monitorhostgroupdetails.HostGroupID='1'); mysql complains about a syntax error right at the begining of the second select... ERROR 1064: You have an error in your SQL syntax near 'SELECT HostID FROM monitorhostgroupdetails WHERE monitorh' at line 4 ty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail
RE: sub-queries
Chris, sounds like you're looking for an exclusive left outer join. You want to see records from monitorhosts, where there is no corresponding record in monitorhostgroupdetails, right? Use: Select * from monitorhosts left join monitorhostgroupdetails on monitorhosts.HostID = monitorhostgroupdetails.HostID where monitorhostgroupdetails.HostID is null ; ++---+---+-++ | hostID | CompanyID | HostGroupDetailID | HostGroupID | HostID | ++---+---+-++ | 7 | 1 | NULL |NULL | NULL | ++---+---+-++ The trick is the is null which trims the result set to only show those join-results which could find no right-hand record to join to. hth, Kevin -Original Message- From: Chris Knipe [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 1:52 PM To: [EMAIL PROTECTED] Subject: Re: sub-queries Ok, I spoke to soon... And this is starting to drive me up the walls now... i.e. getting REALLY irritating and frustrating. mysql DESCRIBE monitorhosts; +--+--+--+-+-+ -- --+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+ -- --+ | HostID | tinyint(4) | | PRI | NULL| auto_increment | | CompanyID| tinyint(4) | | MUL | 0 | | +--+--+--+-+-+ -- --+ 2 rows in set (0.29 sec) mysql DESCRIBE monitorhostgroupdetails; +---+--+--+-+-+--- -+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+--- -+ | HostGroupDetailID | mediumint(9) | | PRI | NULL| auto_increment | | HostGroupID | mediumint(9) | | MUL | 0 | | | HostID| mediumint(9) | | MUL | 0 | | +---+--+--+-+-+--- -+ 3 rows in set (0.02 sec) mysql Now, here's the catch The HostID field from monitorhosts, is a itemID for a server entry. This server entry is unique, can be in one, multiple or even in no group AT ALL. monitorhostgroupdetails maps a hostID to a groupID, and consist of a unique ID (i.e. only one unique hostID is allowed in one GroupID) All the statements I got so far, list only the hostID if they are in a group... Not if they aren't in a group AT ALL. monitorhosts table data: | 1 | 1 | | 7 | 1 | From this, I can see that I have HostID 1 7, assigned to CompanyID 1. monitorhostgroupdetails table data: |51 | 1 | 1 | Here, I can see HostID 1 belongs to HostGroup 1. The results on all the various queries I tried and that's been suggested... Select monitorhosts.HostID from monitorhosts left join monitorhostgroupdetails on monitorhostgroupdetails.HostID = monitorhosts.HostID where monitorhostgroupdetails.HostGroupID !='1'; This returns no data from the monitorhosts table, even through we have HostID which isn't in a group. When HostGroupID is changed to 2, HostID 1 is returned (because it is in the hostgroup table), but hostid 7 which isn't in a group, is still left out of the equasion. SELECT monitorhosts.HostID, monitorhosts.HostDescription FROM monitorhosts, monitorhostgroupdetails WHERE monitorhosts.HostID = monitorhostgroupdetails.HostID AND monitorhostgroupdetails.HostGroupID != '1' AND monitorhosts.CompanyID='1'; Nothing is returned. My gut tells me the same happens as with the previous query. In otherwords, the queries will *only* return any hostID data, if the host is in the monitorhostgroupdetails table, which, isn't going to be right. A host is registered in the database, and only certain hosts, under certain curcumstances is grouped. Basically, what I want to do now, is that when a client modifies the servers assigned in a group, I only want to list the servers which is NOT allready in that specific group. Is this possible, or am I really going to have to use PHP arrays and compare arrays with hundreds of thousands of values in them?? *deep sigh* - Original Message - From: Chris Knipe [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 10:16 PM Subject: Re: sub-queries *MWAH*!!! Thanks a million, tested and working beautifully Can't believe in two days I didn't think of this... -- me - Original Message - From: Sabine Richter [EMAIL PROTECTED] To: Chris Knipe [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, June 06, 2002 10:14 PM Subject: Re: sub
Re: sub-queries
- Original Message - From: Kevin Fries [EMAIL PROTECTED] To: 'Chris Knipe' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, June 07, 2002 12:38 AM Subject: RE: sub-queries Chris, sounds like you're looking for an exclusive left outer join. You want to see records from monitorhosts, where there is no corresponding record in monitorhostgroupdetails, right? Yes - and no. If a HostID 1, assigned to GroupID 1, 3, and 4, then I want to issue a query where I specify the GroupID to be 2, and HostID 1 must be returned. All the join statements I got so far, will only include the hostID if it is not in monitorhostgroupdetails AT ALL. Use: Select * from monitorhosts left join monitorhostgroupdetails on monitorhosts.HostID = monitorhostgroupdetails.HostID where monitorhostgroupdetails.HostID is null 1) I can't specify which GroupID I want to exclude, and 2) I can't limit the hostID on a CompanyID basis (only show hosts belonging to a certain company). 3) This now, shows all the hosts that is not in a group at all. I need to specify which hostID, is NOT IN a GroupID. I can't believe that this is so difficult ?!?!?!?!? Alternative measures Can this be done in two or three different queries?? I really need to find a solution for this... :-(( ++---+---+-++ | hostID | CompanyID | HostGroupDetailID | HostGroupID | HostID | ++---+---+-++ | 7 | 1 | NULL |NULL | NULL | ++---+---+-++ The trick is the is null which trims the result set to only show those join-results which could find no right-hand record to join to. hth, Kevin -Original Message- From: Chris Knipe [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 1:52 PM To: [EMAIL PROTECTED] Subject: Re: sub-queries Ok, I spoke to soon... And this is starting to drive me up the walls now... i.e. getting REALLY irritating and frustrating. mysql DESCRIBE monitorhosts; +--+--+--+-+-+ -- --+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+ -- --+ | HostID | tinyint(4) | | PRI | NULL| auto_increment | | CompanyID| tinyint(4) | | MUL | 0 | | +--+--+--+-+-+ -- --+ 2 rows in set (0.29 sec) mysql DESCRIBE monitorhostgroupdetails; +---+--+--+-+-+--- -+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+--- -+ | HostGroupDetailID | mediumint(9) | | PRI | NULL| auto_increment | | HostGroupID | mediumint(9) | | MUL | 0 | | | HostID| mediumint(9) | | MUL | 0 | | +---+--+--+-+-+--- -+ 3 rows in set (0.02 sec) mysql Now, here's the catch The HostID field from monitorhosts, is a itemID for a server entry. This server entry is unique, can be in one, multiple or even in no group AT ALL. monitorhostgroupdetails maps a hostID to a groupID, and consist of a unique ID (i.e. only one unique hostID is allowed in one GroupID) All the statements I got so far, list only the hostID if they are in a group... Not if they aren't in a group AT ALL. monitorhosts table data: | 1 | 1 | | 7 | 1 | From this, I can see that I have HostID 1 7, assigned to CompanyID 1. monitorhostgroupdetails table data: |51 | 1 | 1 | Here, I can see HostID 1 belongs to HostGroup 1. The results on all the various queries I tried and that's been suggested... Select monitorhosts.HostID from monitorhosts left join monitorhostgroupdetails on monitorhostgroupdetails.HostID = monitorhosts.HostID where monitorhostgroupdetails.HostGroupID !='1'; This returns no data from the monitorhosts table, even through we have HostID which isn't in a group. When HostGroupID is changed to 2, HostID 1 is returned (because it is in the hostgroup table), but hostid 7 which isn't in a group, is still left out of the equasion. SELECT monitorhosts.HostID, monitorhosts.HostDescription FROM monitorhosts, monitorhostgroupdetails WHERE monitorhosts.HostID = monitorhostgroupdetails.HostID AND monitorhostgroupdetails.HostGroupID != '1' AND monitorhosts.CompanyID='1'; Nothing is returned. My gut tells me the same happens as with the previous query. In otherwords, the queries will *only* return any
Re: sub-queries
Hi. On Fri 2002-06-07 at 01:55:18 +0200, [EMAIL PROTECTED] wrote: [...] Chris, sounds like you're looking for an exclusive left outer join. You want to see records from monitorhosts, where there is no corresponding record in monitorhostgroupdetails, right? Yes - and no. If a HostID 1, assigned to GroupID 1, 3, and 4, then I want to issue a query where I specify the GroupID to be 2, and HostID 1 must be returned. All the join statements I got so far, will only include the hostID if it is not in monitorhostgroupdetails AT ALL. Use: Select * from monitorhosts left join monitorhostgroupdetails on monitorhosts.HostID = monitorhostgroupdetails.HostID where monitorhostgroupdetails.HostID is null 1) I can't specify which GroupID I want to exclude, and I presume you refer to monitorhostgroupdetails.HostGroupID='1'? ON ... AND monitorhostgroupdetails.HostGroupID='1' WHERE 2) I can't limit the hostID on a CompanyID basis (only show hosts belonging to a certain company). WHERE ... AND monitorhosts.CompanyID = 5 3) This now, shows all the hosts that is not in a group at all. I need to specify which hostID, is NOT IN a GroupID. Isn't this only a variation of 1) ? I can't believe that this is so difficult ?!?!?!?!? No, it's probably not difficult at all. It simply seems as if people do not understand exactly what you want. A communication problem, IMHO. You already got some quite good pointer where to look at (LEFT JOIN, IS NULL, ...) and probably you just have to do some digging to figure out the correct query. Or else, write a more complete example (some sample data + result in a table form), which shows what you want. This prevents most ambiguities. The examples you provided earlier were hard to read (e.g. no column names) and provided to few rows to be unambigous. Or provide a sub-select that would do what you want. Just for completeness. The sub-query from your first post: SELECT monitorhosts.HostID FROM monitorhosts WHERE monitorhosts.HostID NOT IN ( SELECT HostID FROM monitorhostgroupdetails WHERE monitorhostgroupdetails.HostGroupID='1' ) is equivalent to SELECT mh.HostID FROM monitorhosts LEFT JOIN monitorhostsgroupdetails mhgd ON mhgd.HostID = mh.HostID AND mhgd.HostGroupID = 1 WHERE mhgd.HostID IS NULL (only adding your 1) to Kevin's query) Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sub-queries
All sorted, thanks a million to Kevin Fries For archives purpose and anyone else that ever want to do anything like this... The query I was looking for apparently (Tested and working so far - I will test it a bit more later when I have more data in the tables): select monitorhosts.HostID, monitorhosts.HostDescription from monitorhosts left join monitorhostgroupdetails on monitorhosts.HostID = monitorhostgroupdetails.HostID AND monitorhostgroupdetails.HostGroupID = 2 where monitorhostgroupdetails.HostID is null AND monitorhosts.CompanyID='1'; Once again, my utmost thanks to Kevin for the solution This officially closes three days of headaches and sleepless nights... :-) No, it's probably not difficult at all. It simply seems as if people do not understand exactly what you want. A communication problem, IMHO. Kevin was also kind enough to point this out to me, and I have to agree with both of you. As I did explain to Kevin in private however, I do have very valid reasons for trying to expose as little as possible over the structures of the tables and the data that they contain. Perhaps a little greedy of me seeing that I am the one seeking the help here, but it paid off at long last. I'll try be more specific in future should any *serious* like problem come up in the future again. You already got some quite good pointer where to look at (LEFT JOIN, IS NULL, ...) and probably you just have to do some digging to figure out the correct query. I also have to admit... It was pointed out to me about two days ago that I would need to execute this query with a LEFT JOIN. Coming from a MSSQL background, and being used to simply using sub-queries, I did go and read the section in the manual about LEFT / RIGHT JOIN statements. After reading about three times a day, and still now after getting a query that works, I have to sadly admit, I understand absolutely 0% as to *why* this is working, or how it is working. At this stage, all that is importaint to me is that I got a solution to my direct problem - which is what I wanted. If anyone feel like taking 10 minutes of their time, and explaining exactly what the query does, I'll appreciate it allot. It may help me understand the basic logic behind the structure of the query, and aid me in the future when I may need to execute such queries again (although, I'm honestly hoping that when such a time comes, MySQL will support sub-queries). Or else, write a more complete example (some sample data + result in a table form), which shows what you want. This prevents most ambiguities. The examples you provided earlier were hard to read (e.g. no column names) and provided to few rows to be unambigous. Or provide a sub-select that would do what you want. Yes, and I'll admit that as a fault on my side. Table structures and some data was removed from the tables due to security concirns on my side. The data and structures removed was not relavent to the specific query however (tables / columns against which the query should be made), but I'll have to agree it did add to the confusion. Just for completeness. The sub-query from your first post: SELECT monitorhosts.HostID FROM monitorhosts WHERE monitorhosts.HostID NOT IN ( SELECT HostID FROM monitorhostgroupdetails WHERE monitorhostgroupdetails.HostGroupID='1' ) is equivalent to SELECT mh.HostID FROM monitorhosts LEFT JOIN monitorhostsgroupdetails mhgd ON mhgd.HostID = mh.HostID AND mhgd.HostGroupID = 1 WHERE mhgd.HostID IS NULL (only adding your 1) to Kevin's query) Yes, I see and understand that now as well. The original sub-query posted by me was incorrect. The query was originally posted to me via another member of the list, and seeing MySQL doesn't support sub-queries at all (I thought 3.23.49 might have had support), I was never able to test the query to confirm if it is working or not. However, a solution has been provided, it is working, and I am happy. Thanks to EVERYONE on here, as well as on the PHP-General mailing list for helping and trying to sort this out for me. I really do appreciate all the support, time and effort all of you guys put into this list. -- Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sub queries
Sorry, I was away for a bit, and got side tracked! I have a tiered Access application running over a WAN, and performance has turned out to be an issue. But to convert the Access queries into something that MySQL can understand takes a while. I started converting them before, but ran into a sub querie, and couldn't think of an alternate way to do it, so I gave up. The queries are pretty hefty (by my standards anyway - since I don't really know SQL very well). I'll paste one here so you can see what I'm working with. It'll take me a day or two to get it to that point again. If you're still willing to help, I'll be happy to do it and post it to the group. Thanks! Mark PS. Here's the Access SQL querie that I have to convert... SELECT CensusData.SiteKey, CensusData.Date, CensusData.Testing, LicensedBeds.SNF_Beds, LicensedBeds.SCNF_Beds, LicensedBeds.SubAcute_Beds, [CensusData]![Mix_SNF_Private]+[CensusData]![Mix_SNF_Medicare]+[CensusData]![Mix_SNF_Medicaid]+[CensusData]![Mix_SNF_Ins_Other] AS Total_SNF, [CensusData]![Mix_SCNF_Private]+[CensusData]![Mix_SCNF_Medicare]+[CensusData]![Mix_SCNF_Medicaid]+[CensusData]![Mix_SCNF_Ins_Other] AS Total_SCNF, [CensusData]![Mix_SubAcute_Private]+[CensusData]![Mix_SubAcute_Medicare]+[CensusData]![Mix_SubAcute_Medicaid]+[CensusData]![Mix_SubAcute_Ins_Other] AS Total_SubAcute, IIf([SNF_Beds]=0,0,Int([Total_SNF]/[SNF_Beds]*1)/100) AS SNF_Per, IIf([SCNF_Beds]=0,0,Int([Total_SCNF]/[SCNF_Beds]*1)/100) AS SNCF_Per, IIf([SubAcute_Beds]=0,0,Int([Total_SubAcute]/[SubAcute_Beds]*1)/100) AS SubAcute_Per, [SNF_Beds]+[SCNF_Beds]+[SubAcute_Beds] AS Total_Beds, IIf([SNF_Beds]=0,0,Int([Total_SNF]/[Total_Beds]*1)/100) AS SNF_Per_Total, IIf([SCNF_Beds]=0,0,Int([Total_SCNF]/[Total_Beds]*1)/100) AS SNCF_Per_Total, IIf([SubAcute_Beds]=0,0,Int([Total_SubAcute]/[Total_Beds]*1)/100) AS SubAcute_Per_Total, [Total_SNF]+[Total_SCNF]+[Total_SubAcute] AS Total_Census, IIf([Total_Beds]=0,0,Int([Total_Census]/[Total_Beds]*1)/100) AS Total_Per FROM (SiteLookup INNER JOIN FilterMaxDataEntries ON SiteLookup.SiteKey = FilterMaxDataEntries.SiteKey) INNER JOIN (LicensedBeds INNER JOIN CensusData ON LicensedBeds.LicensedBedsKey = CensusData.LicensedBedsKey) ON (FilterMaxDataEntries.SiteKey = CensusData.SiteKey) AND (FilterMaxDataEntries.Date = CensusData.Date) AND (FilterMaxDataEntries.MaxOfTime_Stamp = CensusData.Time_Stamp) WHERE (((CensusData.SiteKey)=[Forms]![Gen_OneFacility]![Site]) AND ((CensusData.Testing)=False) AND [CensusData]![Date][Forms]![Gen_OneFacility]![StartDate] And [CensusData]![Date][Forms]![Gen_OneFacility]![EndDate]) Or ([CensusData]![Date]=[Forms]![Gen_OneFacility]![StartDate] Or [CensusData]![Date]=[Forms]![Gen_OneFacility]![EndDate])))False)) ORDER BY CensusData.Date; Ryan Wahle [EMAIL PROTECTED] 01/16/01 04:51PM What's your subquery and we can help you convert it to a join. On 16 Jan 2001 15:59:48 -0500, Mark Marshall wrote: Hi all, Is there any kind of schedule that says when the next MySQL version will be available? I'd like to utilize MySQL here, but I can't really start to seriously work on it until sub-queries are possible. Thanks! Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: sub queries
Hi. On Thu, Jan 18, 2001 at 03:25:24PM -0500, [EMAIL PROTECTED] wrote: Sorry, I was away for a bit, and got side tracked! I have a tiered Access application running over a WAN, and performance has turned out to be an issue. But to convert the Access queries into something that MySQL can understand takes a while. I started converting them before, but ran into a sub querie, and couldn't think of an alternate way to do it, so I gave up. The queries are pretty hefty (by my standards anyway - since I don't really know SQL very well). I'll paste one here so you can see what I'm working with. It'll take me a day or two to get it to that point again. If you're still willing to help, I'll be happy to do it and post it to the group. Thanks! Mark PS. Here's the Access SQL querie that I have to convert... Sorry, but the query you quoted doesn't use sub-queries, does it? So what is the problem with. After reformatting and throwing away a lot of redundant parenthesis I get something like SELECT CensusData.SiteKey, CensusData.Date, CensusData.Testing, LicensedBeds.SNF_Beds, LicensedBeds.SCNF_Beds, LicensedBeds.SubAcute_Beds, [CensusData]![Mix_SNF_Private] + [CensusData]![Mix_SNF_Medicare] + [CensusData]![Mix_SNF_Medicaid] + [CensusData]![Mix_SNF_Ins_Other] AS Total_SNF, [CensusData]![Mix_SCNF_Private] + [CensusData]![Mix_SCNF_Medicare] + [CensusData]![Mix_SCNF_Medicaid] + [CensusData]![Mix_SCNF_Ins_Other] AS Total_SCNF, [CensusData]![Mix_SubAcute_Private] + [CensusData]![Mix_SubAcute_Medicare] + [CensusData]![Mix_SubAcute_Medicaid] + [CensusData]![Mix_SubAcute_Ins_Other] AS Total_SubAcute, IF([SNF_Beds]=0,0,Int([Total_SNF]/[SNF_Beds]*1)/100) AS SNF_Per, IF([SCNF_Beds]=0,0,Int([Total_SCNF]/[SCNF_Beds]*1)/100) AS SNCF_Per, IF([SubAcute_Beds]=0,0,Int([Total_SubAcute]/[SubAcute_Beds]*1)/100) AS SubAcute_Per, [SNF_Beds] + [SCNF_Beds] + [SubAcute_Beds] AS Total_Beds, IF([SNF_Beds]=0,0,Int([Total_SNF]/[Total_Beds]*1)/100) AS SNF_Per_Total, IF([SCNF_Beds]=0,0,Int([Total_SCNF]/[Total_Beds]*1)/100) AS SNCF_Per_Total, IF([SubAcute_Beds]=0,0,Int([Total_SubAcute]/[Total_Beds]*1)/100) AS SubAcute_Per_Total, [Total_SNF] + [Total_SCNF] + [Total_SubAcute] AS Total_Census, IF([Total_Beds]=0,0,Int([Total_Census]/[Total_Beds]*1)/100) AS Total_Per /* swapped the order of the INNER JOINS to get rid of the parenthesis */ FROM SiteLookup INNER JOIN FilterMaxDataEntries ON SiteLookup.SiteKey = FilterMaxDataEntries.SiteKey INNER JOIN CensusData ON FilterMaxDataEntries.SiteKey = CensusData.SiteKey AND FilterMaxDataEntries.Date = CensusData.Date AND FilterMaxDataEntries.MaxOfTime_Stamp = CensusData.Time_Stamp INNER JOIN LicensedBeds ON LicensedBeds.LicensedBedsKey = CensusData.LicensedBedsKey WHERE CensusData.SiteKey = [Forms]![Gen_OneFacility]![Site] AND CensusData.Testing = False AND [CensusData]![Date] = [Forms]![Gen_OneFacility]![StartDate] AND [CensusData]![Date] = [Forms]![Gen_OneFacility]![EndDate] ORDER BY CensusData.Date; Well, you still have to change the [some]![thing] syntax to something appropriate and IIRC use FLOOR() instead of INT(). If you encounter other problems, please explain. Bye, Benjamin. PS: Please format the query yourself next time, at least a little bit. It is quite time consuming to scan a bunch of long lines with 400 chars. Ryan Wahle [EMAIL PROTECTED] 01/16/01 04:51PM What's your subquery and we can help you convert it to a join. On 16 Jan 2001 15:59:48 -0500, Mark Marshall wrote: Hi all, Is there any kind of schedule that says when the next MySQL version will be available? I'd like to utilize MySQL here, but I can't really start to seriously work on it until sub-queries are possible. [...] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: sub queries
You can get around subqueries by using JOIN's. mySQL may not have all the bells and whistles that some other db applications may have, but more often than not, there is a way to get around it. Paul DuBois covers this in his mySQL book very well. -Original Message- From: Mark Marshall [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 16, 2001 1:00 PM To: [EMAIL PROTECTED] Subject: sub queries Hi all, Is there any kind of schedule that says when the next MySQL version will be available? I'd like to utilize MySQL here, but I can't really start to seriously work on it until sub-queries are possible. Thanks! Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
