Re: Join query returning duplicate entries
Hello Trimurthy,
On 4/4/2013 3:21 AM, Trimurthy wrote:
Hi list,
i wrote the following query and it is returning duplicate entries
as shown below, can any one suggest me how to avoid this duplicate entries,
with out using distinct.
Query:
select p.date,p.coacode,p.type,p.crdr,p.quantity,p.amount from
ac_financialpostings p join (select iac from ims_itemcodes where (cat =
'Male Birds' or cat = 'Female Birds')) i on p.coacode = i.iac where p.trnum
like '%02' and p.date between '2012-10-04' and '2013-04-04' order by date
Some more options to the DISTINCT clause may be either EXISTS or IN()
Examples:
select ...
from
ac_financialpostings p
WHERE
exists (select 2 from ims_itemcodes where (cat = 'Male Birds' or cat
= 'Female Birds') and ims_itemcodes.iac = p.coacode)
AND p.trnum like '%02'
AND p.date between '2012-10-04' and '2013-04-04'
order by date
select ...
from ac_financialpostings p
WHERE
p.coacode IN(select iac from ims_itemcodes where (cat =
'Male Birds' or cat = 'Female Birds'))
AND p.trnum
like '%02' and p.date between '2012-10-04' and '2013-04-04' order by date
Or you can use the DISTINCT clause in your subquery, too
select ...
from ac_financialpostings p
join (select DISTINCT iac from ims_itemcodes where (cat =
'Male Birds' or cat = 'Female Birds')) i on p.coacode = i.iac where p.trnum
like '%02' and p.date between '2012-10-04' and '2013-04-04' order by date
Or you can use an explicit temporary table
CREATE TEMPORARY TABLE tmp_iac (key(iac)) ENGINE=MEMORY
SELECT DISTINCT iac
FROM ims_itemcodes
WHERE cat IN('Male Birds', 'Female Birds');
SELECT ...
FROM ac_finanancialpositions p
JOIN tmp_iac
ON tmp_iac.iac = p.coacode
WHERE ...
ORDER BY ... ;
DROP TEMPORARY TABLE tmp_iac;
The advantage to this is that before MySQL 5.6, the implicit temporary
table created by your subquery was not indexed. For more than a trivial
number of rows to compare against, that can reduce overall performance
because the results of your subquery would need to be scanned for each
row of the outer table in the main query it was joining to. Based on the
WHERE clause, all rows from the outer table may not be in the 'join set'
so this is not always a Cartesian product problem.
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: Join query returning duplicate entries
Hi, sorry i tried to help but i hardly understand the use of join in your query since the joined table is not used anywhere. From: Trimurthy To: mysql@lists.mysql.com Sent: Thursday, 4 April 2013, 14:21 Subject: Join query returning duplicate entries Hi list, i wrote the following query and it is returning duplicate entries as shown below, can any one suggest me how to avoid this duplicate entries, with out using distinct. Query: select p.date,p.coacode,p.type,p.crdr,p.quantity,p.amount from ac_financialpostings p join (select iac from ims_itemcodes where (cat = 'Male Birds' or cat = 'Female Birds')) i on p.coacode = i.iac where p.trnum like '%02' and p.date between '2012-10-04' and '2013-04-04' order by date Output: ++-++--+--++ | date | coacode | type | crdr | quantity | amount | ++-++--+--++ | 2012-10-06 | 600500 | MORTALITY | Cr | 13 | 0 | | 2012-10-06 | 600500 | MORTALITY | Cr | 13 | 0 | | 2012-10-06 | 600500 | MORTALITY | Cr | 22 | 0 | | 2012-10-06 | 600500 | MORTALITY | Cr | 22 | 0 | | 2012-10-06 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-06 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-06 | 600500 | MORTALITY | Cr | 8 | 0 | | 2012-10-06 | 600500 | MORTALITY | Cr | 8 | 0 | | 2012-10-06 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-06 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 13 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 13 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 15 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 15 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-13 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-13 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-13 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-20 | 600500 | MORTALITY | Cr | 1 | 0 | | 2012-10-20 | 600500 | MORTALITY | Cr | 14 | 0 | | 2012-10-20 | 600500 | MORTALITY | Cr | 14 | 0 | | 2012-10-20 | 600500 | MORTALITY | Cr | 12 | 0 | | 2012-10-20 | 600500 | MORTALITY | Cr | 12 | 0 | | 2012-10-20 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-20 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-20 | 600500 | MORTALITY | Cr | 10 | 0 | | 2012-10-20 | 600500 | MORTALITY | Cr | 10 | 0 | | 2012-10-20 | 600500 | MORTALITY | Cr | 1 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 13 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 13 | 0 | | 2012-10-27 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-27 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-10-27 | 600500 | MORTALITY | Cr | 11 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 11 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 12 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 12 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-10-27 | 600500 | MORTALITY | Cr | 9 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 4 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 4 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 4 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 4 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 8 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 8 | 0 | | 2012-11-03 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-11-03 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-11-03 | 600500 | MORTALITY | Cr | 8 | 0 | | 2012-11-03 | 600500 | MORTALITY | Cr | 8 | 0 | | 2012-11-10 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-11-10 | 600500 | Birds Amortization | Cr | 0 | 411939 | | 2012-11-10 | 600500 | MORTALITY | Cr | 6 | 0 | | 2012-11-10 | 600500 | MORTALITY | Cr | 6 | 0
Re: Join query returning duplicate entries
- Original Message - > From: "Lucky Wijaya" > To: mysql@lists.mysql.com > Sent: Thursday, 4 April, 2013 10:51:50 AM > Subject: Re: Join query returning duplicate entries > > Hi, sorry i tried to help but i hardly understand the use of join in > your query since the joined table is not used anywhere. Of course it's used - the joined subquery limits the items to male or female birds. As for an answer to your question, Trimurthy, just use SELECT DISTINCT . The cause of the duplicates may be that the iac for male and female birds is identical. -- Linux Kriek Wanderung April 19-21, 2013 http://www.tuxera.be/lkw -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: join query for sale report
any suggestion, for my question plz On Fri, Dec 24, 2010 at 10:57 PM, bharani kumar < bharanikumariyer...@gmail.com> wrote: > I want to take the sale report, > > Group by catID , > > Daily report , > > table name : tblbasket BID Auto Incre, Prim Key > > BID int(20), BasketSessionID varchar(100),ProductCode varchar(50), > CatID int(10), Quantity int(20), AfterDiscount double,purchasedate datetime, > Status int(3) > > table name : tblsale SaleID Auto Incre, Prim Key > > SaleID int(20), BillNo varchar(30), BasketSessionID varchar(200), > CatID int(10), AfterDiscount double, VAT int(20),purchasedate datetime, > Status int(2) > > > the above is my table structure, > > I want to build two query , > > One query should display the daily sale report ,(for this i have > purchasedate, AfterDiscount nothing but an bill amount, Status once the > billing sucess then tblbasket status goes to 3 and tblsale Status goes to 1, > On both table relationship is only BasketSessionID ) > > second query, this query should display the sale report group by cat id > with VAt(vat is present in sale table, ) > > How to create the query ,For this scenario > > > and another is > > > -- Regards B.S.Bharanikumar http://php-mysql-jquery.blogspot.com/
join query for sale report
I want to take the sale report, Group by catID , Daily report , table name : tblbasket BID Auto Incre, Prim Key BID int(20), BasketSessionID varchar(100),ProductCode varchar(50), CatID int(10), Quantity int(20), AfterDiscount double,purchasedate datetime, Status int(3) table name : tblsale SaleID Auto Incre, Prim Key SaleID int(20), BillNo varchar(30), BasketSessionID varchar(200), CatID int(10), AfterDiscount double, VAT int(20),purchasedate datetime, Status int(2) the above is my table structure, I want to build two query , One query should display the daily sale report ,(for this i have purchasedate, AfterDiscount nothing but an bill amount, Status once the billing sucess then tblbasket status goes to 3 and tblsale Status goes to 1, On both table relationship is only BasketSessionID ) second query, this query should display the sale report group by cat id with VAt(vat is present in sale table, ) How to create the query ,For this scenario and another is
Re: Nested join query?
Travis, Thanks a lot! That seems to work perfectly and also cleans up the syntax a bit so I think it's more understandable. Michael On Aug 30, 2010, at 7:21 PM, Travis Ard wrote: > I think you could do away with your right outer join of table B (which will > include all rows from B whether or not they match to C), since you are > explicitly filtering for C.State like 'Yes'. The intermediate result > doesn't necessarily need to be stored in a temporary table. You can include > multiple tables in a single query by specifying each table and the join > condition. Also, If you just want a distinct list of values, you could use > the "DISTINCT" clause as opposed to grouping. Something like the following > query should produce the results you're looking for: > > select distinct a.val > from tablec c > inner join tableb b on b.id = c.id > inner join tablea a on a.num = b.num > where c.state = 'Yes'; > > -Travis > > > -Original Message- > From: Michael Stroh [mailto:st...@astroh.org] > Sent: Monday, August 30, 2010 4:39 PM > To: MySql > Subject: Nested join query? > > Hello everyone. I'm trying to perform a query that acts on 3 tables at once. > I'm thinking I need to produce a set of joins between all three tables to > get the results that I want, but am not sure how to go about it with nesting > or maybe there's even a better way. I need to check TableC for cases where > the State is set to 'Yes' and then find the Num fields in TableB that > correspond to the matching IDs between the two. I currently have a query > that performs a right join on the two that will give me the results. > > The problem is that I now have this single column table (TableBC listed > below) from the first query that I then need to perform a query on TableA to > find the matching records. I'd like to make this into a single query but am > not sure the proper way to combine them all or how to perform a join on the > values in this temporary table that I'm using. > > TableA > Val Num > 1 2 > 2 3 > 3 3 > 4 4 > 5 4 > 6 7 > 7 3 > > TableB > NumID > 1 1 > 2 2 > 3 1 > 4 2 > 5 1 > 6 1 > 7 4 > 8 3 > 9 5 > > TableC > ID State > 1No > 2Yes > 3No > 4Yes > 5No > > Currently to get the single column list from TableB and TableC that I want > to use to query TableA, I'm using: > > SELECT `TableB`.`Num` FROM `TableC` RIGHT JOIN `TableB` ON `TableC`.`ID` = > `TableB`.`ID` WHERE (`TableC`.`State` LIKE 'Yes') GROUP BY `TableB`.`Num` > ORDER BY `TableB`.`ID` > > It is possible to have multiple instances of Num in TableB, that is why I'm > also doing some groupings. In this simplified example, the result I get from > this query is: > > TableBC > Num > 2 > 4 > 7 > > And the preferred result once I add in TableA would be: > > TableABC > Val > 1 > 4 > 5 > 6 > > > So I believe the problem is now is how to insert or merge this into a query > that will look for the results in TableA that I'm really interested in. It > looks to be similar to the query I've already performed, but I'm not sure > how to perform a join like this since I'm referencing a column that only > exists in this temporary table that is being built and I don't want to > accidentally reference the fields in TableB or TableC. > > Thanks in advance! > > Michael > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=st...@astroh.org > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Nested join query?
I think you could do away with your right outer join of table B (which will include all rows from B whether or not they match to C), since you are explicitly filtering for C.State like 'Yes'. The intermediate result doesn't necessarily need to be stored in a temporary table. You can include multiple tables in a single query by specifying each table and the join condition. Also, If you just want a distinct list of values, you could use the "DISTINCT" clause as opposed to grouping. Something like the following query should produce the results you're looking for: select distinct a.val from tablec c inner join tableb b on b.id = c.id inner join tablea a on a.num = b.num where c.state = 'Yes'; -Travis -Original Message- From: Michael Stroh [mailto:st...@astroh.org] Sent: Monday, August 30, 2010 4:39 PM To: MySql Subject: Nested join query? Hello everyone. I'm trying to perform a query that acts on 3 tables at once. I'm thinking I need to produce a set of joins between all three tables to get the results that I want, but am not sure how to go about it with nesting or maybe there's even a better way. I need to check TableC for cases where the State is set to 'Yes' and then find the Num fields in TableB that correspond to the matching IDs between the two. I currently have a query that performs a right join on the two that will give me the results. The problem is that I now have this single column table (TableBC listed below) from the first query that I then need to perform a query on TableA to find the matching records. I'd like to make this into a single query but am not sure the proper way to combine them all or how to perform a join on the values in this temporary table that I'm using. TableA Val Num 1 2 2 3 3 3 4 4 5 4 6 7 7 3 TableB NumID 1 1 2 2 3 1 4 2 5 1 6 1 7 4 8 3 9 5 TableC ID State 1No 2Yes 3No 4Yes 5No Currently to get the single column list from TableB and TableC that I want to use to query TableA, I'm using: SELECT `TableB`.`Num` FROM `TableC` RIGHT JOIN `TableB` ON `TableC`.`ID` = `TableB`.`ID` WHERE (`TableC`.`State` LIKE 'Yes') GROUP BY `TableB`.`Num` ORDER BY `TableB`.`ID` It is possible to have multiple instances of Num in TableB, that is why I'm also doing some groupings. In this simplified example, the result I get from this query is: TableBC Num 2 4 7 And the preferred result once I add in TableA would be: TableABC Val 1 4 5 6 So I believe the problem is now is how to insert or merge this into a query that will look for the results in TableA that I'm really interested in. It looks to be similar to the query I've already performed, but I'm not sure how to perform a join like this since I'm referencing a column that only exists in this temporary table that is being built and I don't want to accidentally reference the fields in TableB or TableC. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Nested join query?
Hello everyone. I'm trying to perform a query that acts on 3 tables at once. I'm thinking I need to produce a set of joins between all three tables to get the results that I want, but am not sure how to go about it with nesting or maybe there's even a better way. I need to check TableC for cases where the State is set to 'Yes' and then find the Num fields in TableB that correspond to the matching IDs between the two. I currently have a query that performs a right join on the two that will give me the results. The problem is that I now have this single column table (TableBC listed below) from the first query that I then need to perform a query on TableA to find the matching records. I'd like to make this into a single query but am not sure the proper way to combine them all or how to perform a join on the values in this temporary table that I'm using. TableA Val Num 1 2 2 3 3 3 4 4 5 4 6 7 7 3 TableB NumID 1 1 2 2 3 1 4 2 5 1 6 1 7 4 8 3 9 5 TableC ID State 1No 2Yes 3No 4Yes 5No Currently to get the single column list from TableB and TableC that I want to use to query TableA, I'm using: SELECT `TableB`.`Num` FROM `TableC` RIGHT JOIN `TableB` ON `TableC`.`ID` = `TableB`.`ID` WHERE (`TableC`.`State` LIKE 'Yes') GROUP BY `TableB`.`Num` ORDER BY `TableB`.`ID` It is possible to have multiple instances of Num in TableB, that is why I'm also doing some groupings. In this simplified example, the result I get from this query is: TableBC Num 2 4 7 And the preferred result once I add in TableA would be: TableABC Val 1 4 5 6 So I believe the problem is now is how to insert or merge this into a query that will look for the results in TableA that I'm really interested in. It looks to be similar to the query I've already performed, but I'm not sure how to perform a join like this since I'm referencing a column that only exists in this temporary table that is being built and I don't want to accidentally reference the fields in TableB or TableC. Thanks in advance! Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Left join query
A LEFT OUTER JOIN in that query in not necessary. An inner join should be used. -Original Message- From: Shiv [mailto:shiv...@gmail.com] Sent: Saturday, May 30, 2009 10:18 PM To: bharani kumar Cc: mysql Subject: Re: Left join query Hi, Along with tables, you should also provide details on how they are related. Assuming "Code" is unique in both tables and left joined on Airport table, you can do something like this SELECT A.Code, C.Code, A.SlNo, C.SlNo, A.Name, C.Location, A.status, C.status, C.type FROM Airport A LEFT OUTER JOIN Common C ON A.Code = C.Code WHERE A.status = 1 and C.status = 1 and C.type = 'airport' Cheers, Shiva On Fri, May 29, 2009 at 8:52 PM, bharani kumar < bharanikumariyer...@gmail.com> wrote: > Airport table > > SlNoName Code AuthLocation status > 1 ChennaiCHN Yes India 1 > 2. Hydarabed HYD Yes India 0 > 3 walkerWAK Yes uk1 > > > common table > > SlNoName CodeType Location > status > 1 ChennaiCHNAirport India > 1 > 2. guest Road GR Seaport India 1 > 3. Hyderabad HYDAirport > India 0 > 4. John gardenJG Seaport India 0 > 5 walkerWAKairportuk1 > > > Hi All > > Can u please tell the query for the above table , > > Display the record which satisfy below condtions , > > 1.Need to display airport name , Where airport status = 1 and common > table status = 1 and common table type = airport > > Output somthing like below > > AirportCode CommonCode AirportSlNo CommonSlNo AirportName > CommonLocation Status Type > > CHNCHN1 1 > Chennai india 1airport > > WAK WAK 35 > walker uk 1airport > > > Any idea , > > > > > Thnaks > > > > > > > > -- > Regards > B.S.Bharanikumar > http://php-mysql-jquery.blogspot.com/ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=shiv...@gmail.com > > The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Left join query
Hi, Along with tables, you should also provide details on how they are related. Assuming "Code" is unique in both tables and left joined on Airport table, you can do something like this SELECT A.Code, C.Code, A.SlNo, C.SlNo, A.Name, C.Location, A.status, C.status, C.type FROM Airport A LEFT OUTER JOIN Common C ON A.Code = C.Code WHERE A.status = 1 and C.status = 1 and C.type = 'airport' Cheers, Shiva On Fri, May 29, 2009 at 8:52 PM, bharani kumar < bharanikumariyer...@gmail.com> wrote: > Airport table > > SlNoName Code AuthLocation status > 1 ChennaiCHN Yes India 1 > 2. Hydarabed HYD Yes India 0 > 3 walkerWAK Yes uk1 > > > common table > > SlNoName CodeType Location > status > 1 ChennaiCHNAirport India > 1 > 2. guest Road GR Seaport India 1 > 3. Hyderabad HYDAirport > India 0 > 4. John gardenJG Seaport India 0 > 5 walkerWAKairportuk1 > > > Hi All > > Can u please tell the query for the above table , > > Display the record which satisfy below condtions , > > 1.Need to display airport name , Where airport status = 1 and common > table status = 1 and common table type = airport > > Output somthing like below > > AirportCode CommonCode AirportSlNo CommonSlNo AirportName > CommonLocation Status Type > > CHNCHN1 1 > Chennai india 1airport > > WAK WAK 35 > walker uk 1airport > > > Any idea , > > > > > Thnaks > > > > > > > > -- > Regards > B.S.Bharanikumar > http://php-mysql-jquery.blogspot.com/ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=shiv...@gmail.com > >
Left join query
Airport table SlNoName Code AuthLocation status 1 ChennaiCHN Yes India 1 2. Hydarabed HYD Yes India 0 3 walkerWAK Yes uk1 common table SlNoName CodeType Locationstatus 1 ChennaiCHNAirport India 1 2. guest Road GR Seaport India 1 3. Hyderabad HYDAirport India 0 4. John gardenJG Seaport India 0 5 walkerWAKairportuk1 Hi All Can u please tell the query for the above table , Display the record which satisfy below condtions , 1.Need to display airport name , Where airport status = 1 and common table status = 1 and common table type = airport Output somthing like below AirportCode CommonCode AirportSlNo CommonSlNo AirportName CommonLocation Status Type CHNCHN1 1 Chennai india 1airport WAK WAK 35 walker uk 1airport Any idea , Thnaks -- Regards B.S.Bharanikumar http://php-mysql-jquery.blogspot.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Complex group/join query
I'm trying to create a rather (what I consider to be) complex analysis query.
It should use data from three tables that contain measurements and write the
results back to a fourth table.
There are three measurement tables: metrica, metricb, and metricc. There's one
table which contains a summary of the results of the analysis: zone
Here are the table descriptions:
+--+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+--+--+--+-+-++
| zone | int(10) unsigned | NO | PRI | NULL | auto_increment |
| zonename | varchar(45) | YES | | NULL | |
| metrica | double | NO | | 0 | |
| metricb | double | NO | | 0 | |
| metricc | double | NO | | 0 | |
+--+--+--+-+-++
All of the metric tables follow the same format. (maybe I should group them
into one table?)
+---+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+---+--+--+-+-++
| metric_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| zone | int(10) unsigned | NO | PRI | NULL | |
| metric | int(11) | NO | | 0 | |
+---+--+--+-+-++
There is a "test zone" with zone = 0. I want to compare this zone to all the
others based on the three metrics and store the similarity factor to the metric
column for that zone in the zone table.
To produce the similarity factor for a given metric in an arbitrary zone, n, I
want to count all metric_id's where the metric is posititve in both zone n and
zone 0 or negative in both. I'll take this sum and divide it by the count of
all metric_id's with non-zero metrics for zone 0. I want to store the result of
this calculation to zone n's entry in the zone table.
Ideally, I'd like to do this all in a large query.
I would imagine that the query would look something like this, but my count
syntax is completely wrong and I know that I'm missing some join and grouping
syntax:
INSERT INTO zone SELECT COUNT(
SELECT a.metric_id FROM metrica AS a JOIN metrica AS b ON metric_id WHERE
a.zone=0 AND b.zone<>0 GROUP BY metric_id HAVING (a.metric>0 AND b.metric>0) OR
(a.metric<0 AND b.metric<0)
) / COUNT(SELECT a.metric_id FROM metrica WHERE metric<>0),
{{repeats similar syntax for remaining columns}} ON DUPLICATE KEY UPDATE
metrica=VALUES(metrica), etc...
How would I go about implementing this query so that it actually works?
Thanks,
Brendan
Re: Trying to work out why a join query is so slow
This is going to return duplicate rows if there are results that match both conditions. One of the queries needs a condition to exclude the results that'll be sent in the other query. You can do it this way, and in some cases it's faster. But, what I think we should really be asking is: 1) what is the output of EXPLAIN SELECT . \G for the original query? Notice the \G terminator -- not a semicolon. 2) what is the output of SHOW CREATE TABLE\G for each table? On Sun, Feb 1, 2009 at 5:23 PM, Sangeetha wrote: > My guess is that the OR is searching the whole table for each element of the > other table. It compounds the select statement. > You may try a Union.Im new to Mysql so im not sure it will work, but you > might try it out. > > SELECT * > FROM sites > INNER JOIN users ON sites.userid = users.ID > WHERE sites.email = 'per...@domain.com' > UNION > SELECT * > FROM sites > INNER JOIN users ON sites.userid = users.ID > WHERE userid.email = 'per...@domain.com' > > On Sun, Feb 1, 2009 at 4:17 PM, Simon Kimber > wrote: > >> Hi Everyone, >> >> I'm trying to run a very simple query on two joined tables but it's taking >> a long time to run. >> >> I have two tables, users and sites, both of which have an email address >> field that I'm querying. >> >> here's my query: >> >> SELECT * >> FROM sites >> INNER JOIN users ON sites.userid = users.ID >> WHERE sites.email = 'per...@domain.com' >> OR users.email = 'per...@domain.com' >> >> both tables contain over 100k rows. users.ID is a primary key, and >> sites.userid, sites.email and users.email all have indices. >> >> The query above is taking over 3.3 seconds to run, but if i only use one of >> the where clauses, ie. I only search on users.email or I only search on >> sites.email, the query takes around 0.002 seconds to run. >> >> As soon as I try and run the query with BOTH where clauses it takes >> exponentially longer! >> >> Can anyone suggest what might be the problem or how I could rewrite the >> query to significantly speed it up? >> >> Thanks! >> >> Simon >> > > > > -- > Sangeetha Damodar > www.vlsibank.com > -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Trying to work out why a join query is so slow
UNION all does a cartesian join ..maxiumum number of results will be delivered SELECT * FROM sites INNER JOIN sites.userid = users.ID; -- INNER JOIN users ON sites.userid = users.ID --ALSO put (hopefully UNIQUE indexes) on sites.userid and users.id Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > Date: Sun, 1 Feb 2009 17:23:10 -0500 > Subject: Re: Trying to work out why a join query is so slow > From: ysn...@gmail.com > To: si...@internetstuff.ltd.uk > CC: mysql@lists.mysql.com > > My guess is that the OR is searching the whole table for each element of the > other table. It compounds the select statement. > You may try a Union.Im new to Mysql so im not sure it will work, but you > might try it out. > > SELECT * > FROM sites > INNER JOIN users ON sites.userid = users.ID > WHERE sites.email = 'per...@domain.com' > UNION > SELECT * > FROM sites > INNER JOIN users ON sites.userid = users.ID > WHERE userid.email = 'per...@domain.com' > > On Sun, Feb 1, 2009 at 4:17 PM, Simon Kimber > wrote: > > > Hi Everyone, > > > > I'm trying to run a very simple query on two joined tables but it's taking > > a long time to run. > > > > I have two tables, users and sites, both of which have an email address > > field that I'm querying. > > > > here's my query: > > > > SELECT * > > FROM sites > > INNER JOIN users ON sites.userid = users.ID > > WHERE sites.email = 'per...@domain.com' > > OR users.email = 'per...@domain.com' > > > > both tables contain over 100k rows. users.ID is a primary key, and > > sites.userid, sites.email and users.email all have indices. > > > > The query above is taking over 3.3 seconds to run, but if i only use one of > > the where clauses, ie. I only search on users.email or I only search on > > sites.email, the query takes around 0.002 seconds to run. > > > > As soon as I try and run the query with BOTH where clauses it takes > > exponentially longer! > > > > Can anyone suggest what might be the problem or how I could rewrite the > > query to significantly speed it up? > > > > Thanks! > > > > Simon > > > > > > -- > Sangeetha Damodar > www.vlsibank.com _ Windows Live™ Hotmail®:…more than just e-mail. http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t2_hm_justgotbetter_explore_012009
Re: Trying to work out why a join query is so slow
My guess is that the OR is searching the whole table for each element of the other table. It compounds the select statement. You may try a Union.Im new to Mysql so im not sure it will work, but you might try it out. SELECT * FROM sites INNER JOIN users ON sites.userid = users.ID WHERE sites.email = 'per...@domain.com' UNION SELECT * FROM sites INNER JOIN users ON sites.userid = users.ID WHERE userid.email = 'per...@domain.com' On Sun, Feb 1, 2009 at 4:17 PM, Simon Kimber wrote: > Hi Everyone, > > I'm trying to run a very simple query on two joined tables but it's taking > a long time to run. > > I have two tables, users and sites, both of which have an email address > field that I'm querying. > > here's my query: > > SELECT * > FROM sites > INNER JOIN users ON sites.userid = users.ID > WHERE sites.email = 'per...@domain.com' > OR users.email = 'per...@domain.com' > > both tables contain over 100k rows. users.ID is a primary key, and > sites.userid, sites.email and users.email all have indices. > > The query above is taking over 3.3 seconds to run, but if i only use one of > the where clauses, ie. I only search on users.email or I only search on > sites.email, the query takes around 0.002 seconds to run. > > As soon as I try and run the query with BOTH where clauses it takes > exponentially longer! > > Can anyone suggest what might be the problem or how I could rewrite the > query to significantly speed it up? > > Thanks! > > Simon > -- Sangeetha Damodar www.vlsibank.com
Trying to work out why a join query is so slow
Hi Everyone, I'm trying to run a very simple query on two joined tables but it's taking a long time to run. I have two tables, users and sites, both of which have an email address field that I'm querying. here's my query: SELECT * FROM sites INNER JOIN users ON sites.userid = users.ID WHERE sites.email = 'per...@domain.com' OR users.email = 'per...@domain.com' both tables contain over 100k rows. users.ID is a primary key, and sites.userid, sites.email and users.email all have indices. The query above is taking over 3.3 seconds to run, but if i only use one of the where clauses, ie. I only search on users.email or I only search on sites.email, the query takes around 0.002 seconds to run. As soon as I try and run the query with BOTH where clauses it takes exponentially longer! Can anyone suggest what might be the problem or how I could rewrite the query to significantly speed it up? Thanks! Simon
Re: question about update/join query
Brent - thanks, now I understand. My aversion to subqueries is for performance against a very large table, which event_log promises to be. I hope to minimize this with some time boundaries on that table (where event_time between x and y). But thanks, I'll play with that. And thanks Martin for the start. andy Brent Baisley wrote: You can do it in a single UPDATE statement, but you do need a form of a subquery. Why the aversion of a subquery? The simplest approach is to first get what you want using a SELECT statement. Then change SELECT to UPDATE and add your SET statement. In your case there is a little twist because you have to use a group by to get the max. So first get the users and latest event time, as Martin stated. SELECT user_id, MAX(event_time) maxtime FROM event_log GROUP by user_id That can be used to create a "virtual" table you can join against for your update statement. UPDATE user JOIN (SELECT user_id, MAX(event_time) maxtime FROM event_log GROUP by user_id) AS eMax ON user.user_id=eMax.user_id SET last_visit=maxtime WHERE user.user_id=eMax.user_id That should do it, although I don't think you need the WHERE clause. That will do a full table scan on the event_log table, which can be very bad if it is large. You can work around this by compiling groups of users at a time. This will require querying the users and for the group of users and joining on the event_log table. Brent Baisley Systems Architect On Thu, May 8, 2008 at 12:26 AM, Andy Wallace <[EMAIL PROTECTED]> wrote: Ok, I think I need to try to restate my problem. I have an event_log table, which tracks events (!). Basic structure is: table: event_log event_time timestamp event_idint user_id int and my user table: table: user user_id int namevarchar(50) last_visit datetime I want to run a query that updates the last_visit column of user with the MAX(event_time) row for which the user_id's match, but only if I find an event: update user U set U.last_visit = (select max(L.event_time) from event_log L where L.user_id = U.user_id) I would like to do it without a subquery, I thought that the multiple table syntax for UPDATE would do it, but I can't wrap my head around it. UPDATE user U, event_log L SET U.last_visit = MAX(L.event_time) WHERE U.user_id = L.user_id GROUP BY L.event_time I guess the main question is - CAN I do this? Or will I have to resort to either a subquery, or external processing? thanks, andy Martin wrote: Hi Andy- the MAX function needs group by for the column for which it calculating max value as in this example (select MAX(EL.event_time) // from event_log EL // where EL.enduser_acnt = E.enduser_acnt //Inner join forces selection on columns which contain non null values as seen here from event_log AS EL INNER JOIN Event AS E ON EL.enduser_acnt = Event.enduser_anct group by EL.event_time); // group by EL.enduser_acnt); HTH Martin - Original Message - From: "Andy Wallace" <[EMAIL PROTECTED]> To: "Martin" <[EMAIL PROTECTED]> Cc: "mysql list" Sent: Wednesday, May 07, 2008 6:21 PM Subject: Re: question about update/join query I want to put only the max date into the field... I was thinking that max was a group function, but now that I type that out loud, perhaps I'm not using all the neurons available... hmmm... thanks, andy Martin wrote: Hi Andy- Is there a reason why you are using Query group by clause in UPDATE statement? M - Original Message - From: "Andy Wallace" <[EMAIL PROTECTED]> To: "mysql list" Sent: Wednesday, May 07, 2008 1:07 PM Subject: Re: question about update/join query Clarification: I DON'T want to update the last_visit field if there is no matching event record... I managed to get this to sort of work: update enduser E set E.last_visit = (select MAX(EL.event_time) from event_log EL where EL.enduser_acnt = E.enduser_acnt group by EL.enduser_acnt); but it updated the last_visit field to the default value if it found no matching event_log row... which I don't want to happen. thanks, andy Andy Wallace wrote: Hey all - I have two tables - an event_log table, and a user table. There is a "last_visit" column in the user table, and I want to update it from the event_log with the most recent event timestamp. And I want to do it without a subquery, eventually, both these tables will be pretty large, especially the event_log. I tried this: update enduser E join event_log EL on EL.enduser_acnt = E.enduser_acnt set E.last_visit = MAX(EL.event_time) group by EL.enduser_acnt but I get an error on the group by. The pertinent tables sections are: table event_log event_time TIMESTAMP
Re: question about update/join query
Ok, I think I need to try to restate my problem. I have an event_log table, which tracks events (!). Basic structure is: table: event_log event_time timestamp event_idint user_id int and my user table: table: user user_id int namevarchar(50) last_visit datetime I want to run a query that updates the last_visit column of user with the MAX(event_time) row for which the user_id's match, but only if I find an event: update user U set U.last_visit = (select max(L.event_time) from event_log L where L.user_id = U.user_id) I would like to do it without a subquery, I thought that the multiple table syntax for UPDATE would do it, but I can't wrap my head around it. UPDATE user U, event_log L SET U.last_visit = MAX(L.event_time) WHERE U.user_id = L.user_id GROUP BY L.event_time I guess the main question is - CAN I do this? Or will I have to resort to either a subquery, or external processing? thanks, andy Martin wrote: Hi Andy- the MAX function needs group by for the column for which it calculating max value as in this example (select MAX(EL.event_time) // from event_log EL // where EL.enduser_acnt = E.enduser_acnt //Inner join forces selection on columns which contain non null values as seen here from event_log AS EL INNER JOIN Event AS E ON EL.enduser_acnt = Event.enduser_anct group by EL.event_time); // group by EL.enduser_acnt); HTH Martin - Original Message - From: "Andy Wallace" <[EMAIL PROTECTED]> To: "Martin" <[EMAIL PROTECTED]> Cc: "mysql list" Sent: Wednesday, May 07, 2008 6:21 PM Subject: Re: question about update/join query I want to put only the max date into the field... I was thinking that max was a group function, but now that I type that out loud, perhaps I'm not using all the neurons available... hmmm... thanks, andy Martin wrote: Hi Andy- Is there a reason why you are using Query group by clause in UPDATE statement? M - Original Message - From: "Andy Wallace" <[EMAIL PROTECTED]> To: "mysql list" Sent: Wednesday, May 07, 2008 1:07 PM Subject: Re: question about update/join query Clarification: I DON'T want to update the last_visit field if there is no matching event record... I managed to get this to sort of work: update enduser E set E.last_visit = (select MAX(EL.event_time) from event_log EL where EL.enduser_acnt = E.enduser_acnt group by EL.enduser_acnt); but it updated the last_visit field to the default value if it found no matching event_log row... which I don't want to happen. thanks, andy Andy Wallace wrote: Hey all - I have two tables - an event_log table, and a user table. There is a "last_visit" column in the user table, and I want to update it from the event_log with the most recent event timestamp. And I want to do it without a subquery, eventually, both these tables will be pretty large, especially the event_log. I tried this: update enduser E join event_log EL on EL.enduser_acnt = E.enduser_acnt set E.last_visit = MAX(EL.event_time) group by EL.enduser_acnt but I get an error on the group by. The pertinent tables sections are: table event_log event_time TIMESTAMP enduser_acnt int table enduser enduser_acnt int last_visit datetime Any help appreciated. Thanks... andy -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about update/join query
Ok, I think I need to try to restate my problem. I have an event_log table, which tracks events (!). Basic structure is: table: event_log event_time timestamp event_idint user_id int and my user table: table user user_id int namevarchar(50) last_visit datetime I want to run a query that updates the last_visit column of user with the MAX(event_time) row for which the user_id's match, but only if I find an event: update user U set U.last_visit = (select max(L.event_time) from event_log L where L.user_id = U.user_id I would like to do it without a subquery, I thought that the multiple table syntax for UPDATE would do it, but I can't wrap my head around it. UPDATE user U, event_log L SET U.last_visit = MAX(L.event_time) WHERE U.user_id = L.user_id GROUP BY L.event_time I guess the main question is - CAN I do this? Or will I have to resort to either a subquery, or external processing? thanks, andy Martin wrote: Hi Andy- the MAX function needs group by for the column for which it calculating max value as in this example (select MAX(EL.event_time) // from event_log EL // where EL.enduser_acnt = E.enduser_acnt //Inner join forces selection on columns which contain non null values as seen here from event_log AS EL INNER JOIN Event AS E ON EL.enduser_acnt = Event.enduser_anct group by EL.event_time); // group by EL.enduser_acnt); HTH Martin - Original Message - From: "Andy Wallace" <[EMAIL PROTECTED]> To: "Martin" <[EMAIL PROTECTED]> Cc: "mysql list" Sent: Wednesday, May 07, 2008 6:21 PM Subject: Re: question about update/join query I want to put only the max date into the field... I was thinking that max was a group function, but now that I type that out loud, perhaps I'm not using all the neurons available... hmmm... thanks, andy Martin wrote: Hi Andy- Is there a reason why you are using Query group by clause in UPDATE statement? M - Original Message - From: "Andy Wallace" <[EMAIL PROTECTED]> To: "mysql list" Sent: Wednesday, May 07, 2008 1:07 PM Subject: Re: question about update/join query Clarification: I DON'T want to update the last_visit field if there is no matching event record... I managed to get this to sort of work: update enduser E set E.last_visit = (select MAX(EL.event_time) from event_log EL where EL.enduser_acnt = E.enduser_acnt group by EL.enduser_acnt); but it updated the last_visit field to the default value if it found no matching event_log row... which I don't want to happen. thanks, andy Andy Wallace wrote: Hey all - I have two tables - an event_log table, and a user table. There is a "last_visit" column in the user table, and I want to update it from the event_log with the most recent event timestamp. And I want to do it without a subquery, eventually, both these tables will be pretty large, especially the event_log. I tried this: update enduser E join event_log EL on EL.enduser_acnt = E.enduser_acnt set E.last_visit = MAX(EL.event_time) group by EL.enduser_acnt but I get an error on the group by. The pertinent tables sections are: table event_log event_time TIMESTAMP enduser_acnt int table enduser enduser_acnt int last_visit datetime Any help appreciated. Thanks... andy -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about update/join query
I want to put only the max date into the field... I was thinking that max was a group function, but now that I type that out loud, perhaps I'm not using all the neurons available... hmmm... thanks, andy Martin wrote: Hi Andy- Is there a reason why you are using Query group by clause in UPDATE statement? M - Original Message - From: "Andy Wallace" <[EMAIL PROTECTED]> To: "mysql list" Sent: Wednesday, May 07, 2008 1:07 PM Subject: Re: question about update/join query Clarification: I DON'T want to update the last_visit field if there is no matching event record... I managed to get this to sort of work: update enduser E set E.last_visit = (select MAX(EL.event_time) from event_log EL where EL.enduser_acnt = E.enduser_acnt group by EL.enduser_acnt); but it updated the last_visit field to the default value if it found no matching event_log row... which I don't want to happen. thanks, andy Andy Wallace wrote: Hey all - I have two tables - an event_log table, and a user table. There is a "last_visit" column in the user table, and I want to update it from the event_log with the most recent event timestamp. And I want to do it without a subquery, eventually, both these tables will be pretty large, especially the event_log. I tried this: update enduser E join event_log EL on EL.enduser_acnt = E.enduser_acnt set E.last_visit = MAX(EL.event_time) group by EL.enduser_acnt but I get an error on the group by. The pertinent tables sections are: table event_log event_time TIMESTAMP enduser_acnt int table enduser enduser_acnt int last_visit datetime Any help appreciated. Thanks... andy -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about update/join query
Clarification: I DON'T want to update the last_visit field if there is no matching event record... I managed to get this to sort of work: update enduser E set E.last_visit = (select MAX(EL.event_time) from event_log EL where EL.enduser_acnt = E.enduser_acnt group by EL.enduser_acnt); but it updated the last_visit field to the default value if it found no matching event_log row... which I don't want to happen. thanks, andy Andy Wallace wrote: Hey all - I have two tables - an event_log table, and a user table. There is a "last_visit" column in the user table, and I want to update it from the event_log with the most recent event timestamp. And I want to do it without a subquery, eventually, both these tables will be pretty large, especially the event_log. I tried this: update enduser E join event_log EL on EL.enduser_acnt = E.enduser_acnt set E.last_visit = MAX(EL.event_time) group by EL.enduser_acnt but I get an error on the group by. The pertinent tables sections are: table event_log event_time TIMESTAMP enduser_acnt int table enduser enduser_acnt int last_visit datetime Any help appreciated. Thanks... andy -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question about update/join query
Hey all - I have two tables - an event_log table, and a user table. There is a "last_visit" column in the user table, and I want to update it from the event_log with the most recent event timestamp. And I want to do it without a subquery, eventually, both these tables will be pretty large, especially the event_log. I tried this: update enduser E join event_log EL on EL.enduser_acnt = E.enduser_acnt set E.last_visit = MAX(EL.event_time) group by EL.enduser_acnt but I get an error on the group by. The pertinent tables sections are: table event_log event_time TIMESTAMP enduser_acnt int table enduser enduser_acnt int last_visit datetime Any help appreciated. Thanks... andy -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Not In join query.
Hi Chris, Chris W wrote: I have 2 queries to give me a list of names. Q1: SELECT DISTINCT FName, LName FROM user u JOIN userprofile p USING ( UserID ) JOIN trainingstatus t USING ( UserID ) WHERE ProgramID =12 ORDER BY LName, FName Q2 SELECT DISTINCT FName, LName FROM namelist WHERE `Date` What I need is query that will give me a list of names that are in the Q2 result but not in the Q1 result. This is easy enough if I am just doing the match on one filed I can do this SELECT Name FROM namelist WHERE `Date` AND Name NOT IN( SELECT Name FROM . . . . . . ) What I can't figure out is how to do it if I want to match of FName and LName. I tried to use concat to build the full name and do the not in based on the new field MySQL didn't like that query at all. This is easier to do with an exclusion join: http://www.xaprb.com/blog/2005/09/23/how-to-write-a-sql-exclusion-join/ It is also much more efficient in current versions of MySQL. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Not In join query.
I have 2 queries to give me a list of names. Q1: SELECT DISTINCT FName, LName FROM user u JOIN userprofile p USING ( UserID ) JOIN trainingstatus t USING ( UserID ) WHERE ProgramID =12 ORDER BY LName, FName Q2 SELECT DISTINCT FName, LName FROM namelist WHERE `Date` What I need is query that will give me a list of names that are in the Q2 result but not in the Q1 result. This is easy enough if I am just doing the match on one filed I can do this SELECT Name FROM namelist WHERE `Date` AND Name NOT IN( SELECT Name FROM . . . . . . ) What I can't figure out is how to do it if I want to match of FName and LName. I tried to use concat to build the full name and do the not in based on the new field MySQL didn't like that query at all. -- Chris W KE5GIX "Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm"; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insane execution time for JOIN query
>> On 8/31/06, Harrison Fisk ** <[EMAIL PROTECTED]> *** wrote: >> Hello, >> >> On Aug 31, 2006, at 5:12 AM, Kim Christensen wrote: >> >>> Hey list; >>> >>> I posted this message under an earlier thread which touched the same >>> subject - but I realized my case could be slightly different, thus >>> posting a new thread here. Sorry for any inconvenience. >>> >>> I have two tables, one of which is a list over products which all have >>> unique product id's (product_id). The other table, items, is a list >>> over the products' corresponding prices and stock info; one row per >>> each one of our supplier that has the item. >> >> The problem is that your product_id is an INT in one table and a >> VARCHAR in the other. This will cause MySQL to have to do >> conversions of data types, which means it can't use indexes. Switch >> product_id to be an INT and both tables and it should solve your >> performance problems. > Ouch. I reduced the DELETE... query time to 0.4 secs in total after > changing the second table's product_id to an INT. Thanks alot, I sure > wouldn't have seen this on my own after staring myself blind on this - > you've saved me lots of work! > > I'm sure gonna be more careful with the data types next time! :-) Having taken a weeklong MySQL course with Harry a number of years ago, I'm not surprised by his knowledge and acumen. It's a benefit to us all that he still frequents this mailing list. David Giragosian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insane execution time for JOIN query
On 8/31/06, Harrison Fisk <[EMAIL PROTECTED]> wrote: Hello, On Aug 31, 2006, at 5:12 AM, Kim Christensen wrote: > Hey list; > > I posted this message under an earlier thread which touched the same > subject - but I realized my case could be slightly different, thus > posting a new thread here. Sorry for any inconvenience. > > I have two tables, one of which is a list over products which all have > unique product id's (product_id). The other table, items, is a list > over the products' corresponding prices and stock info; one row per > each one of our supplier that has the item. The problem is that your product_id is an INT in one table and a VARCHAR in the other. This will cause MySQL to have to do conversions of data types, which means it can't use indexes. Switch product_id to be an INT and both tables and it should solve your performance problems. Ouch. I reduced the DELETE... query time to 0.4 secs in total after changing the second table's product_id to an INT. Thanks alot, I sure wouldn't have seen this on my own after staring myself blind on this - you've saved me lots of work! I'm sure gonna be more careful with the data types next time! :-) -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insane execution time for JOIN query
Hello, On Aug 31, 2006, at 5:12 AM, Kim Christensen wrote: Hey list; I posted this message under an earlier thread which touched the same subject - but I realized my case could be slightly different, thus posting a new thread here. Sorry for any inconvenience. I have two tables, one of which is a list over products which all have unique product id's (product_id). The other table, items, is a list over the products' corresponding prices and stock info; one row per each one of our supplier that has the item. The problem is that your product_id is an INT in one table and a VARCHAR in the other. This will cause MySQL to have to do conversions of data types, which means it can't use indexes. Switch product_id to be an INT and both tables and it should solve your performance problems. The culpr... ehrm, query: DELETE products FROM products LEFT JOIN items ON products.product_id = items.product_id WHERE items.product_id IS NULL; Table descriptions: PRODUCTS ++--+--+-+- ++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+- ++ | product_id | int(10) unsigned | NO | PRI | | auto_increment | ITEMS: +--+--+--+-+- ++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+- ++ | item_id | int(10) unsigned | NO | PRI | | auto_increment | | product_id | varchar(45) | NO | MUL | 0 || Regards, Harrison -- Harrison C. Fisk, Senior Support Engineer MySQL AB, www.mysql.com Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/ packaged/cluster.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insane execution time for JOIN query
Kim, The first thing I'd do is run a MyISAMChk on the table to see if the index is damaged. The second thing I'd do is run Optimize on the tables regularly because after a lot of rows have been deleted it leaves holes in the table which slows down table performance. Of course you can do an Explain on: select * from products LEFT JOIN items ON products.product_id = items.product_id WHERE items.product_id IS NULL; to see if it is using the index. And finally turning it into a Subselect might speed it up. (Yes, subselects can run faster than table joins in some cases) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insane execution time for JOIN query
On 8/31/06, Brent Baisley <[EMAIL PROTECTED]> wrote: hmmm, not sure why it's only scanning 89K records from the products table, I would think it would scan the whole table. It is scanning the entire items table, which I would think it wouldn't do. Well, the query in question actually did do some work, the current row count IS 89k. My bad. The speed is still an issue though! How about posting your "SHOW INDEX FROM items" result. +---+++--+--+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---+++--+--+---+-+--++--++-+ | items | 0 | PRIMARY| 1| item_id | A | 123223 | NULL || | BTREE | | | items | 0 | PRICE_INFO | 1| product_id | A | NULL| NULL || | BTREE | | | items | 0 | PRICE_INFO | 2| supplier_id | A | 123223 | NULL || | BTREE | | | items | 1 | retail_id | 1| item_internal_id | A | 123223 | NULL || | BTREE | | +---+++--+--+---+-+--++--++-+ Also, what are your memory settings? SHOW VARIABLES LIKE "%buffer_size" +-+--+ | Variable_name | Value| +-+--+ | bulk_insert_buffer_size | 8388608 | | innodb_log_buffer_size | 1048576 | | join_buffer_size| 131072 | | key_buffer_size | 16777216 | | myisam_sort_buffer_size | 8388608 | | preload_buffer_size | 32768| | read_buffer_size| 131072 | | read_rnd_buffer_size| 262144 | | sort_buffer_size| 2097144 | +-+--+ The box has 1GB of physical RAM and 2GB in one swap partition. Thanks for the help so far! -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insane execution time for JOIN query
hmmm, not sure why it's only scanning 89K records from the products table, I would think it would scan the whole table. It is scanning the entire items table, which I would think it wouldn't do. How about posting your "SHOW INDEX FROM items" result. Also, what are your memory settings? SHOW VARIABLES LIKE "%buffer_size" - Original Message - From: "Kim Christensen" <[EMAIL PROTECTED]> To: "MySQL List" Sent: Thursday, August 31, 2006 9:12 AM Subject: Re: Insane execution time for JOIN query On 8/31/06, Brent Baisley <[EMAIL PROTECTED]> wrote: How about posting the results of: EXPLAIN SELECT products LEFT JOIN items ON products.product_id = items.product_id WHERE items.product_id IS NULL; ++-+--+--+---+--+-+--++-+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+---+--+-+--++-+ | 1 | SIMPLE | products | ALL | | | | | 89447 | | | 1 | SIMPLE | items| ALL | PRICE_INFO| | | | 123223 | Using where; Not exists | ++-+--+--+---+--+-+--++-+ As you can see I have an index (UNIQUE) set that I use for an on INSERT... ON DUPLICATE method while updating the prices every morning, is this the messy part? Your query looks fine to me. 6 hours is a very long time for tables that small. Are these MyISAM or InnoDB? They are both MyISAM, with no other options changed from default. - Original Message - From: "Kim Christensen" <[EMAIL PROTECTED]> To: "MySQL List" Sent: Thursday, August 31, 2006 5:12 AM Subject: Insane execution time for JOIN query > Hey list; > > I posted this message under an earlier thread which touched the same > subject - but I realized my case could be slightly different, thus > posting a new thread here. Sorry for any inconvenience. > > I have two tables, one of which is a list over products which all have > unique product id's (product_id). The other table, items, is a list > over the products' corresponding prices and stock info; one row per > each one of our supplier that has the item. > > Now, each night I run a query which removes all rows from the "items" > table which haven't been updated in the last 24 hours. This works just > fine, so here's my actual problem: > > Every now and then, all rows for a specific product in the "items" > table gets removed after the nightly update, and I'm working on a > query which removes all rows in the "products" table that doesn't have > any corresponding rows (matched with product_id) in the "items" table. > > The culpr... ehrm, query: > > DELETE products FROM products > LEFT JOIN items ON products.product_id = items.product_id > WHERE items.product_id IS NULL; > > Now, my query has been running for 6 hours straight, and it's marked > as "Sending data" in the process list. Any ideas? > > "products" has ~113.500 records while "items" has ~123.439. > > Table descriptions: > > PRODUCTS > ++--+--+-+-++ > | Field | Type | Null | Key | Default | > Extra | > ++--+--+-+-++ > | product_id | int(10) unsigned | NO | PRI | | > auto_increment | > | product_retail_id | varchar(255) | NO | MUL | | >| > | manufactor_id | int(10) unsigned | NO | | 0 | >| > | product_description| varchar(255) | NO | | | >| > | product_comment| text | NO | | | >| > | product_internal_id| varchar(255) | NO | | | >| > | product_price1_percent | float| NO | | 0 | >| > | product_price2_percent | float| NO | | 0 | >| > | product_price3_percent | float| NO | | 0 | >| > | product_price1_amount | float| NO | | 0 | >| > | product_price2_amount | float| NO | | 0 | >| > | product_price3_amount | float| NO | | 0 | >| > | product_added | int(10) unsigned | NO | | 0
Re: Insane execution time for JOIN query
On 8/31/06, Brent Baisley <[EMAIL PROTECTED]> wrote: How about posting the results of: EXPLAIN SELECT products LEFT JOIN items ON products.product_id = items.product_id WHERE items.product_id IS NULL; ++-+--+--+---+--+-+--++-+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+--+---+--+-+--++-+ | 1 | SIMPLE | products | ALL | | | | | 89447 | | | 1 | SIMPLE | items| ALL | PRICE_INFO| | | | 123223 | Using where; Not exists | ++-+--+--+---+--+-+--++-+ As you can see I have an index (UNIQUE) set that I use for an on INSERT... ON DUPLICATE method while updating the prices every morning, is this the messy part? Your query looks fine to me. 6 hours is a very long time for tables that small. Are these MyISAM or InnoDB? They are both MyISAM, with no other options changed from default. - Original Message - From: "Kim Christensen" <[EMAIL PROTECTED]> To: "MySQL List" Sent: Thursday, August 31, 2006 5:12 AM Subject: Insane execution time for JOIN query > Hey list; > > I posted this message under an earlier thread which touched the same > subject - but I realized my case could be slightly different, thus > posting a new thread here. Sorry for any inconvenience. > > I have two tables, one of which is a list over products which all have > unique product id's (product_id). The other table, items, is a list > over the products' corresponding prices and stock info; one row per > each one of our supplier that has the item. > > Now, each night I run a query which removes all rows from the "items" > table which haven't been updated in the last 24 hours. This works just > fine, so here's my actual problem: > > Every now and then, all rows for a specific product in the "items" > table gets removed after the nightly update, and I'm working on a > query which removes all rows in the "products" table that doesn't have > any corresponding rows (matched with product_id) in the "items" table. > > The culpr... ehrm, query: > > DELETE products FROM products > LEFT JOIN items ON products.product_id = items.product_id > WHERE items.product_id IS NULL; > > Now, my query has been running for 6 hours straight, and it's marked > as "Sending data" in the process list. Any ideas? > > "products" has ~113.500 records while "items" has ~123.439. > > Table descriptions: > > PRODUCTS > ++--+--+-+-++ > | Field | Type | Null | Key | Default | > Extra | > ++--+--+-+-++ > | product_id | int(10) unsigned | NO | PRI | | > auto_increment | > | product_retail_id | varchar(255) | NO | MUL | | >| > | manufactor_id | int(10) unsigned | NO | | 0 | >| > | product_description| varchar(255) | NO | | | >| > | product_comment| text | NO | | | >| > | product_internal_id| varchar(255) | NO | | | >| > | product_price1_percent | float| NO | | 0 | >| > | product_price2_percent | float| NO | | 0 | >| > | product_price3_percent | float| NO | | 0 | >| > | product_price1_amount | float| NO | | 0 | >| > | product_price2_amount | float| NO | | 0 | >| > | product_price3_amount | float| NO | | 0 | >| > | product_added | int(10) unsigned | NO | | 0 | >| > | product_url| varchar(255) | NO | | | >| > | product_ean| varchar(13) | NO | | | >| > ++--+--+-+-++ > > ITEMS: > +--+--+--+-+-++ > | Field| Type | Null | Key | Default | Extra | > +--+--+--+-+-++ > |
Re: Insane execution time for JOIN query
How about posting the results of: EXPLAIN SELECT products LEFT JOIN items ON products.product_id = items.product_id WHERE items.product_id IS NULL; Your query looks fine to me. 6 hours is a very long time for tables that small. Are these MyISAM or InnoDB? - Original Message - From: "Kim Christensen" <[EMAIL PROTECTED]> To: "MySQL List" Sent: Thursday, August 31, 2006 5:12 AM Subject: Insane execution time for JOIN query Hey list; I posted this message under an earlier thread which touched the same subject - but I realized my case could be slightly different, thus posting a new thread here. Sorry for any inconvenience. I have two tables, one of which is a list over products which all have unique product id's (product_id). The other table, items, is a list over the products' corresponding prices and stock info; one row per each one of our supplier that has the item. Now, each night I run a query which removes all rows from the "items" table which haven't been updated in the last 24 hours. This works just fine, so here's my actual problem: Every now and then, all rows for a specific product in the "items" table gets removed after the nightly update, and I'm working on a query which removes all rows in the "products" table that doesn't have any corresponding rows (matched with product_id) in the "items" table. The culpr... ehrm, query: DELETE products FROM products LEFT JOIN items ON products.product_id = items.product_id WHERE items.product_id IS NULL; Now, my query has been running for 6 hours straight, and it's marked as "Sending data" in the process list. Any ideas? "products" has ~113.500 records while "items" has ~123.439. Table descriptions: PRODUCTS ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | product_id | int(10) unsigned | NO | PRI | | auto_increment | | product_retail_id | varchar(255) | NO | MUL | | | | manufactor_id | int(10) unsigned | NO | | 0 | | | product_description| varchar(255) | NO | | | | | product_comment| text | NO | | | | | product_internal_id| varchar(255) | NO | | | | | product_price1_percent | float| NO | | 0 | | | product_price2_percent | float| NO | | 0 | | | product_price3_percent | float| NO | | 0 | | | product_price1_amount | float| NO | | 0 | | | product_price2_amount | float| NO | | 0 | | | product_price3_amount | float| NO | | 0 | | | product_added | int(10) unsigned | NO | | 0 | | | product_url| varchar(255) | NO | | | | | product_ean| varchar(13) | NO | | | | ++--+--+-+-++ ITEMS: +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | item_id | int(10) unsigned | NO | PRI | | auto_increment | | product_id | varchar(45) | NO | MUL | 0 || | item_price | float| NO | | 0 || | item_stock | int(11) | NO | | 0 || | item_incoming| varchar(45) | NO | | || | item_updated | int(10) unsigned | NO | | 0 || | item_url | varchar(255) | NO | | || | supplier_id | int(10) unsigned | NO | | 0 || | item_internal_id | varchar(45) | NO | MUL | || | item_description | varchar(255) | NO | | || | item_weight | float| NO | | 0 || +--+--+--+-+-++ Best regards -- Kim Christensen -- 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]
Insane execution time for JOIN query
Hey list; I posted this message under an earlier thread which touched the same subject - but I realized my case could be slightly different, thus posting a new thread here. Sorry for any inconvenience. I have two tables, one of which is a list over products which all have unique product id's (product_id). The other table, items, is a list over the products' corresponding prices and stock info; one row per each one of our supplier that has the item. Now, each night I run a query which removes all rows from the "items" table which haven't been updated in the last 24 hours. This works just fine, so here's my actual problem: Every now and then, all rows for a specific product in the "items" table gets removed after the nightly update, and I'm working on a query which removes all rows in the "products" table that doesn't have any corresponding rows (matched with product_id) in the "items" table. The culpr... ehrm, query: DELETE products FROM products LEFT JOIN items ON products.product_id = items.product_id WHERE items.product_id IS NULL; Now, my query has been running for 6 hours straight, and it's marked as "Sending data" in the process list. Any ideas? "products" has ~113.500 records while "items" has ~123.439. Table descriptions: PRODUCTS ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | product_id | int(10) unsigned | NO | PRI | | auto_increment | | product_retail_id | varchar(255) | NO | MUL | | | | manufactor_id | int(10) unsigned | NO | | 0 | | | product_description| varchar(255) | NO | | | | | product_comment| text | NO | | | | | product_internal_id| varchar(255) | NO | | | | | product_price1_percent | float| NO | | 0 | | | product_price2_percent | float| NO | | 0 | | | product_price3_percent | float| NO | | 0 | | | product_price1_amount | float| NO | | 0 | | | product_price2_amount | float| NO | | 0 | | | product_price3_amount | float| NO | | 0 | | | product_added | int(10) unsigned | NO | | 0 | | | product_url| varchar(255) | NO | | | | | product_ean| varchar(13) | NO | | | | ++--+--+-+-++ ITEMS: +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | item_id | int(10) unsigned | NO | PRI | | auto_increment | | product_id | varchar(45) | NO | MUL | 0 || | item_price | float| NO | | 0 || | item_stock | int(11) | NO | | 0 || | item_incoming| varchar(45) | NO | | || | item_updated | int(10) unsigned | NO | | 0 || | item_url | varchar(255) | NO | | || | supplier_id | int(10) unsigned | NO | | 0 || | item_internal_id | varchar(45) | NO | MUL | || | item_description | varchar(255) | NO | | || | item_weight | float| NO | | 0 || +--+--+--+-+-++ Best regards -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help on join query
Hi All, Given this table: +-+-+++- |sip_status | sip_method | sip_callid | username | fromtag| totag | time| timestamp | +-+-+++- |200| INVITE | [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:27:39 | 2006-06-19 05:27:39 | |200| ACK| [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:27:39 | 2006-06-19 05:27:39 | |200| INVITE | [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:27:59 | 2006-06-19 05:27:59 | |200| ACK| [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:28:00 | 2006-06-19 05:28:00 | |200| INVITE | [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:28:15 | 2006-06-19 05:28:15 | |200| ACK| [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:28:15 | 2006-06-19 05:28:15 | |200| INVITE | [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:28:16 | 2006-06-19 05:28:16 | |200| ACK| [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:28:16 | 2006-06-19 05:28:16 | |200| INVITE | [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:29:16 | 2006-06-19 05:29:16 | |200| ACK| [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:29:17 | 2006-06-19 05:29:17 | |406| REFER | [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:29:19 | 2006-06-19 05:29:19 | |200| BYE| [EMAIL PROTECTED] | 0061396962022 |3359683324-99483 | e8936439e4bdfb0co0 | 2006-06-19 13:29:19 | 2006-06-19 05:29:19 | |481| BYE| [EMAIL PROTECTED] | 5743 |e8936439e4bdfb0co0 | 3359683324-99483 | 2006-06-19 13:29:19 | 2006-06-19 05:29:19 | |200| INVITE | [EMAIL PROTECTED] | 5093 | 73af10c095f4a93do1 | 3359711691-612956 | 2006-06-19 21:20:25 | 2006-06-19 13:20:25 | |200| ACK| [EMAIL PROTECTED] | 5093 | 73af10c095f4a93do1 | 3359711691-612956 | 2006-06-19 21:20:25 | 2006-06-19 13:20:25 | |481| BYE| [EMAIL PROTECTED] | 5093 | 73af10c095f4a93do1 | 3359711691-612956 | 2006-06-19 21:22:11 | 2006-06-19 13:22:11 | |481| BYE| [EMAIL PROTECTED] | 0027164306000 | 3359711691-612956 | 73af10c095f4a93do1 | 2006-06-19 21:22:14 | 2006-06-19 13:22:14 | +--+--+++--- I tried this query: SELECT t1.sip_callid as callid, t1.username as username, t1.sip_method as t1meth, t2.sip_method as t2meth, t3.sip_method as t3meth, t1.time as start, t2.time as stop, TIMEDIFF(t2.time,t1.time) as timediff FROM acc AS t1 INNER JOIN acc AS t2 ON t1.sip_callid = t2.sip_callid AND ((t1.fromtag = t2.fromtag AND t1.totag = t2.totag) OR (t1.fromtag = t2.totag AND t1.totag = t2.fromtag)) LEFT JOIN acc AS t3 ON t1.sip_callid = t3.sip_callid AND ((t1.fromtag = t2.fromtag AND t1.totag = t2.totag) OR (t1.fromtag = t2.totag AND t1.totag = t2.fromtag)) AND t3.sip_method='INVITE' AND t1.time > t3.time WHERE t1.sip_method='INVITE' AND t2.sip_method='BYE' AND t3.sip_method IS NULL; but i still get this result +--+--++++-+-+--+ | callid | username | t1meth | t2meth | t3meth | start | stop| timediff | +--+--++++-+-+--+ | [EMAIL PROTECTED] | 5093 | INVITE | BYE| NULL | 2006-06-19 21:20:25 | 2006-06-19 21:22:11 | 00:01:46 | | [EMAIL PROTECTED] | 5093 | INVITE | BYE| NULL | 2006-06-19 21:20:25 | 2006-06-19 21:22:14 | 00:01:49 | | [EMAIL PROTECTED] | 5743 | INVITE | BYE| NULL | 2006-06-19 13:27:39 | 2006-06-19 13:29:19 | 00:01:40 | | [EMAIL PROTECTED] | 5743 | INVITE | BYE| NULL | 2006-06-19 13:27:39 | 2006-06-19 13:29:19 | 00:01:40 | +--+--++++-+-+--+ What query should I use to make it look like these: +--+--
Re: Help with a join query
Yoed Anis wrote: Hi all, I'm trying to do the following. I have three table: Table a has address information: address_id | City | State | Zip 1Austin TX 78758 2 Dallas TX 77000 3 Galveston TX 77550 Table b has information about the location: address_id | Location_id | Location_name 11The Place 12The Place Before 23A shop Table c has montlhy sales history Locationid | MonthYear | Sales 12005-01-01 299 12005-02-01100 12005-10-01300 22005-01-01 154 32005-10-1099 Not every location has sales information. I am trying to create a query where I can SELECT the Locationname, City, State, Zip, and the SUM(sales) if the place has sales. So far, despite playing around with joins for more hours than one should ever dedicated to the matter, I haven't been able to include SUM(sales) without excluding listings without sales. So far this is my best shot: SELECT locationname, city, state, zip, SUM(sales) as 'Sales' FROM a, b LEFT JOIN c ON (b.locationid = c.locationid) WHERE a.address_id = b.address_id AND monthyear > "2005-01-01" GROUP BY c.locationid SELECT locationname, city, state, zip, SUM(sales) as 'Sales' FROM a INNER JOIN b ON a.address_id = b.address_id LEFT JOIN c ON b.locationid = c.locationid and monthyear>"2005-01-10" GROUP BY c.locationid This however, will return only records with Sales and not those without it. I haven't been able to force adding empty rows from table c... Doing "AND c.locationid IS NULL" returns no results at all. Any help would GREATLY be appreciated!!! Thank you!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a join query
Hi all, I'm trying to do the following. I have three table: Table a has address information: address_id | City | State | Zip 1Austin TX 78758 2 Dallas TX 77000 3 Galveston TX 77550 Table b has information about the location: address_id | Location_id | Location_name 11The Place 12The Place Before 23A shop Table c has montlhy sales history Locationid | MonthYear | Sales 12005-01-01 299 12005-02-01100 12005-10-01300 22005-01-01 154 32005-10-1099 Not every location has sales information. I am trying to create a query where I can SELECT the Locationname, City, State, Zip, and the SUM(sales) if the place has sales. So far, despite playing around with joins for more hours than one should ever dedicated to the matter, I haven't been able to include SUM(sales) without excluding listings without sales. So far this is my best shot: SELECT locationname, city, state, zip, SUM(sales) as 'Sales' FROM a, b LEFT JOIN c ON (b.locationid = c.locationid) WHERE a.address_id = b.address_id AND monthyear > "2005-01-01" GROUP BY c.locationid This however, will return only records with Sales and not those without it. I haven't been able to force adding empty rows from table c... Doing "AND c.locationid IS NULL" returns no results at all. Any help would GREATLY be appreciated!!! Thank you!!
Re: a difficult join query question
Klemens Ullmann <[EMAIL PROTECTED]> wrote on 09/02/2005 07:37:43 AM: > hello! > > I've got two tables for an IT hardware inventory: > > ### table inventory: > invid model > --- > 1001 HP Notebook// no attributes > 1002 Dell Desktop // only one attribut 'pc-name' > 1003 Acer Laptop// attributes 'pc-name' & 'harddisk' > 1004 Apple iBook// only one attribut 'harddisk' > > ### table attributes: > id invid attribute value > - > 501 1002 pcname atpc01 > 502 1003 pcname atpc02 > 503 1003 harddisk 20GB > 504 1004 harddisk 40GB > > what I want is a list of all computers (without exeptions) in the > following form: > invid - modell - pcname > > my best guess zu date of a possible query is: > SELECT inventory.invid,inventory.model,attributes.value as pcname > FROM inventory > LEFT JOIN attributes ON (inventory.invid=attributes.invid) > WHERE attributes.attribute='pcname' or attributes.attribute is NULL; > > ### result: > invid model pcname > --- > 1001 HP Notebook NULL > 1002 Dell Desktop atpc01 > 1003 Acer Laptop atpc02 > > > now my problem are the missing computers which have an attribute, but > not a 'pc-name'-attribute. > (in the example above the missing iBook) > > thank you for suggestions how to solve the problem! > > have a nice day, > > klemens ullmann / vienna > That's a simple OUTER JOIN query, like you wrote, with one exception. By putting the checks for attributes.attribute='whatever' in the WHERE clause, you forced those values to be part of the results. This in effect changed your query into an INNER JOIN and eliminated all of the rows from `model` that didn't have those attributes. What you wanted to do is to add that comparison into the ON subclause of your JOIN clause like this: SELECT inventory.invid,inventory.model,attributes.value as pcname FROM inventory LEFT JOIN attributes ON inventory.invid=attributes.invid AND attributes.attribute='pcname' ; Putting it here made it a condition of the JOIN which meant that rows from the `attributes` table that were not 'pcname' rows would be excluded from the "right" side of the LEFT JOIN. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: a difficult join query question
Klemens Ullmann wrote: hello! I've got two tables for an IT hardware inventory: ### table inventory: invid model --- 1001 HP Notebook// no attributes 1002 Dell Desktop // only one attribut 'pc-name' 1003 Acer Laptop// attributes 'pc-name' & 'harddisk' 1004 Apple iBook// only one attribut 'harddisk' ### table attributes: id invid attribute value - 501 1002 pcname atpc01 502 1003 pcname atpc02 503 1003 harddisk 20GB 504 1004 harddisk 40GB what I want is a list of all computers (without exeptions) in the following form: invid - modell - pcname my best guess zu date of a possible query is: SELECT inventory.invid,inventory.model,attributes.value as pcname FROM inventory LEFT JOIN attributes ON (inventory.invid=attributes.invid) WHERE attributes.attribute='pcname' or attributes.attribute is NULL; ### result: invid model pcname --- 1001 HP Notebook NULL 1002 Dell Desktop atpc01 1003 Acer Laptop atpc02 now my problem are the missing computers which have an attribute, but not a 'pc-name'-attribute. (in the example above the missing iBook) thank you for suggestions how to solve the problem! have a nice day, klemens ullmann / vienna Quite a fun challenge! Using the test data you gave, this query works, assuming that you are running a version of MySQL that can do subqueries. SELECT inventory.invid , model , value AS pcname FROM inventory LEFT JOIN ( SELECT invid, value FROM attributes WHERE attribute='pcname' ) AS a USING(invid); +---+-++ | invid | model | pcname | +---+-++ | NULL | HP Notebook | NULL | | 1002 | Dell Desktop| atpc01 | | 1003 | Acer Laptop | atpc02 | | 1004 | Apple iBook | NULL | +---+-++ 4 rows in set (0.00 sec) Regards, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a difficult join query question
Hi, I would rather put the attributes.attribute='pcname' in the join clause It should look something like this SELECT inventory.invid,inventory.model,attributes.value as pcname FROM inventory LEFT JOIN attributes ON (inventory.invid=attributes.invid and attributes.attribute='pcname' ); HTH -- Dobromir Velev [EMAIL PROTECTED] http://www.websitepulse.com/ On Friday 02 September 2005 14:37, Klemens Ullmann wrote: > hello! > > I've got two tables for an IT hardware inventory: > > ### table inventory: > invid model > --- > 1001 HP Notebook// no attributes > 1002 Dell Desktop // only one attribut 'pc-name' > 1003 Acer Laptop// attributes 'pc-name' & 'harddisk' > 1004 Apple iBook// only one attribut 'harddisk' > > ### table attributes: > id invid attribute value > - > 501 1002 pcname atpc01 > 502 1003 pcname atpc02 > 503 1003 harddisk 20GB > 504 1004 harddisk 40GB > > what I want is a list of all computers (without exeptions) in the > following form: > invid - modell - pcname > > my best guess zu date of a possible query is: > SELECT inventory.invid,inventory.model,attributes.value as pcname > FROM inventory > LEFT JOIN attributes ON (inventory.invid=attributes.invid) > WHERE attributes.attribute='pcname' or attributes.attribute is NULL; > > ### result: > invid model pcname > --- > 1001 HP Notebook NULL > 1002 Dell Desktop atpc01 > 1003 Acer Laptop atpc02 > > > now my problem are the missing computers which have an attribute, but > not a 'pc-name'-attribute. > (in the example above the missing iBook) > > thank you for suggestions how to solve the problem! > > have a nice day, > > klemens ullmann / vienna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a difficult join query question
hello! I've got two tables for an IT hardware inventory: ### table inventory: invid model --- 1001 HP Notebook// no attributes 1002 Dell Desktop // only one attribut 'pc-name' 1003 Acer Laptop// attributes 'pc-name' & 'harddisk' 1004 Apple iBook// only one attribut 'harddisk' ### table attributes: id invid attribute value - 501 1002 pcname atpc01 502 1003 pcname atpc02 503 1003 harddisk 20GB 504 1004 harddisk 40GB what I want is a list of all computers (without exeptions) in the following form: invid - modell - pcname my best guess zu date of a possible query is: SELECT inventory.invid,inventory.model,attributes.value as pcname FROM inventory LEFT JOIN attributes ON (inventory.invid=attributes.invid) WHERE attributes.attribute='pcname' or attributes.attribute is NULL; ### result: invid model pcname --- 1001 HP Notebook NULL 1002 Dell Desktop atpc01 1003 Acer Laptop atpc02 now my problem are the missing computers which have an attribute, but not a 'pc-name'-attribute. (in the example above the missing iBook) thank you for suggestions how to solve the problem! have a nice day, klemens ullmann / vienna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOIN QUERY -> UPDATE ... help?!
Brendan Gogarty wrote: > We are running mysql 3.23.58 and I want to do a query with joins > from two tables and then insert the results into the column of a third. [snip] Shawn Green wrote: > Start from here: > http://dev.mysql.com/doc/mysql/en/update.html > > Updates *are* allowed to use JOINED tables as the "thing to be > updated". [snip] Michael wrote: [snip] > From the manual page Shawn cites: "Starting with MySQL 4.0.4, you can also > perform UPDATE operations that cover multiple tables." So, yes, it's a > version issue. So, to return to my original question ... Is there a workaround or a way of updating from a joined query in <4mysql? I thought perhaps creating a temporary table, but even this doesn't seem to work? There MUST be a way of doing this ... Its an administrative nightmare. Regards, Brendan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOIN QUERY -> UPDATE ... help?!
Multi Table UPDATES are first supported in 4.0.x -Original Message- From: Brendan Gogarty [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 09, 2005 12:16 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: JOIN QUERY -> UPDATE ... help?! "Brendan Gogarty" <[EMAIL PROTECTED]> wrote on 08/09/2005 05:30:51 AM: > Hi, > We are running mysql 3.23.58 and I want to do a query with joins > from two tables and then insert the > results into the column of a third. This appears to be harder than I > realised with this version of mysql and I am banging my head against a > wall. Please Help! > ok first query. [snip] > any ideas? > > Start from here: http://dev.mysql.com/doc/mysql/en/update.html Updates *are* allowed to use JOINED tables as the "thing to be updated". Which means that an UPDATE statement can look VERY MUCH like a SELECT statement turned on it's head. In your case, I think you are trying to figure out how to flip this: select link_ID,of_group FROM page_elements LEFT JOIN pages ON page_elements.link_to=pages.page_ID LEFT JOIN links_DB_bk ON page_elements.link_ID=links_DB_bk.link_ID WHERE content_type='text' into this (while adding the `links_db` table into the mix: UPDATE links_db INNER JOIN page_elements ON page_elements.link_ID=links_DB.link_ID LEFT JOIN pages ON page_elements.link_to=pages.page_ID LEFT JOIN links_DB_bk ON page_elements.link_ID=links_DB_bk.link_ID SET <*** see note***> WHERE content_type='text'; *** note: your SET clause can reference ANY column from ANY table defined in your UPDATE clause. You are not limited to just changing one table at a time. Just make sure you properly identify the columns you want to get data from and which ones you want to set. Now, you didn't say exactly what you wanted to update with what or I would have filled in more of the SET clause. If you want to flip a SELECT ... GROUP BY statement into an UPDATE statement, you have to go through a temporary table first. That is because the GROUP BY eliminates any one-to-one row-to-value mappings 99.9% of the time. There is no UPDATE ... GROUP BY command for any RDBMS that I know of. However, if you save the results of the SELECT...GROUP BY statement, you can JOIN to that table in an UPDATE statement just like any other data. Let me know if you run into any more issues and I can help you work it out. Hi Shaun, I'm afraid after a few hours of testing various things it doesn't work. I am pretty sure its a version issue as even the simplest query such as UPDATE links_DB LEFT JOIN page_elements SET links_DB.in_group=0 brings up an error ' MySQL said: You have an error in your SQL syntax near 'LEFT JOIN page_elements SET links_DB.in_group = 0' at line 1 ' cheers, brendan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN QUERY -> UPDATE ... help?!
Brendan Gogarty wrote: > We are running mysql 3.23.58 and I want to do a query with joins > from two tables and then insert the results into the column of a third. Shawn Green wrote: Start from here: http://dev.mysql.com/doc/mysql/en/update.html Updates *are* allowed to use JOINED tables as the "thing to be updated". Which means that an UPDATE statement can look VERY MUCH like a SELECT statement turned on it's head. In your case, I think you are trying to figure out how to flip this: Brendan Gogarty wrote: Hi Shaun, I'm afraid after a few hours of testing various things it doesn't work. I am pretty sure its a version issue as even the simplest query such as UPDATE links_DB LEFT JOIN page_elements SET links_DB.in_group=0 brings up an error MySQL said: You have an error in your SQL syntax near 'LEFT JOIN page_elements SET links_DB.in_group = 0' at line 1 cheers, brendan. From the manual page Shawn cites: "Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables." So, yes, it's a version issue. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOIN QUERY -> UPDATE ... help?!
"Brendan Gogarty" <[EMAIL PROTECTED]> wrote on 08/09/2005 05:30:51 AM: > Hi, > We are running mysql 3.23.58 and I want to do a query with joins > from two tables and then insert the > results into the column of a third. This appears to be harder than I > realised with this version of mysql and I am banging my head against a > wall. Please Help! > ok first query. [snip] > any ideas? > > Start from here: http://dev.mysql.com/doc/mysql/en/update.html Updates *are* allowed to use JOINED tables as the "thing to be updated". Which means that an UPDATE statement can look VERY MUCH like a SELECT statement turned on it's head. In your case, I think you are trying to figure out how to flip this: select link_ID,of_group FROM page_elements LEFT JOIN pages ON page_elements.link_to=pages.page_ID LEFT JOIN links_DB_bk ON page_elements.link_ID=links_DB_bk.link_ID WHERE content_type='text' into this (while adding the `links_db` table into the mix: UPDATE links_db INNER JOIN page_elements ON page_elements.link_ID=links_DB.link_ID LEFT JOIN pages ON page_elements.link_to=pages.page_ID LEFT JOIN links_DB_bk ON page_elements.link_ID=links_DB_bk.link_ID SET <*** see note***> WHERE content_type='text'; *** note: your SET clause can reference ANY column from ANY table defined in your UPDATE clause. You are not limited to just changing one table at a time. Just make sure you properly identify the columns you want to get data from and which ones you want to set. Now, you didn't say exactly what you wanted to update with what or I would have filled in more of the SET clause. If you want to flip a SELECT ... GROUP BY statement into an UPDATE statement, you have to go through a temporary table first. That is because the GROUP BY eliminates any one-to-one row-to-value mappings 99.9% of the time. There is no UPDATE ... GROUP BY command for any RDBMS that I know of. However, if you save the results of the SELECT...GROUP BY statement, you can JOIN to that table in an UPDATE statement just like any other data. Let me know if you run into any more issues and I can help you work it out. Hi Shaun, I'm afraid after a few hours of testing various things it doesn't work. I am pretty sure its a version issue as even the simplest query such as UPDATE links_DB LEFT JOIN page_elements SET links_DB.in_group=0 brings up an error ' MySQL said: You have an error in your SQL syntax near 'LEFT JOIN page_elements SET links_DB.in_group = 0' at line 1 ' cheers, brendan.
Re: JOIN QUERY -> UPDATE ... help?!
"Brendan Gogarty" <[EMAIL PROTECTED]> wrote on 08/09/2005 05:30:51 AM: > Hi, > We are running mysql 3.23.58 and I want to do a query with joins > from two tables and then insert the > results into the column of a third. This appears to be harder than I > realised with this version of mysql and I am banging my head against a > wall. Please Help! > ok first query. > - > "select link_ID,of_group > FROM > page_elements > LEFT JOIN pages > ON page_elements.link_to=pages.page_ID > LEFT JOIN links_DB_bk > ON page_elements.link_ID=links_DB_bk.link_ID > WHERE content_type='text'" > - > > Now I want to update table 3 (links_DB) using > page_elements.link_ID=links_DB.link_ID > > usual update query doesn't seem to work in this circumstance > .. > any ideas? > > Start from here: http://dev.mysql.com/doc/mysql/en/update.html Updates *are* allowed to use JOINED tables as the "thing to be updated". Which means that an UPDATE statement can look VERY MUCH like a SELECT statement turned on it's head. In your case, I think you are trying to figure out how to flip this: select link_ID,of_group FROM page_elements LEFT JOIN pages ON page_elements.link_to=pages.page_ID LEFT JOIN links_DB_bk ON page_elements.link_ID=links_DB_bk.link_ID WHERE content_type='text' into this (while adding the `links_db` table into the mix: UPDATE links_db INNER JOIN page_elements ON page_elements.link_ID=links_DB.link_ID LEFT JOIN pages ON page_elements.link_to=pages.page_ID LEFT JOIN links_DB_bk ON page_elements.link_ID=links_DB_bk.link_ID SET <*** see note***> WHERE content_type='text'; *** note: your SET clause can reference ANY column from ANY table defined in your UPDATE clause. You are not limited to just changing one table at a time. Just make sure you properly identify the columns you want to get data from and which ones you want to set. Now, you didn't say exactly what you wanted to update with what or I would have filled in more of the SET clause. If you want to flip a SELECT ... GROUP BY statement into an UPDATE statement, you have to go through a temporary table first. That is because the GROUP BY eliminates any one-to-one row-to-value mappings 99.9% of the time. There is no UPDATE ... GROUP BY command for any RDBMS that I know of. However, if you save the results of the SELECT...GROUP BY statement, you can JOIN to that table in an UPDATE statement just like any other data. Let me know if you run into any more issues and I can help you work it out. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
JOIN QUERY -> UPDATE ... help?!
Hi, We are running mysql 3.23.58 and I want to do a query with joins from two tables and then insert the results into the column of a third. This appears to be harder than I realised with this version of mysql and I am banging my head against a wall. Please Help! ok first query. - "select link_ID,of_group FROM page_elements LEFT JOIN pages ON page_elements.link_to=pages.page_ID LEFT JOIN links_DB_bk ON page_elements.link_ID=links_DB_bk.link_ID WHERE content_type='text'" - Now I want to update table 3 (links_DB) using page_elements.link_ID=links_DB.link_ID usual update query doesn't seem to work in this circumstance .. any ideas?
Re: Help with a JOIN query please
"shaun thornburgh" <[EMAIL PROTECTED]> wrote on 03/17/2005 06:46:22 PM: > Hi, > > I have ( among others ) three tables in my database: Claims, Expenses and > Mileage. A claim can contain many expense entries and many mileage entries. > I am using the follwing query to show the total expenses and mileage per > claim for a particulare user: > > SELECT C.*, SUM(E.Amount) AS Amount, SUM(M.Mileage) AS Mileage > FROM Claims C > LEFT JOIN Expenses E ON E.Claim_ID = C.Claim_ID > LEFT JOIN Mileage M ON M.Claim_ID = C.Claim_ID > WHERE C.Claimant_ID = '1' > GROUP BY C.Claim_ID > > The problem is if there are two mileage entries and one expense entry the > expense total is doubled (and vice versa), can some explain this to me > please? > > Thanks for your help. > > TABLE DEFINITIONS: > > mysql> desc Claims; > +-+-+--+-+-++ > | Field | Type| Null | Key | Default | Extra | > +-+-+--+-+-++ > | Claim_ID| int(11) | | PRI | NULL| auto_increment | > | Claimant_ID | int(11) | | | 0 | | > | Description | varchar(50) | | | | | > | Status | varchar(50) | YES | | Open| | > | Submission_Date | datetime| YES | | NULL| | > | Approval_Date | datetime| YES | | NULL| | > | Approver_ID | int(11) | YES | | NULL| | > +-+-+--+-+-++ > 7 rows in set (0.00 sec) > > mysql> desc Expenses; > +-+---+--+-+- > ++ > | Field | Type | Null | Key | Default | Extra > | > +-+---+--+-+- > ++ > | Expense_ID | int(11) | | PRI | NULL| > auto_increment | > | Claim_ID| int(11) | YES | | NULL| > | > | Description | varchar(50) | YES | | NULL| > | > | Expense_Category_ID | int(11) | YES | | NULL| > | > | Insertion_Date | date | YES | | NULL| > | > | Project_ID | int(11) | YES | | NULL| > | > | Amount | decimal(10,2) | YES | | NULL| > | > | Rate_ID | int(11) | YES | | NULL| > | > | Supplier_ID | int(11) | YES | | NULL| > | > | Receipt | varchar(10) | YES | | NULL| > | > | Receipt_Date| varchar(10) | YES | | NULL| > | > | VAT_Receipt | varchar(10) | YES | | NULL| > | > | VAT_Amount | decimal(10,2) | YES | | NULL| > | > +-+---+--+-+- > ++ > 13 rows in set (0.00 sec) > > mysql> desc Mileage; > ++--+--+-+++ > | Field | Type | Null | Key | Default| Extra | > ++--+--+-+++ > | Mileage_ID | int(11) | | PRI | NULL | auto_increment | > | Claim_ID | int(11) | | | 0 | | > | Project_ID | int(11) | | | 0 | | > | Insertion_Date | date | | | -00-00 | | > | Description| varchar(255) | | || | > | Start_Mileage | int(11) | | | 0 | | > | End_Mileage| int(11) | | | 0 | | > | Mileage| int(11) | | | 0 | | > ++--+--+-+++ > 8 rows in set (0.00 sec) > > mysql> > It's happening because you are joining two child tables at once. Maybe a sequence of diagrams will help. This is what happens within the DB engine whenever you make a JOIN. Since you were nice enough to post your table structures (THANK YOU!!!) I will use your tables and problem query as examples. Something simple: SELECT ... FROM Claims C LEFT JOIN Expenses E ON E.Claim_ID = C.Claim_ID; Step one of any query is to identify all of your source data. In this case, we are combining the records of two tables so that it behaves like a single larger table. This internally created, virtual table represents all possible combinations of rows that satisfy the ON conditions of your JOIN clauses. INTERNAL VIRTUAL TABLE A +---+-+ | all columns of Claims | all columns of Expenses | +---+-+ | Claims row 1 | all null values | +---+-+ | Claims row 2 | Expenses row 246| +---+-+ | Claims row 2 | Expenses row
Re: Help with a JOIN query please
Hello shaun, May be with subselects you can do what you want: SELECT C.*,(select SUM(E.Amount) from Expenses E where E.Claim_ID = C.Claim_ID)AS Amount, (select SUM(M.Mileage) from Mileage M where M.Claim_ID = C.Claim_ID) AS Mileage FROM Claims C WHERE C.Claimant_ID = '1' st> Hi, st> I have ( among others ) three tables in my database: Claims, Expenses and st> Mileage. A claim can contain many expense entries and many mileage entries. st> I am using the follwing query to show the total expenses and mileage per st> claim for a particulare user: st> SELECT C.*, SUM(E.Amount) AS Amount, SUM(M.Mileage) AS Mileage st> FROM Claims C st> LEFT JOIN Expenses E ON E.Claim_ID = C.Claim_ID st> LEFT JOIN Mileage M ON M.Claim_ID = C.Claim_ID st> WHERE C.Claimant_ID = '1' st> GROUP BY C.Claim_ID st> The problem is if there are two mileage entries and one expense entry the st> expense total is doubled (and vice versa), can some explain this to me st> please? st> Thanks for your help. st> TABLE DEFINITIONS: mysql>> desc Claims; st> +-+-+--+-+-++ st> | Field | Type| Null | Key | Default | Extra | st> +-+-+--+-+-++ st> | Claim_ID| int(11) | | PRI | NULL| auto_increment | st> | Claimant_ID | int(11) | | | 0 | st> | Description | varchar(50) | | | | st> | Status | varchar(50) | YES | | Open| st> | Submission_Date | datetime| YES | | NULL| st> | Approval_Date | datetime| YES | | NULL| st> | Approver_ID | int(11) | YES | | NULL| st> +-+-+--+-+-++ st> 7 rows in set (0.00 sec) mysql>> desc Expenses; st> +-+---+--+-+-++ st> | Field | Type | Null | Key | Default | Extra st> | st> +-+---+--+-+-++ st> | Expense_ID | int(11) | | PRI | NULL| st> auto_increment | st> | Claim_ID| int(11) | YES | | NULL| st> | st> | Description | varchar(50) | YES | | NULL| st> | st> | Expense_Category_ID | int(11) | YES | | NULL| st> | st> | Insertion_Date | date | YES | | NULL| st> | st> | Project_ID | int(11) | YES | | NULL| st> | st> | Amount | decimal(10,2) | YES | | NULL| st> | st> | Rate_ID | int(11) | YES | | NULL| st> | st> | Supplier_ID | int(11) | YES | | NULL| st> | st> | Receipt | varchar(10) | YES | | NULL| st> | st> | Receipt_Date| varchar(10) | YES | | NULL| st> | st> | VAT_Receipt | varchar(10) | YES | | NULL| st> | st> | VAT_Amount | decimal(10,2) | YES | | NULL| st> | st> +-+---+--+-+-++ st> 13 rows in set (0.00 sec) mysql>> desc Mileage; st> ++--+--+-+++ st> | Field | Type | Null | Key | Default| Extra | st> ++--+--+-+++ st> | Mileage_ID | int(11) | | PRI | NULL | auto_increment | st> | Claim_ID | int(11) | | | 0 | st> | Project_ID | int(11) | | | 0 | st> | Insertion_Date | date | | | -00-00 | st> | Description| varchar(255) | | || st> | Start_Mileage | int(11) | | | 0 | st> | End_Mileage| int(11) | | | 0 | st> | Mileage| int(11) | | | 0 | st> ++--+--+-+++ st> 8 rows in set (0.00 sec) mysql>> -- Best regards, Krasimir_Slaveykov, 18 Ìàðò 2005 ã., 12:54:56 mailto: [EMAIL PROTECTED] [EMAIL PROTECTED] |-| |/ * * *** * ** /| | *** *** *** *** *** *** ** *** //| |/// *** *** * * *** **** *** ///| |// *** *** *** *** *** *** ** ****** | |/ *** *** *** * *** /| |--- www.office1.bg --| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a JOIN query please
Hi, I have ( among others ) three tables in my database: Claims, Expenses and Mileage. A claim can contain many expense entries and many mileage entries. I am using the follwing query to show the total expenses and mileage per claim for a particulare user: SELECT C.*, SUM(E.Amount) AS Amount, SUM(M.Mileage) AS Mileage FROM Claims C LEFT JOIN Expenses E ON E.Claim_ID = C.Claim_ID LEFT JOIN Mileage M ON M.Claim_ID = C.Claim_ID WHERE C.Claimant_ID = '1' GROUP BY C.Claim_ID The problem is if there are two mileage entries and one expense entry the expense total is doubled (and vice versa), can some explain this to me please? Thanks for your help. TABLE DEFINITIONS: mysql> desc Claims; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Claim_ID| int(11) | | PRI | NULL| auto_increment | | Claimant_ID | int(11) | | | 0 || | Description | varchar(50) | | | || | Status | varchar(50) | YES | | Open|| | Submission_Date | datetime| YES | | NULL|| | Approval_Date | datetime| YES | | NULL|| | Approver_ID | int(11) | YES | | NULL|| +-+-+--+-+-++ 7 rows in set (0.00 sec) mysql> desc Expenses; +-+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-++ | Expense_ID | int(11) | | PRI | NULL| auto_increment | | Claim_ID| int(11) | YES | | NULL| | | Description | varchar(50) | YES | | NULL| | | Expense_Category_ID | int(11) | YES | | NULL| | | Insertion_Date | date | YES | | NULL| | | Project_ID | int(11) | YES | | NULL| | | Amount | decimal(10,2) | YES | | NULL| | | Rate_ID | int(11) | YES | | NULL| | | Supplier_ID | int(11) | YES | | NULL| | | Receipt | varchar(10) | YES | | NULL| | | Receipt_Date| varchar(10) | YES | | NULL| | | VAT_Receipt | varchar(10) | YES | | NULL| | | VAT_Amount | decimal(10,2) | YES | | NULL| | +-+---+--+-+-++ 13 rows in set (0.00 sec) mysql> desc Mileage; ++--+--+-+++ | Field | Type | Null | Key | Default| Extra | ++--+--+-+++ | Mileage_ID | int(11) | | PRI | NULL | auto_increment | | Claim_ID | int(11) | | | 0 || | Project_ID | int(11) | | | 0 || | Insertion_Date | date | | | -00-00 || | Description| varchar(255) | | ||| | Start_Mileage | int(11) | | | 0 || | End_Mileage| int(11) | | | 0 || | Mileage| int(11) | | | 0 || ++--+--+-+++ 8 rows in set (0.00 sec) mysql> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join query
[EMAIL PROTECTED] wrote: Hello, I made little board, where all postings are in one table: Field Type --- topic_id int(6) unsigned topic_pid int(6) unsigned authorvarchar(50) mail varchar(255) posting_time timestamp(14) ipvarchar(15) body text reg_user tinyint(1) topic_pid shows what kind of topic it is, if 0 then its new thread else reply for exist one. Now i want to sort threads by last posting_time but because i havent mysql 4.1 in server then i cant use sub query. Is it possible to get it with joins and if yes then how? -- Lauri You need to break it down into two steps: First get the latest posting times, holding the result in a temporary table, then get all the posts properly ordered. Something like this (I'm using 'board' as the name of your table): CREATE TEMPORARY TABLE pt ( topic_pid int unsigned, latesttimestamp index (latest, topic_pid)); LOCK TABLES board READ; INSERT INTO pt SELECT topic_pid, MAX(posting_time) AS latest FROM board GROUP BY topic_pid; SELECT board.topic_id, board.topic_pid, board.author, board.posting_time FROM pt JOIN board USING (topic_pid) ORDER BY pt.latest DESC, pt.topic_pid DESC, board.posting_time DESC; UNLOCK TABLES; DROP TABLE tmp; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
join query
Hello, I made little board, where all postings are in one table: Field Type --- topic_id int(6) unsigned topic_pid int(6) unsigned authorvarchar(50) mail varchar(255) posting_time timestamp(14) ipvarchar(15) body text reg_user tinyint(1) topic_pid shows what kind of topic it is, if 0 then its new thread else reply for exist one. Now i want to sort threads by last posting_time but because i havent mysql 4.1 in server then i cant use sub query. Is it possible to get it with joins and if yes then how? -- Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a join query please!
Hi Shawn,
Sorry for the confusions! The booking in question is where B.Booking_ID = x
as in the original query:
SELECT U.User_ID, U.User_Firstname, U.User_Lastname
FROM Allocations A, Users U
LEFT JOIN Bookings B
ON B.User_ID = U.User_ID
AND B.Booking_ID = 4512
WHERE U.User_ID = A.User_ID
AND A.Project_ID = 11
ORDER BY User_Firstname;
Thanks,
Shaun
From: [EMAIL PROTECTED]
To: "shaun thornburgh" <[EMAIL PROTECTED]>
CC: mysql@lists.mysql.com,[EMAIL PROTECTED]
Subject: Re: Help with a join query please!
Date: Wed, 29 Dec 2004 15:58:43 -0500
"shaun thornburgh" <[EMAIL PROTECTED]> wrote on 12/29/2004
03:00:08 PM:
> Hi Shawn,
>
> Thanks for your reply, but your query seems to return everyone allocated
to
> that project plus everyone who has ever been booked on that project.
What I
> need is everyone who is currently allocated to that project and the
staff
> member who has been booked for the booking in question whether they have
> been allocated to the project or not.
How can I determine the "booking in question"? What confluence of values
makes a "generic" booking the "booking in question". If you could walk me
through the process of finding it manually ("compare field x on table y to
field z on table bb then go over to table c and look at ...") I can get
you the data you need.
>
> In reply to your quesion bookings don't relate to allocations directly.
A
> booking relates to a project by Project_ID and to a user by User_ID.
> Allocations contains both of these fields to determine who is
'Allocated' to
> a particular project.
>
> Thanks again for your help!
>
any time :-)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
>
> >From: [EMAIL PROTECTED]
> >To: "shaun thornburgh" <[EMAIL PROTECTED]>
> >CC: mysql@lists.mysql.com,[EMAIL PROTECTED]
> >Subject: Re: Help with a join query please!
> >Date: Thu, 23 Dec 2004 10:14:24 -0500
> >
> >So -- what's the field that relates a booking to an allocation? Do they
> >share a project_ID or what? If they do, you might try this:
> >
> >SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname
> >FROM Users U
> >LEFT JOIN Allocations A
> > on A.User_ID = U.User_ID
> >LEFT JOIN Bookings B
> > ON B.User_ID = U.User_ID
> >WHERE A.Project_ID = '11'
> >OR B.Project_ID = '11'
> >ORDER BY User_Firstname;
> >
> >
> >A pet peeve of mine is when people 'quote' NUMBERS. According to the
> >extremely well written manual, you only need to quote STRING values and
> >DATETIME values. Unless the columns Project_ID and Booking_ID are some
> >form of STRING column (CHAR, VARCHAR, TEXT, etc.) you don't need to
quote
> >their values in queries. It forces the query engine to perform an
> >unnecessary internal type conversion. Here is what I think your query
> >should look like:
> >
> >SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname
> >FROM Users U
> >LEFT JOIN Allocations A
> > on A.User_ID = U.User_ID
> >LEFT JOIN Bookings B
> > ON B.User_ID = U.User_ID
> >WHERE A.Project_ID = 11
> >OR B.Project_ID = 11
> >ORDER BY User_Firstname;
> >
> >
> >I used SELECT DISTINCT so that in the event that someone was both
BOOKED
> >and ALLOCATED to the same project, you only got them listed once.
> >
> >Shawn Green
> >Database Administrator
> >Unimin Corporation - Spruce Pine
> >
> >"shaun thornburgh" <[EMAIL PROTECTED]> wrote on 12/23/2004
> >08:37:37 AM:
> >
> > > Hi,
> > >
> > > Thanks for your reply but that produces exactly the same result...
> > >
> > > Any ideas?
> > >
> > > >From: Sasha Pachev <[EMAIL PROTECTED]>
> > > >To: shaun thornburgh <[EMAIL PROTECTED]>
> > > >CC: mysql@lists.mysql.com
> > > >Subject: Re: Help with a join query please!
> > > >Date: Tue, 21 Dec 2004 14:57:43 -0700
> > > >
> > > >shaun thornburgh wrote:
> > > >>Hi,
> > > >>
> > > >>I have (among others) three tables in my database that i am
struggling
> >
> > > >>with a join query on. The database holds dates for Bookings. If
Users
> >are
> > > >>Allocated to a particular Project they can be booked. However if a
> >user is
> > > >>booked but then unallocated I want to be able to display all
peolple
> > > >>allocated to that project plus the person originally booked. Here
are
&
Re: Help with a join query please!
"shaun thornburgh" <[EMAIL PROTECTED]> wrote on 12/29/2004
03:00:08 PM:
> Hi Shawn,
>
> Thanks for your reply, but your query seems to return everyone allocated
to
> that project plus everyone who has ever been booked on that project.
What I
> need is everyone who is currently allocated to that project and the
staff
> member who has been booked for the booking in question whether they have
> been allocated to the project or not.
How can I determine the "booking in question"? What confluence of values
makes a "generic" booking the "booking in question". If you could walk me
through the process of finding it manually ("compare field x on table y to
field z on table bb then go over to table c and look at ...") I can get
you the data you need.
>
> In reply to your quesion bookings don't relate to allocations directly.
A
> booking relates to a project by Project_ID and to a user by User_ID.
> Allocations contains both of these fields to determine who is
'Allocated' to
> a particular project.
>
> Thanks again for your help!
>
any time :-)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
>
> >From: [EMAIL PROTECTED]
> >To: "shaun thornburgh" <[EMAIL PROTECTED]>
> >CC: mysql@lists.mysql.com,[EMAIL PROTECTED]
> >Subject: Re: Help with a join query please!
> >Date: Thu, 23 Dec 2004 10:14:24 -0500
> >
> >So -- what's the field that relates a booking to an allocation? Do they
> >share a project_ID or what? If they do, you might try this:
> >
> >SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname
> >FROM Users U
> >LEFT JOIN Allocations A
> > on A.User_ID = U.User_ID
> >LEFT JOIN Bookings B
> > ON B.User_ID = U.User_ID
> >WHERE A.Project_ID = '11'
> >OR B.Project_ID = '11'
> >ORDER BY User_Firstname;
> >
> >
> >A pet peeve of mine is when people 'quote' NUMBERS. According to the
> >extremely well written manual, you only need to quote STRING values and
> >DATETIME values. Unless the columns Project_ID and Booking_ID are some
> >form of STRING column (CHAR, VARCHAR, TEXT, etc.) you don't need to
quote
> >their values in queries. It forces the query engine to perform an
> >unnecessary internal type conversion. Here is what I think your query
> >should look like:
> >
> >SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname
> >FROM Users U
> >LEFT JOIN Allocations A
> > on A.User_ID = U.User_ID
> >LEFT JOIN Bookings B
> > ON B.User_ID = U.User_ID
> >WHERE A.Project_ID = 11
> >OR B.Project_ID = 11
> >ORDER BY User_Firstname;
> >
> >
> >I used SELECT DISTINCT so that in the event that someone was both
BOOKED
> >and ALLOCATED to the same project, you only got them listed once.
> >
> >Shawn Green
> >Database Administrator
> >Unimin Corporation - Spruce Pine
> >
> >"shaun thornburgh" <[EMAIL PROTECTED]> wrote on 12/23/2004
> >08:37:37 AM:
> >
> > > Hi,
> > >
> > > Thanks for your reply but that produces exactly the same result...
> > >
> > > Any ideas?
> > >
> > > >From: Sasha Pachev <[EMAIL PROTECTED]>
> > > >To: shaun thornburgh <[EMAIL PROTECTED]>
> > > >CC: mysql@lists.mysql.com
> > > >Subject: Re: Help with a join query please!
> > > >Date: Tue, 21 Dec 2004 14:57:43 -0700
> > > >
> > > >shaun thornburgh wrote:
> > > >>Hi,
> > > >>
> > > >>I have (among others) three tables in my database that i am
struggling
> >
> > > >>with a join query on. The database holds dates for Bookings. If
Users
> >are
> > > >>Allocated to a particular Project they can be booked. However if a
> >user is
> > > >>booked but then unallocated I want to be able to display all
peolple
> > > >>allocated to that project plus the person originally booked. Here
are
> >my
> > > >>efforts so far:
> > > >>
> > > >>SELECT U.User_ID, U.User_Firstname, U.User_Lastname
> > > >>FROM Allocations A, Users U
> > > >>LEFT JOIN Bookings B
> > > >>ON B.User_ID = U.User_ID
> > > >>AND B.Booking_ID = '4512'
> > > >>WHERE U.User_ID = A.User_ID
> > > >>AND A.Project_ID = '11'
> > > >>ORDER BY User_Firstname;
> > > >
> > > >Shaun:
> > > >
> > > >If I understand the problem right, it sounds like you are missing
AND
> > > >B.Bookings_ID is NULL in the where clause.
> > > >
> > > >
> > > >
> > > >--
> > > >Sasha Pachev
> > > >Create online surveys at http://www.surveyz.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: Help with a join query please!
Hi Shawn, Thanks for your reply, but your query seems to return everyone allocated to that project plus everyone who has ever been booked on that project. What I need is everyone who is currently allocated to that project and the staff member who has been booked for the booking in question whether they have been allocated to the project or not. In reply to your quesion bookings don't relate to allocations directly. A booking relates to a project by Project_ID and to a user by User_ID. Allocations contains both of these fields to determine who is 'Allocated' to a particular project. Thanks again for your help! From: [EMAIL PROTECTED] To: "shaun thornburgh" <[EMAIL PROTECTED]> CC: mysql@lists.mysql.com,[EMAIL PROTECTED] Subject: Re: Help with a join query please! Date: Thu, 23 Dec 2004 10:14:24 -0500 So -- what's the field that relates a booking to an allocation? Do they share a project_ID or what? If they do, you might try this: SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U LEFT JOIN Allocations A on A.User_ID = U.User_ID LEFT JOIN Bookings B ON B.User_ID = U.User_ID WHERE A.Project_ID = '11' OR B.Project_ID = '11' ORDER BY User_Firstname; A pet peeve of mine is when people 'quote' NUMBERS. According to the extremely well written manual, you only need to quote STRING values and DATETIME values. Unless the columns Project_ID and Booking_ID are some form of STRING column (CHAR, VARCHAR, TEXT, etc.) you don't need to quote their values in queries. It forces the query engine to perform an unnecessary internal type conversion. Here is what I think your query should look like: SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U LEFT JOIN Allocations A on A.User_ID = U.User_ID LEFT JOIN Bookings B ON B.User_ID = U.User_ID WHERE A.Project_ID = 11 OR B.Project_ID = 11 ORDER BY User_Firstname; I used SELECT DISTINCT so that in the event that someone was both BOOKED and ALLOCATED to the same project, you only got them listed once. Shawn Green Database Administrator Unimin Corporation - Spruce Pine "shaun thornburgh" <[EMAIL PROTECTED]> wrote on 12/23/2004 08:37:37 AM: > Hi, > > Thanks for your reply but that produces exactly the same result... > > Any ideas? > > >From: Sasha Pachev <[EMAIL PROTECTED]> > >To: shaun thornburgh <[EMAIL PROTECTED]> > >CC: mysql@lists.mysql.com > >Subject: Re: Help with a join query please! > >Date: Tue, 21 Dec 2004 14:57:43 -0700 > > > >shaun thornburgh wrote: > >>Hi, > >> > >>I have (among others) three tables in my database that i am struggling > >>with a join query on. The database holds dates for Bookings. If Users are > >>Allocated to a particular Project they can be booked. However if a user is > >>booked but then unallocated I want to be able to display all peolple > >>allocated to that project plus the person originally booked. Here are my > >>efforts so far: > >> > >>SELECT U.User_ID, U.User_Firstname, U.User_Lastname > >>FROM Allocations A, Users U > >>LEFT JOIN Bookings B > >>ON B.User_ID = U.User_ID > >>AND B.Booking_ID = '4512' > >>WHERE U.User_ID = A.User_ID > >>AND A.Project_ID = '11' > >>ORDER BY User_Firstname; > > > >Shaun: > > > >If I understand the problem right, it sounds like you are missing AND > >B.Bookings_ID is NULL in the where clause. > > > > > > > >-- > >Sasha Pachev > >Create online surveys at http://www.surveyz.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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a join query please!
So -- what's the field that relates a booking to an allocation? Do they share a project_ID or what? If they do, you might try this: SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U LEFT JOIN Allocations A on A.User_ID = U.User_ID LEFT JOIN Bookings B ON B.User_ID = U.User_ID WHERE A.Project_ID = '11' OR B.Project_ID = '11' ORDER BY User_Firstname; A pet peeve of mine is when people 'quote' NUMBERS. According to the extremely well written manual, you only need to quote STRING values and DATETIME values. Unless the columns Project_ID and Booking_ID are some form of STRING column (CHAR, VARCHAR, TEXT, etc.) you don't need to quote their values in queries. It forces the query engine to perform an unnecessary internal type conversion. Here is what I think your query should look like: SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U LEFT JOIN Allocations A on A.User_ID = U.User_ID LEFT JOIN Bookings B ON B.User_ID = U.User_ID WHERE A.Project_ID = 11 OR B.Project_ID = 11 ORDER BY User_Firstname; I used SELECT DISTINCT so that in the event that someone was both BOOKED and ALLOCATED to the same project, you only got them listed once. Shawn Green Database Administrator Unimin Corporation - Spruce Pine "shaun thornburgh" <[EMAIL PROTECTED]> wrote on 12/23/2004 08:37:37 AM: > Hi, > > Thanks for your reply but that produces exactly the same result... > > Any ideas? > > >From: Sasha Pachev <[EMAIL PROTECTED]> > >To: shaun thornburgh <[EMAIL PROTECTED]> > >CC: mysql@lists.mysql.com > >Subject: Re: Help with a join query please! > >Date: Tue, 21 Dec 2004 14:57:43 -0700 > > > >shaun thornburgh wrote: > >>Hi, > >> > >>I have (among others) three tables in my database that i am struggling > >>with a join query on. The database holds dates for Bookings. If Users are > >>Allocated to a particular Project they can be booked. However if a user is > >>booked but then unallocated I want to be able to display all peolple > >>allocated to that project plus the person originally booked. Here are my > >>efforts so far: > >> > >>SELECT U.User_ID, U.User_Firstname, U.User_Lastname > >>FROM Allocations A, Users U > >>LEFT JOIN Bookings B > >>ON B.User_ID = U.User_ID > >>AND B.Booking_ID = '4512' > >>WHERE U.User_ID = A.User_ID > >>AND A.Project_ID = '11' > >>ORDER BY User_Firstname; > > > >Shaun: > > > >If I understand the problem right, it sounds like you are missing AND > >B.Bookings_ID is NULL in the where clause. > > > > > > > >-- > >Sasha Pachev > >Create online surveys at http://www.surveyz.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: Help with a join query please!
Hi, Thanks for your reply but that produces exactly the same result... Any ideas? From: Sasha Pachev <[EMAIL PROTECTED]> To: shaun thornburgh <[EMAIL PROTECTED]> CC: mysql@lists.mysql.com Subject: Re: Help with a join query please! Date: Tue, 21 Dec 2004 14:57:43 -0700 shaun thornburgh wrote: Hi, I have (among others) three tables in my database that i am struggling with a join query on. The database holds dates for Bookings. If Users are Allocated to a particular Project they can be booked. However if a user is booked but then unallocated I want to be able to display all peolple allocated to that project plus the person originally booked. Here are my efforts so far: SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Allocations A, Users U LEFT JOIN Bookings B ON B.User_ID = U.User_ID AND B.Booking_ID = '4512' WHERE U.User_ID = A.User_ID AND A.Project_ID = '11' ORDER BY User_Firstname; Shaun: If I understand the problem right, it sounds like you are missing AND B.Bookings_ID is NULL in the where clause. -- Sasha Pachev Create online surveys at http://www.surveyz.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: Help with a join query please!
shaun thornburgh wrote: Hi, I have (among others) three tables in my database that i am struggling with a join query on. The database holds dates for Bookings. If Users are Allocated to a particular Project they can be booked. However if a user is booked but then unallocated I want to be able to display all peolple allocated to that project plus the person originally booked. Here are my efforts so far: SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Allocations A, Users U LEFT JOIN Bookings B ON B.User_ID = U.User_ID AND B.Booking_ID = '4512' WHERE U.User_ID = A.User_ID AND A.Project_ID = '11' ORDER BY User_Firstname; Shaun: If I understand the problem right, it sounds like you are missing AND B.Bookings_ID is NULL in the where clause. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a join query please!
Hi, I have (among others) three tables in my database that i am struggling with a join query on. The database holds dates for Bookings. If Users are Allocated to a particular Project they can be booked. However if a user is booked but then unallocated I want to be able to display all peolple allocated to that project plus the person originally booked. Here are my efforts so far: SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Allocations A, Users U LEFT JOIN Bookings B ON B.User_ID = U.User_ID AND B.Booking_ID = '4512' WHERE U.User_ID = A.User_ID AND A.Project_ID = '11' ORDER BY User_Firstname; I am using version 3.23.54. The query works but doesnt return the required values. The query returns all of the users allocated to the project, not the user who is currently booked but not allocated to the project. Here are the table definitions: mysql> DESCRIBE Allocations; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | Project_ID | int(11) | | PRI | 0 | | | User_ID| int(11) | | PRI | 0 | | ++-+--+-+-+---+ 2 rows in set (0.00 sec) mysql> DESCRIBE Bookings; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Booking_ID | int(11) | | PRI | NULL| auto_increment | | Booking_Type| varchar(15) | | | Unavailability | | | User_ID | int(11) | | | 0 | | | Project_ID | int(11) | YES | | NULL| | | Rep_ID | int(11) | YES | | NULL| | | PCT_ID | int(11) | YES | | NULL| | | Practice_ID | int(11) | YES | | NULL| | | Booking_Creator_ID | int(11) | YES | | NULL| | | Booking_Creation_Date | datetime| YES | | NULL| | | Booking_Start_Date | datetime| | | -00-00 00:00:00 | | | Booking_End_Date| datetime| | | -00-00 00:00:00 | | | Booking_Completion_Date | date| YES | | NULL| | | Booking_Mileage | int(5) | YES | | NULL| | | Booking_Status | varchar(15) | | | Other | | | Unavailability_ID | int(2) | YES | | NULL| | | Work_Type_ID| int(2) | YES | | NULL| | | Additional_Notes| text| YES | | NULL| | | Form_Recieved | char(3) | | | | | | Section_C | char(3) | | | | | | Date_Difference | varchar(20) | | | n/a | | +-+-+--+-+-++ 20 rows in set (0.00 sec) mysql> DESCRIBE Projects; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | Project_ID | int(11) | | PRI | NULL| auto_increment | | Project_Name | varchar(100) | | | || | Client_ID| int(11) | | | 0 || | Rep_Viewable | char(3) | | | Yes || | User_ID | int(11) | YES | | NULL|| +--+--+--+-+-++ 5 rows in set (0.00 sec) mysql> DESCRIBE Users; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | User_ID | int(11) | | PRI | NULL| auto_increment | | Client_ID| int(3) | YES | | NULL| | | User_Username| varchar(40) | | | | | | User_Firstname | varchar(50) | YES | | NULL| | | User_Lastname| varchar(50) | YES | | NULL| | | U
Re: Help with a join query please
From: "Rhino" <[EMAIL PROTECTED]> To: "shaun thornburgh" <[EMAIL PROTECTED]>,<[EMAIL PROTECTED]> Subject: Re: Help with a join query please Date: Thu, 16 Dec 2004 14:59:48 -0500 - Original Message - From: "shaun thornburgh" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, December 16, 2004 2:02 PM Subject: Help with a join query please > Hi, > > I have (among others) three tables in my database that i am struggling with > a join query on. The database holds dates for Bookings. If Users are > Allocated to a particular Project they can be booked. However if a user is > booked but then unallocated I want to be able to display all peolple > allocated to that project plus the person originally booked. Here are my > efforts so far: > > SELECT U.User_ID, U.User_Firstname, U.User_Lastname > FROM Allocations A, Users U > LEFT JOIN Bookings B > ON B.User_ID = U.User_ID > AND B.Booking_ID = '4512' > WHERE U.User_ID = A.User_ID > AND A.Project_ID = '11' > ORDER BY User_Firstname; > > I hope this makes sense! > We could do a lot more for you if you mentioned a few things: a) which version of MySQL you are using b) whether the query works or not the way it stands c) if it does work, what is wrong with the answer you are getting d) if it doesn't work, what error message you are getting And if you REALLY want to help us help you, you could provide definitions of the tables and a few rows of typical data from each table to help us visualize the problem better. Rhino Sorry guys! a) I am using version 3.23.54 b) The query works but doesnt return the required values c) The query returns all of the users allocated to the project, not the user who is currently booked but not allocated to the project d) No error message! Here are the table definitions: mysql> DESCRIBE Allocations; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | Project_ID | int(11) | | PRI | 0 | | | User_ID| int(11) | | PRI | 0 | | ++-+--+-+-+---+ 2 rows in set (0.00 sec) mysql> DESCRIBE Bookings; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | Booking_ID | int(11) | | PRI | NULL| auto_increment | | Booking_Type| varchar(15) | | | Unavailability | | | User_ID | int(11) | | | 0 | | | Project_ID | int(11) | YES | | NULL| | | Rep_ID | int(11) | YES | | NULL| | | PCT_ID | int(11) | YES | | NULL| | | Practice_ID | int(11) | YES | | NULL| | | Booking_Creator_ID | int(11) | YES | | NULL| | | Booking_Creation_Date | datetime| YES | | NULL| | | Booking_Start_Date | datetime| | | -00-00 00:00:00 | | | Booking_End_Date| datetime| | | -00-00 00:00:00 | | | Booking_Completion_Date | date| YES | | NULL| | | Booking_Mileage | int(5) | YES | | NULL| | | Booking_Status | varchar(15) | | | Other | | | Unavailability_ID | int(2) | YES | | NULL| | | Work_Type_ID| int(2) | YES | | NULL| | | Additional_Notes| text| YES | | NULL| | | Form_Recieved | char(3) | | | | | | Section_C | char(3) | | | | | | Date_Difference | varchar(20) | | | n/a | | +-+-+--+-+-++ 20 rows in set (0.00 sec) mysql> DESCRIBE Projects; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | Project_ID | int(11) | | PRI | NULL| auto_increment | | Project_Nam
Re: Help with a join query please
- Original Message - From: "shaun thornburgh" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, December 16, 2004 2:02 PM Subject: Help with a join query please > Hi, > > I have (among others) three tables in my database that i am struggling with > a join query on. The database holds dates for Bookings. If Users are > Allocated to a particular Project they can be booked. However if a user is > booked but then unallocated I want to be able to display all peolple > allocated to that project plus the person originally booked. Here are my > efforts so far: > > SELECT U.User_ID, U.User_Firstname, U.User_Lastname > FROM Allocations A, Users U > LEFT JOIN Bookings B > ON B.User_ID = U.User_ID > AND B.Booking_ID = '4512' > WHERE U.User_ID = A.User_ID > AND A.Project_ID = '11' > ORDER BY User_Firstname; > > I hope this makes sense! > We could do a lot more for you if you mentioned a few things: a) which version of MySQL you are using b) whether the query works or not the way it stands c) if it does work, what is wrong with the answer you are getting d) if it doesn't work, what error message you are getting And if you REALLY want to help us help you, you could provide definitions of the tables and a few rows of typical data from each table to help us visualize the problem better. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a join query please
Hi, I have (among others) three tables in my database that i am struggling with a join query on. The database holds dates for Bookings. If Users are Allocated to a particular Project they can be booked. However if a user is booked but then unallocated I want to be able to display all peolple allocated to that project plus the person originally booked. Here are my efforts so far: SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Allocations A, Users U LEFT JOIN Bookings B ON B.User_ID = U.User_ID AND B.Booking_ID = '4512' WHERE U.User_ID = A.User_ID AND A.Project_ID = '11' ORDER BY User_Firstname; I hope this makes sense! Shaun -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with SQL (join?) query
I think he gave you the right answer. you can also use joins instead of where, but it is the same idea, not sure which one of the two will be faster though: select groupid, groupname, owner.name as f_owner, creator.name as f_creator from groups left join users as owner on owner.uid = groups.groupowner left join users as creator on groups.groupcreator = creator.uid - Original Message - From: "Kris" <[EMAIL PROTECTED]> To: "Peter Valdemar Mørch" <[EMAIL PROTECTED]> Cc: "Kris zoob-at-doomstar.com |Lists|" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, November 30, 2004 7:08 PM Subject: Re: help with SQL (join?) query No offense but your response has created more confusion about this.. Here is a more simple diagram for what I'd like to get from an SQL query: Table users: uid username 1 john 2 jim 3 mary Table groups: id name creator owner 1 test 11 2 abc 12 3 test2 23 output from mysql: id(from groups) name(from groups) creatorname ownername 1 test john john 2 abc john jim 3 test2 jim mary I just want the output from the groups table but a name instead of the number where creator# and owner# in groups table is associated to the uid in users. Hope this helps understand my problem.. Thanks Kris Peter Valdemar Mørch wrote: Kris zoob-at-doomstar.com |Lists| wrote: I am trying to join to tables: users: uid name 1 john 2 jim 3 mary groups: groupid groupname groupowner groupcreator 1 test1 1 1 2test2 1 2 3 test32 3 My desired output would look like: groupid groupname owner creator 1 test1john john 2 test2john jim 3 test3jim mary I was unable to find an example online.. Can anyone help? try something like : select groupid, groupname, owner.name as f_owner, creator.name as f_creator from groups, users as owner, users as creator where groups.groupowner = owner.uid and groups.groupcreator = creator.uid; (untested - but principle should be sound...) Peter -- 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: help with SQL (join?) query
How so? Is there something you didn't understand? Peter's solution is the right idea. You need to join the groups table to the users table once to get the creatorname and again to get the ownername. Maybe it will be clearer if we rewrite the query to make the join conditions explicit: SELECT g.id, g.name, o.name AS 'owner', c.name AS 'creator' FROM groups g JOIN users o ON groups.owner = o.uid JOIN users c ON groups.creator = c.uid; Does that help? Kris wrote: No offense but your response has created more confusion about this.. Here is a more simple diagram for what I'd like to get from an SQL query: Table users: uid username 1 john 2 jim 3 mary Table groups: id name creator owner 1 test 11 2 abc 12 3 test2 23 output from mysql: id(from groups) name(from groups) creatorname ownername 1 test john john 2 abc john jim 3 test2 jim mary I just want the output from the groups table but a name instead of the number where creator# and owner# in groups table is associated to the uid in users. Hope this helps understand my problem.. Thanks Kris Peter Valdemar Mørch wrote: Kris zoob-at-doomstar.com |Lists| wrote: I am trying to join to tables: users: uid name 1 john 2 jim 3 mary groups: groupid groupname groupowner groupcreator 1 test1 1 1 2test2 1 2 3 test32 3 My desired output would look like: groupid groupname owner creator 1 test1john john 2 test2john jim 3 test3jim mary I was unable to find an example online.. Can anyone help? try something like : select groupid, groupname, owner.name as f_owner, creator.name as f_creator from groups, users as owner, users as creator where groups.groupowner = owner.uid and groups.groupcreator = creator.uid; (untested - but principle should be sound...) Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with SQL (join?) query
No offense but your response has created more confusion about this.. Here is a more simple diagram for what I'd like to get from an SQL query: Table users: uid username 1 john 2 jim 3 mary Table groups: id name creator owner 1 test 11 2 abc 12 3 test2 23 output from mysql: id(from groups) name(from groups) creatorname ownername 1 test john john 2 abc john jim 3 test2 jim mary I just want the output from the groups table but a name instead of the number where creator# and owner# in groups table is associated to the uid in users. Hope this helps understand my problem.. Thanks Kris Peter Valdemar Mørch wrote: Kris zoob-at-doomstar.com |Lists| wrote: I am trying to join to tables: users: uid name 1 john 2 jim 3 mary groups: groupid groupname groupowner groupcreator 1 test1 1 1 2test2 1 2 3 test32 3 My desired output would look like: groupid groupname owner creator 1 test1john john 2 test2john jim 3 test3jim mary I was unable to find an example online.. Can anyone help? try something like : select groupid, groupname, owner.name as f_owner, creator.name as f_creator from groups, users as owner, users as creator where groups.groupowner = owner.uid and groups.groupcreator = creator.uid; (untested - but principle should be sound...) Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with SQL (join?) query
Kris zoob-at-doomstar.com |Lists| wrote: I am trying to join to tables: users: uid name 1 john 2 jim 3 mary groups: groupid groupname groupowner groupcreator 1 test1 1 1 2test2 1 2 3 test32 3 My desired output would look like: groupid groupname owner creator 1 test1john john 2 test2john jim 3 test3jim mary I was unable to find an example online.. Can anyone help? try something like : select groupid, groupname, owner.name as f_owner, creator.name as f_creator from groups, users as owner, users as creator where groups.groupowner = owner.uid and groups.groupcreator = creator.uid; (untested - but principle should be sound...) Peter -- Peter Valdemar Mørch http://www.morch.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with SQL (join?) query
I am trying to join to tables: users: uid name 1 john 2 jim 3 mary groups: groupid groupname groupowner groupcreator 1 test1 1 1 2test2 1 2 3 test32 3 My desired output would look like: groupid groupname owner creator 1 test1john john 2 test2john jim 3 test3jim mary I was unable to find an example online.. Can anyone help? Thanks, Kris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky self join query help?
I noticed a few mistakes in my query, which may be causing some confusion and would probably cause it not to work. But I'll break everything down. The NULLs in the second owner column are the indicators that there is no matching owner in the most recent three months. Breaking down the query, the first part indicates what databases you want to delete records from: DELETE FROM Events Then you indicate which database you will be using to create the filter. In this case, Events and an alias of the Events table that I just called Owners. You are doing a left join so that you don't filter out any records from the first table, Events. USING Events LEFT JOIN Events AS Owners Since you are doing a join, you obviously need to specify a join condition. You are joining, based on OwnerID, the records from the Owners (Events) table that are younger than 3 months with ALL (because of the left join) records in the Events table. ON Events.ownerID=Owners.ownerID AND Owners.eventDate>= 3 months ago Anything that does not have a matching OwnerID from the Events/Owners join will have a NULL (or not match) "value" in the OwnerID column. Those are the ones you want tot delete, thus the last piece. WHERE Owners.ownerID IS NULL You should add a filter on Events for records older than 3 months. It will probably speed things up and will assure you don't delete newer records. So add this to the end, which I didn't have originally: AND Events.EventDate< 3 months ago Final delete statement: DELETE FROM Events USING Events LEFT JOIN Events AS Owners ON Events.ownerID=Owners.ownerID AND Owners.eventDate>= 3 months ago WHERE Owners.ownerID IS NULL AND Events.EventDate< 3 months ago Original statement with typos and logic flaw: DELETE FROM Events USING Events LEFT JOIN Events AS Owners ON Events.ownerID=Owners.ownerID AND Events.eventData> 3 months ago <-- logic flaw, should be Owners.EventDate WHERE Owners.ownerID IS NULL Hope that helps. On Nov 10, 2004, at 4:50 PM, Gerald Taylor wrote: Thanks And I am liking that other answer although it has all nulls in the second owner column and I don't get how it works. -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky self join query help?
The parts I am interested in: (I won't bore you with the fields not relevant to this problem ) CREATE TABLE events ( e_id int(15) NOT NULL auto_increment, e_owner int(15) NOT NULL default '0', e_time int(15) NOT NULL default '0', other junk omitted PRIMARY KEY (e_id) ) TYPE=MyISAM; Thanks And I am liking that other answer although it has all nulls in the second owner column and I don't get how it works. When I write applications that delete I always back up the table and use a copy or a small sample on a play database. [EMAIL PROTECTED] wrote: If you post the table structure (SHOW CREATE TABLE tablename\G) we could help you write this statement. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Gerald Taylor <[EMAIL PROTECTED]> wrote on 11/10/2004 11:52:35 AM: > I have this table of events. Each event has an owner > id and the time that it happened. > > What I want to do is delete all events > more than three months old but only if the owner does not own > any newer events. > > The coolest would just be a single DELETE query. > > Can this be done? > Mysql 4.0.18 > > > > > > > -- > 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: Tricky self join query help?
Try something like this: SELECT Events.ID, Events.ownerID, Owners.ownerID FROM Events LEFT JOIN Events AS Owners ON Events.ownerID=Owners.ownerID AND Events.eventData> 3 months ago WHERE Owners.ownerID IS NULL I know you want to do a delete, but play with SELECT first to make sure it's doing what you want. What the above query is doing is a self left join on owner IDs in the past three months. Thus, any owner with an event in the past three months will have a matching owner ID from the "Owners" table. You then create a filter for all events without a matching owner ID, value of NULL in the Owners.ownerID column. So your result will look like this: Events.ID Events.ownerID Owners.ownerID 1 1 1 2 1 1 3 2 NULL 4 3 3 So your DELETE statement would look something like this: DELETE FROM Events USING Events LEFT JOIN Events AS Owners ON Events.ownerID=Owners.ownerID AND Events.eventData> 3 months ago WHERE Owners.ownerID IS NULL No guarantee that this is totally correct, but some quick testing seems to indicate it works. On Nov 10, 2004, at 11:52 AM, Gerald Taylor wrote: I have this table of events. Each event has an owner id and the time that it happened. What I want to do is delete all events more than three months old but only if the owner does not own any newer events. The coolest would just be a single DELETE query. Can this be done? Mysql 4.0.18 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky self join query help?
If you post the table structure (SHOW CREATE TABLE tablename\G) we could help you write this statement. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Gerald Taylor <[EMAIL PROTECTED]> wrote on 11/10/2004 11:52:35 AM: > I have this table of events. Each event has an owner > id and the time that it happened. > > What I want to do is delete all events > more than three months old but only if the owner does not own > any newer events. > > The coolest would just be a single DELETE query. > > Can this be done? > Mysql 4.0.18 > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
Tricky self join query help?
I have this table of events. Each event has an owner id and the time that it happened. What I want to do is delete all events more than three months old but only if the owner does not own any newer events. The coolest would just be a single DELETE query. Can this be done? Mysql 4.0.18 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with Left Join query
First, you want to get an answer for every module, so you must use modules LEFT JOIN permissions to get a row for every module. You see? The table with missing rows (no row for Bob/module3 in permissions) goes on the right. When the table on the right does not have a matching row, the LEFT JOIN fills the result with NULLs in place of the missing values from the table on the right. So, given your sample data, SELECT m.mod_name, p.* FROM modules m LEFT JOIN permissions p ON m.mod_name = p.module would yield mod_name | ID | USER | MODULE | ADD | MOD | DEL -- module1 | 1 | bob | module11 | 1 | 0 | 1 module2 | 2 | bob | module2 | 1 | 0 | 1 module3 |NULL | NULL | NULL |NULL |NULL |NULL module1 | 3 | jane | module1 | 1 | 1 | 0 ... Now, do you see the problem with adding "WHERE p.user='bob'"? That won't match the 3rd row, which was meant to show bob has no permissions on module3. So, you have to make that part of the join condition. Try this: SELECT m.mod_name, p.ADD, p.MOD, p.DEL FROM modules m LEFT JOIN permissions p ON m.mod_name = p.module AND p.user='bob'; You probably want to see 0 instead of NULL for the missing rows. Then use: SELECT m.mod_name, IFNULL(p.ADD,0) Add, IFNULL(p.MOD,0) Mod, IFNULL(p.DEL,0) Del FROM modules m LEFT JOIN permissions p ON m.mod_name = p.module AND p.user='bob'; Finally, you are wasting a lot of space by storing module names in the permissions table. I expect your modules table has an id column. (It should.) You should store that id in the permissions table, rather than the name. That will save space and speed up your query. Then you would use something like: SELECT m.mod_name, IFNULL(p.ADD,0) Add, IFNULL(p.MOD,0) Mod, IFNULL(p.DEL,0) Del FROM modules m LEFT JOIN permissions p ON m.id = p.module_id AND p.user='bob'; Michael David T. wrote: I am building a database with permissions for different modules. The permissions table contains a separate row for each module/user combination, and lists the add/mod/del permissions for that module. So, for example, user 'bob' might have add and del rights for 'module1', add only rights for 'module2', and no rights for 'module3'. The rows in permissions would be: ID | USER | MODULE | ADD | MOD | DEL 1 | bob | module1| 1 | 0 | 1 2 | bob | module2| 1 | 0 | 0 But, I need to build an entry form that lists all of the modules in the modules table and loads in the permissions for that user. I was believing that I could write a single LEFT JOIN query that would give me all the modules and permissions in one single pass. However, when I build the query, it only returns the user records. I have tried: SELECT modules.mod_name, permissions.* FROM permissions LEFT JOIN modules ON permissions.module = modules.mod_name WHERE permissions.user='bob' What am I doing wrong? Cheers, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with Left Join query
- Original Message - From: "David T." <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, September 17, 2004 2:33 PM Subject: Problems with Left Join query > I am building a database with permissions for different modules. The > permissions table contains a separate row for each module/user combination, and > lists the add/mod/del permissions for that module. So, for example, user 'bob' > might have add and del rights for 'module1', add only rights for 'module2', and > no rights for 'module3'. The rows in permissions would be: > > ID | USER | MODULE | ADD | MOD | DEL > > 1 | bob | module1| 1 | 0 | 1 > 2 | bob | module2| 1 | 0 | 0 > > But, I need to build an entry form that lists all of the modules in the modules > table and loads in the permissions for that user. I was believing that I could > write a single LEFT JOIN query that would give me all the modules and > permissions in one single pass. However, when I build the query, it only > returns the user records. I have tried: > > SELECT modules.mod_name, permissions.* > FROM permissions > LEFT JOIN modules ON > permissions.module = modules.mod_name > WHERE permissions.user='bob' > > What am I doing wrong? Have you tried this query as a RIGHT JOIN instead of a LEFT JOIN? If so, what happened? Also, what MySQL version are you using? Perhaps you are doing Left Joins on a version that doesn't support them. I would have thought you'd get a syntax error in that case but maybe that's too optimistic Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with Left Join query
On Fri, 17 Sep 2004 11:33:04 -0700 (PDT), David T. <[EMAIL PROTECTED]> wrote: > I am building a database with permissions for different modules. The > permissions table contains a separate row for each module/user combination, You didn't say, but if you're using PHP you might find this of use: http://pear.php.net/package/LiveUser -- Greg Donald http://gdconsultants.com/ http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with Left Join query
I am building a database with permissions for different modules. The permissions table contains a separate row for each module/user combination, and lists the add/mod/del permissions for that module. So, for example, user 'bob' might have add and del rights for 'module1', add only rights for 'module2', and no rights for 'module3'. The rows in permissions would be: ID | USER | MODULE | ADD | MOD | DEL 1 | bob | module1| 1 | 0 | 1 2 | bob | module2| 1 | 0 | 0 But, I need to build an entry form that lists all of the modules in the modules table and loads in the permissions for that user. I was believing that I could write a single LEFT JOIN query that would give me all the modules and permissions in one single pass. However, when I build the query, it only returns the user records. I have tried: SELECT modules.mod_name, permissions.* FROM permissions LEFT JOIN modules ON permissions.module = modules.mod_name WHERE permissions.user='bob' What am I doing wrong? Cheers, David ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sum/Join Query Building
Try adding DISTINCT to your query: SELECT DISTINCT I'm not sure if that's going to work in your case, but the problem you are having seems to be duplicate rows caused by joins. A left join will always return one or more rows from the main table (VPN). On Jul 30, 2004, at 9:47 AM, Alex wrote: I'm having a problem with summing up joined tables.. can anyone help me? I have 3 tables that I am joining together vpn, dialup, and userinfo vpn has the following columns (This was a premade table so i couldn't change username into user_id to link with userinfo) username session_time and other extraneous columns.. dialup has the following columns user_id session_time and other extraneous columns.. userinfo has the following columns id username first_name last_name and other extraneous columns.. dialup user_id joins with userinfo's id to get the username My query is as follows.. SELECT userinfo.First_Name, userinfo.Last_Name, (Sum(Dialup.Session_Time)/3600) as Dialup_Total_Hours, (Sum(VPN.Session_Time)/3600) as VPN_Total_hours FROM VPN LEFT JOIN user_info ON (VPN.Username = userinfo.Username) LEFT JOIN Dialup ON (userinfo.ID = dialup.User_ID) WHERE GROUP BY Username, First_Name, Last_Name If I don't do any joining.. and sum up session time by itself i get the correct # of hours. With this joining I get roughly a factor of 10x the correct amount.. Why is this the case? Can anyone help me fix it? I couldn't find any advanced sql query building topics on this through google, so I'm trying my luck here. thanks Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sum/Join Query Building
This has more to do with how JOINing tables work than making the sums work right. When you join tables, a new virtual table containing all possible combinations of rows from each of the tables is created. The ON clauses of each join limits which rows stay in the table (or get put into it in the first place, depending on your point of view). What is happening is that for each VPN/user_info combination there are multiple dialup rows that also match. That means there will be duplicates of all of your data. Those duplicates are throwing your sums off; If you want to present multiple sums in the same report, you have to first calculate them in isolation (to avoid the duplication problems I just described) then combine them later to create a well-formatted report. CREATE TEMPORARY TABLE tmpDialup SELECT userinfo.Username, userinfo.First_Name, userinfo.Last_Name, (Sum(Dialup.Session_Time)/3600) as Dialup_Total_Hours, FROM userinfo LEFT JOIN Dialup ON (userinfo.ID = dialup.User_ID) GROUP BY Username, First_Name, Last_Name CREATE TEMPORARY TABLE tmpVPN SELECT userinfo.Username, userinfo.First_Name, userinfo.Last_Name, (Sum(VPN.Session_Time)/3600) as VPN_Total_hours FROM userinfo LEFT JOIN VPN ON (VPN.Username = userinfo.Username) GROUP BY Username, First_Name, Last_Name SELECT u.Username , u.First_name , u.Last_Name , SUM(Dialup_Total_Hours) as Dialup_Total_Hours , SUM(VPN_Total_hours) as VPN_Total_Hours FROM userinfo u INNER JOIN tmpDialup td ON td.Username =u.Username AND td.First_Name = u.First_Name AND td.Last_Name = u.Last_Name INNER JOIN tmpVPN tv ON tv.Username =u.Username AND tv.First_Name = u.First_Name AND tv.Last_Name = u.Last_Name GROUP BY u.Username, u.First_name, u.Last_Name DROP TABLE tmpDialup, tmpVPN; IF Username is a unique value on the table userinfo, you can get rid of all of the First_Name and Last_Name columns from all of the queries except the last one. They would have to stay in the SELECT and ORDER BY clauses (but that's all) if you want to see them in the report. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Alex <[EMAIL PROTECTED]> wrote on 07/30/2004 09:47:20 AM: > I'm having a problem with summing up joined tables.. can anyone help me? > > I have 3 tables that I am joining together > > vpn, dialup, and userinfo > > vpn has the following columns > (This was a premade table so i couldn't change username into user_id > to link with userinfo) > username > session_time > and other extraneous columns.. > > dialup has the following columns > user_id > session_time > and other extraneous columns.. > > userinfo has the following columns > id > username > first_name > last_name > and other extraneous columns.. > > dialup user_id joins with userinfo's id to get the username > > My query is as follows.. > > SELECT > userinfo.First_Name, > userinfo.Last_Name, > (Sum(Dialup.Session_Time)/3600) as Dialup_Total_Hours, > (Sum(VPN.Session_Time)/3600) as VPN_Total_hours > FROM > VPN > LEFT JOIN user_info ON (VPN.Username = userinfo.Username) > LEFT JOIN Dialup ON (userinfo.ID = dialup.User_ID) > WHERE > GROUP BY Username, First_Name, Last_Name > > > If I don't do any joining.. and sum up session time by itself i get > the correct # of hours. > With this joining I get roughly a factor of 10x the correct amount.. > Why is this the case? Can anyone help me fix it? > I couldn't find any advanced sql query building topics on this through > google, so I'm trying my luck here. > > thanks > Alex > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
Re: Sum/Join Query Building
Alex wrote: I'm having a problem with summing up joined tables.. can anyone help me? I have 3 tables that I am joining together vpn, dialup, and userinfo vpn has the following columns (This was a premade table so i couldn't change username into user_id to link with userinfo) username session_time and other extraneous columns.. dialup has the following columns user_id session_time and other extraneous columns.. userinfo has the following columns id username first_name last_name and other extraneous columns.. dialup user_id joins with userinfo's id to get the username My query is as follows.. SELECT userinfo.First_Name, userinfo.Last_Name, (Sum(Dialup.Session_Time)/3600) as Dialup_Total_Hours, (Sum(VPN.Session_Time)/3600) as VPN_Total_hours FROM VPN LEFT JOIN user_info ON (VPN.Username = userinfo.Username) LEFT JOIN Dialup ON (userinfo.ID = dialup.User_ID) You will get a repeat of VPN.Session_Time for each Dialup.Session_time. WHERE GROUP BY Username, First_Name, Last_Name If I don't do any joining.. and sum up session time by itself i get the correct # of hours. With this joining I get roughly a factor of 10x the correct amount.. Why is this the case? Can anyone help me fix it? I couldn't find any advanced sql query building topics on this through google, so I'm trying my luck here. thanks Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sum/Join Query Building
I'm having a problem with summing up joined tables.. can anyone help me? I have 3 tables that I am joining together vpn, dialup, and userinfo vpn has the following columns (This was a premade table so i couldn't change username into user_id to link with userinfo) username session_time and other extraneous columns.. dialup has the following columns user_id session_time and other extraneous columns.. userinfo has the following columns id username first_name last_name and other extraneous columns.. dialup user_id joins with userinfo's id to get the username My query is as follows.. SELECT userinfo.First_Name, userinfo.Last_Name, (Sum(Dialup.Session_Time)/3600) as Dialup_Total_Hours, (Sum(VPN.Session_Time)/3600) as VPN_Total_hours FROM VPN LEFT JOIN user_info ON (VPN.Username = userinfo.Username) LEFT JOIN Dialup ON (userinfo.ID = dialup.User_ID) WHERE GROUP BY Username, First_Name, Last_Name If I don't do any joining.. and sum up session time by itself i get the correct # of hours. With this joining I get roughly a factor of 10x the correct amount.. Why is this the case? Can anyone help me fix it? I couldn't find any advanced sql query building topics on this through google, so I'm trying my luck here. thanks Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a join query
shaun thornburgh wrote: Hi, I am having trouble with a LEFT JOIN. I have three tables: Users, Projects and Allocations. Users can be allocated to zero or more projects and a project can be allocated to zero or more users. Table descriptions are below. How can i select a users details and all of the pojects they are allocated to? This query is fine if the user is allocated to one or more projects: SELECT U.*, P.* FROM Users U, Allocations A, Projects P WHERE A.Project_ID = P.Project_ID AND A.User_ID = U.User_ID AND U.User_ID = '2'; Right. A user assigned to no projects won't have an entry in the Allocations table, so the JOIN condition isn't met. I'm assuming the problem is that the user is missing from the Allocations table, so you need a LEFT JOIN of Users to Allocations. And if i put a join in the query i get hundreds of results: Your original query above joins 3 tables. In your next query, you're changing one of the joins from an implicit inner join to a left join, not adding a join. SELECT U.*, P.* FROM Users U, Allocations A LEFT JOIN Projects P ON P.Project_ID = A.Project_ID AND A.User_ID = U.User_ID WHERE U.User_ID = '2'; First, though it's not the source of the problem, User_ID is an int, so lose the quotes around 2. You're forcing mysql to convert the string '2' to the integer 2. You have no join condition on the join of Users and Allocations, so you're getting the Cartesian product, hence the hundreds of results. (Yes, I see the "A.User_ID = U.User_ID", but it's part of the ON clause for the LEFT JOIN of A to P, so it's not doing what you meant.) SELECT U.*, P.* FROM Users U LEFT JOIN Allocations A ON U.User_ID = A.User_ID LEFT JOIN Projects P ON A.Project_ID = P.Project_ID WHERE U.User_ID = 2; Michael Any help here would be great, many thanks. mysql> DESCRIBE Users; +--+---+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-++ | User_ID | int(11) | | PRI | NULL| auto_increment | | User_Username| varchar(40) | | | || | User_Password| varchar(20) | YES | | NULL || | User_Name| varchar(100) | | | || | User_Type| varchar(20) | | | Nurse || | User_Email | varchar(100) | YES | | NULL || +--+---+--+-+-++ 17 rows in set (0.01 sec) mysql> DESCRIBE Allocations; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | Project_ID | int(11) | | PRI | 0 | | | User_ID| int(11) | | PRI | 0 | | ++-+--+-+-+---+ 2 rows in set (0.00 sec) mysql> DESCRIBE Projects; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | Project_ID | int(11) | | PRI | NULL| auto_increment | | Project_Name | varchar(100) | | | || +--+--+--+-+-++ 5 rows in set (0.01 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with a join query
Hi, I am having trouble with a LEFT JOIN. I have three tables: Users, Projects and Allocations. Users can be allocated to zero or more projects and a project can be allocated to zero or more users. Table descriptions are below. How can i select a users details and all of the pojects they are allocated to? This query is fine if the user is allocated to one or more projects: SELECT U.*, P.* FROM Users U, Allocations A, Projects P WHERE A.Project_ID = P.Project_ID AND A.User_ID = U.User_ID AND U.User_ID = '2'; And if i put a join in the query i get hundreds of results: SELECT U.*, P.* FROM Users U, Allocations A LEFT JOIN Projects P ON P.Project_ID = A.Project_ID AND A.User_ID = U.User_ID WHERE U.User_ID = '2'; Any help here would be great, many thanks. mysql> DESCRIBE Users; +--+---+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-++ | User_ID | int(11) | | PRI | NULL| auto_increment | | User_Username| varchar(40) | | | | | | User_Password| varchar(20) | YES | | NULL| | | User_Name| varchar(100) | | | | | | User_Type| varchar(20) | | | Nurse | | | User_Email | varchar(100) | YES | | NULL| | +--+---+--+-+-++ 17 rows in set (0.01 sec) mysql> DESCRIBE Allocations; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | Project_ID | int(11) | | PRI | 0 | | | User_ID| int(11) | | PRI | 0 | | ++-+--+-+-+---+ 2 rows in set (0.00 sec) mysql> DESCRIBE Projects; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | Project_ID | int(11) | | PRI | NULL| auto_increment | | Project_Name | varchar(100) | | | || +--+--+--+-+-++ 5 rows in set (0.01 sec) _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with my LEFT JOIN query...
It sounds like you are missing indexes. Please post the results of - SHOW CREATE TABLE ImportLiebermansStep3Add; - and - SHOW CREATE TABLE ProductsOld; - and we can tell you if you have enough indexes or not. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jeff Gannaway <[EMAIL PROTECTED]To: [EMAIL PROTECTED] hics.com>cc: Fax to: 07/10/2004 08:18 Subject: Need help with my LEFT JOIN query... PM I'm trying to SELECT a field (ProductID) from 'Table a' WHERE two corresponding fields are equal (a.PUBLISHER = b.Vendor AND a.NUMBER = b.VIN) Table 'a' (approximately 100,000 records): ++ | ImportLiebermansStep3Add | ++ | ProductID | PUBLISHER | NUMBER | +---+---++ | ACC_4076 | ACC | 4076 | | BCD_300 | BCD | 300| | DEC_R50 | DEC | R50| | WIN_220 | WIN | 220| +---+---++ Table 'b' (approximately 20,000 records): ++ | ProductsOLD| ++ | ProductID | Vendor| VIN| +---+---++ | | ACC | 4076 | | | BCD | | | | DEC | R50| +---+---++ Here's my Query SELECT a.ProductID FROM ImportLiebermansStep3Add a LEFT JOIN ProductsOLD b ON a.PUBLISHER=b.Vendor AND a.NUMBER=b.VIN WHERE b.ProductID IS NULL; But this query runs, and runs, and runs, and runs, and runs without ever giving results. What am I missing??? Thanks! -Jeff Gannaway ___ http://RadioU.com This Is Where Music Is Going - Listen Online! ___ -- 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: Need help with my LEFT JOIN query...
Jeff Why are you doing a LEFT JOIN instead of a INNER JOIN right now you are getting all rows from first table regardless of match condition so all of the rows for a.ProductID FROM ImportLiebermansStep3Add are being returned try the inner join instead Martin - Original Message - From: "Jeff Gannaway" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, July 10, 2004 5:18 PM Subject: Need help with my LEFT JOIN query... > I'm trying to SELECT a field (ProductID) from 'Table a' WHERE two > corresponding fields are equal (a.PUBLISHER = b.Vendor AND a.NUMBER = b.VIN) > > > Table 'a' (approximately 100,000 records): > ++ > | ImportLiebermansStep3Add | > ++ > | ProductID | PUBLISHER | NUMBER | > +---+---++ > | ACC_4076 | ACC | 4076 | > | BCD_300 | BCD | 300| > | DEC_R50 | DEC | R50| > | WIN_220 | WIN | 220| > +---+---++ > > Table 'b' (approximately 20,000 records): > ++ > | ProductsOLD| > ++ > | ProductID | Vendor| VIN| > +---+---++ > | | ACC | 4076 | > | | BCD | | > | | DEC | R50| > +---+---++ > > > Here's my Query > > SELECT a.ProductID FROM ImportLiebermansStep3Add a > LEFT JOIN ProductsOLD b ON a.PUBLISHER=b.Vendor AND a.NUMBER=b.VIN > WHERE b.ProductID IS NULL; > > But this query runs, and runs, and runs, and runs, and runs without ever > giving results. > > What am I missing??? > > Thanks! > -Jeff Gannaway > ___ > > http://RadioU.com > This Is Where Music Is Going - Listen Online! > ___ > > > > -- > 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]
Need help with my LEFT JOIN query...
I'm trying to SELECT a field (ProductID) from 'Table a' WHERE two corresponding fields are equal (a.PUBLISHER = b.Vendor AND a.NUMBER = b.VIN) Table 'a' (approximately 100,000 records): ++ | ImportLiebermansStep3Add | ++ | ProductID | PUBLISHER | NUMBER | +---+---++ | ACC_4076 | ACC | 4076 | | BCD_300 | BCD | 300| | DEC_R50 | DEC | R50| | WIN_220 | WIN | 220| +---+---++ Table 'b' (approximately 20,000 records): ++ | ProductsOLD| ++ | ProductID | Vendor| VIN| +---+---++ | | ACC | 4076 | | | BCD | | | | DEC | R50| +---+---++ Here's my Query SELECT a.ProductID FROM ImportLiebermansStep3Add a LEFT JOIN ProductsOLD b ON a.PUBLISHER=b.Vendor AND a.NUMBER=b.VIN WHERE b.ProductID IS NULL; But this query runs, and runs, and runs, and runs, and runs without ever giving results. What am I missing??? Thanks! -Jeff Gannaway ___ http://RadioU.com This Is Where Music Is Going - Listen Online! ___ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Crash with join query in 4.1.1
Is this bug a known one? I can reliably crash 4.1.1 with this query every time right now, which is a bit of a bummer. From the error log: Some pointers may be invalid and cause the dump to abort... thd->query at 0x86644a0 = SELECT profileid, profile_name, domain_name, domainid FROM profiles, domain_profile, domains WHERE profileid=ref_profileid AND domainid=ref_domainid AND ref_domainid in (SELECT domainid FROM domains LEFT JOIN domain_profile ON ref_domainid=domainid GROUP BY domain_name HAVING count(ref_profileid) > 1) ORDER BY domain_name thd->thread_id=2 ~/crash> resolve_stack_dump -s mysqld.sym -n m.stack 0x8089167 handle_segfault + 423 0x82da818 pthread_sighandler + 184 0x80baa71 get_best_combination__FP4JOIN + 145 0x80b899e make_join_statistics__FP4JOINP13st_table_listP4ItemP16st_dynamic_array + 4158 0x80b5271 optimize__4JOIN + 369 0x80b780f mysql_select__FP3THDPPP4ItemP13st_table_listUiRt4List1Z4ItemP4ItemUiP8st_orderT7T5T7UlP13select_resultP18st_select_lex_unitP13s + 767 0x80b487e handle_select__FP3THDP6st_lexP13select_result + 174 0x8096b1a mysql_execute_command__FP3THD + 1354 0x809b3f1 mysql_parse__FP3THDPcUi + 177 0x80957ff dispatch_command__F19enum_server_commandP3THDPcUi + 1631 0x8095192 do_command__FP3THD + 162 0x8094907 handle_one_connection + 551 0x82d7fcc pthread_start_thread + 220 0x830b8fa thread_start + 4 CREATE TABLE `domain_profile` ( `ref_domainid` int(10) unsigned NOT NULL default '0', `ref_profileid` int(10) unsigned NOT NULL default '0', `modified` timestamp NOT NULL, `created` timestamp NOT NULL, KEY `idx_domainid` (`ref_domainid`), KEY `idx_profileid` (`ref_profileid`), CONSTRAINT `0_1700` FOREIGN KEY (`ref_profileid`) REFERENCES `profiles` (`profileid`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `0_1701` FOREIGN KEY (`ref_domainid`) REFERENCES `domains` (`domainid`) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Domain::profile mapping'; CREATE TABLE `domains` ( `domainid` int(10) unsigned NOT NULL auto_increment, `domain_name` char(200) NOT NULL default 'INVALID', `domain_active` char(1) NOT NULL default 'N', `modified` timestamp NOT NULL, `created` timestamp NOT NULL, PRIMARY KEY (`domainid`), KEY `idx_active` (`domain_active`) ) TYPE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Master list of domains for customer'; CREATE TABLE `profiles` ( `profileid` int(10) unsigned NOT NULL auto_increment, `profile_name` varchar(100) NOT NULL default 'Default Profile', `modified` timestamp NOT NULL, `created` timestamp NOT NULL, PRIMARY KEY (`profileid`) ) TYPE=InnoDB DEFAULT CHARSET=latin1 COMMENT='List of profiles'; mysql> select * from profiles; +---+--+-+-+ | profileid | profile_name | modified| created | +---+--+-+-+ | 1 | Built-in Profile | 2004-06-28 10:00:42 | -00-00 00:00:00 | | 2 | Test Profile | 2004-07-01 10:32:04 | 2004-07-01 10:32:04 | +---+--+-+-+ mysql> select * from domain_profile; +--+---+-+-+ | ref_domainid | ref_profileid | modified| created | +--+---+-+-+ |1 | 1 | 2004-06-29 17:12:41 | 2004-06-29 17:12:41 | |2 | 1 | 2004-07-01 09:29:57 | 2004-07-01 09:29:57 | |2 | 2 | 2004-07-01 11:37:19 | 2004-07-01 11:37:19 | |1 | 2 | 2004-07-01 11:45:28 | 2004-07-01 11:45:28 | +--+---+-+-+ mysql> select * from domains; +--+-+---+-+-+ | domainid | domain_name | domain_active | modified| created | +--+-+---+-+-+ |1 | test.domain | Y | 2004-06-29 17:12:15 | 2004-06-29 17:12:10 | |2 | foo.domain | Y | 2004-06-30 10:52:34 | 2004-06-30 10:52:34 | |3 | jack.domain | Y | 2004-06-30 15:34:04 | 2004-06-30 15:34:04 | +--+-+---+-+-+ *** 1. row *** id: 1 select_type: PRIMARY table: profiles type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 2 Extra: Using temporary; Using filesort *** 2. row *** id: 1 select_type: PRIMARY table: domain_profile type: ref possible_keys: idx_domainid,idx_profileid key: idx_profileid key
Re: Self-Join Query
Perhaps I got my syntax wrong. IF() has been available since 3.23 as far as I know. I find it more readable than case if I'm only doing a single test, but either accomplishes what you need. Here is the documentation for CASE and IF http://dev.mysql.com/doc/mysql/en/Control_flow_functions.html On Jun 3, 2004, at 8:33 PM, James KATARSKI wrote: Harold and Brent, Thanks for your help. I tried Brent's solution first, but the MySQL client didn't seem to like the IF statements. The case statements worked sweet though. -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Self-Join Query
Harold and Brent, Thanks for your help. I tried Brent's solution first, but the MySQL client didn't seem to like the IF statements. The case statements worked sweet though. Cheers guys, James Katarski Systems Administrator School of Computer & Information Science Edith Cowan University ML -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Harald Fuchs Sent: Thursday, June 03, 2004 10:23 PM To: [EMAIL PROTECTED] Subject: Re: Self-Join Query In article <[EMAIL PROTECTED]>, "James KATARSKI" <[EMAIL PROTECTED]> writes: > I'm trying to generate a report like this: (Which I've done in TWO > querys, the copied and pasted together) > +-+--+--+ > | Page Name | Internal | External | > +-+--+--+ > | after_hours | 615 | 105 | > | conditions | 332 | 50 | > | faq | 89 |2 | > | help| 458 | 174 | > | labtimes| 682 | 143 | > | support | 2151 | 383 | > | passwords | 154 | 22 | > | practise| 497 | 99 | > | printing| 801 | 85 | > | wireless| 926 | 180 | > +-+--+--+ > Using a query like this: > SELECT DISTINCT i.page_name AS 'Page Name', COUNT(i.page_name) as > 'Internal', COUNT(e.page_name) as 'External' > FROM ip_logs i, ip_logs e > WHERE (i.hit_no != e.hit_no) > AND (i.ip LIKE '10.%' OR i.ip LIKE '139.230.%') > #Internal Addresses > AND (e.ip NOT LIKE '10.%' AND e.ip NOT LIKE '139.230.%') > #External Addresses > GROUP BY i.page_name; You could try something like SELECT page_name AS "Page Name", sum(CASE WHEN ip LIKE '10.%' OR ip LIKE '139.230.%' THEN 1 ELSE 0 END) AS "Internal", sum(CASE WHEN ip LIKE '10.%' OR ip LIKE '139.230.%' THEN 0 ELSE 1 END) AS "External" FROM tst1 GROUP BY page_name If you have an index on "ip", this should be pretty fast. -- 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: Self-Join Query
In article <[EMAIL PROTECTED]>, "James KATARSKI" <[EMAIL PROTECTED]> writes: > I'm trying to generate a report like this: (Which I've done in TWO > querys, the copied and pasted together) > +-+--+--+ > | Page Name | Internal | External | > +-+--+--+ > | after_hours | 615 | 105 | > | conditions | 332 | 50 | > | faq | 89 |2 | > | help| 458 | 174 | > | labtimes| 682 | 143 | > | support | 2151 | 383 | > | passwords | 154 | 22 | > | practise| 497 | 99 | > | printing| 801 | 85 | > | wireless| 926 | 180 | > +-+--+--+ > Using a query like this: > SELECT DISTINCT i.page_name AS 'Page Name', COUNT(i.page_name) as > 'Internal', COUNT(e.page_name) as 'External' > FROM ip_logs i, ip_logs e > WHERE (i.hit_no != e.hit_no) > AND (i.ip LIKE '10.%' OR i.ip LIKE '139.230.%') > #Internal Addresses > AND (e.ip NOT LIKE '10.%' AND e.ip NOT LIKE '139.230.%') > #External Addresses > GROUP BY i.page_name; You could try something like SELECT page_name AS "Page Name", sum(CASE WHEN ip LIKE '10.%' OR ip LIKE '139.230.%' THEN 1 ELSE 0 END) AS "Internal", sum(CASE WHEN ip LIKE '10.%' OR ip LIKE '139.230.%' THEN 0 ELSE 1 END) AS "External" FROM tst1 GROUP BY page_name If you have an index on "ip", this should be pretty fast. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Self-Join Query
You may want to forget all those joins and filters to create two columns, which is probably making MySQL do multiple full table scans. It sounds like what you are really looking for is to separate your hits into 2 columns based on the ip address, and perhaps put a filter on the hit time or page name. Create the counts for your separate columns by putting a condition in your column selection, setting the value for the column to a 1 or 0 depending on internal or external address and then sum those 1s and 0s. SELECT page_name, SUM(if(ip LIKE '10.%' OR ip LIKE '139.230.%',1,0)) as Internal SUM(if(ip NOT LIKE '10.%' AND ip NOT LIKE '139.230.%',1,0)) as External FROM ip_logs GROUP BY page_name That should be pretty quick and putting a WHERE condition on it should just making things faster, since the selection is being narrowed and indexes can be used. On Jun 2, 2004, at 11:35 PM, James KATARSKI wrote: Hi All, I'm attempting to generate a report of page hits from both internal and external IP addresses, from one table, using self join. Some sample data: ++-+++ | hit_no | page_name | ip | hit_time | ++-+++ | 6649 | printing| 10.77.1.128| 1061362239 | | 6650 | wireless| 10.77.28.100 | 1061365331 | | 6651 | after_hours | 10.77.31.101 | 1061365461 | | 6632 | labtimes| 10.77.25.102 | 1061350012 | | 6633 | after_hours | 10.77.25.102 | 1061350017 | | 6634 | labtimes| 10.77.25.102 | 1061350325 | | 6635 | practise| 10.77.30.114 | 1061350609 | | 6636 | support | 10.19.7.155| 1061352345 | | 6637 | help| 203.35.134.16 | 1061352351 | | 6638 | support | 10.19.7.156| 1061352352 | | 6639 | support | 10.19.7.151| 1061352387 | | 6640 | support | 10.19.7.159| 1061352411 | | 6621 | support | 10.19.7.158| 1061348961 | | 6620 | support | 10.19.6.112| 1061348628 | | 7318 | labtimes| 202.137.192.7 | 1063262879 | | 6284 | conditions | 10.77.31.109 | 1060605402 | | 7317 | practise| 202.137.192.7 | 1063262789 | | 7316 | wireless| 203.59.185.185 | 1063262707 | | 7315 | wireless| 10.77.28.121 | 1063256685 | ++-+++ I'm trying to generate a report like this: (Which I've done in TWO querys, the copied and pasted together) +-+--+--+ | Page Name | Internal | External | +-+--+--+ | after_hours | 615 | 105 | | conditions | 332 | 50 | | faq | 89 |2 | | help| 458 | 174 | | labtimes| 682 | 143 | | support | 2151 | 383 | | passwords | 154 | 22 | | practise| 497 | 99 | | printing| 801 | 85 | | wireless| 926 | 180 | +-+--+--+ Using a query like this: SELECT DISTINCT i.page_name AS 'Page Name', COUNT(i.page_name) as 'Internal', COUNT(e.page_name) as 'External' FROM ip_logs i, ip_logs e WHERE (i.hit_no != e.hit_no) AND (i.ip LIKE '10.%' OR i.ip LIKE '139.230.%') #Internal Addresses AND (e.ip NOT LIKE '10.%' AND e.ip NOT LIKE '139.230.%') #External Addresses GROUP BY i.page_name; The query takes between 30 seconds and 4.5 minutes to process 6000 rows, depending upon what extra WHERE conditions I put in (like i.hit_no = e.hit_no, or i.page_name = e.page_name etc), the result of which looks like: +-+--+--+ | Page Name | Internal | External | +-+--+--+ | after_hours | 638520 | 638520 | | conditions | 353064 | 353064 | | faq | 108924 | 108924 | | help| 355568 | 355568 | | labtimes| 674828 | 674828 | | oracle | 2213536 | 2213536 | | passwords | 165264 | 165264 | | practise| 498296 | 498296 | | printing| 896432 | 896432 | | wireless| 933992 | 933992 | +-+--+--+ Can anyone offer any suggestions as to the problem with my logic? Regards, James Katarski Systems Administrator School of Computer & Information Science Edith Cowan University ML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Self-Join Query
Hi All, I'm attempting to generate a report of page hits from both internal and external IP addresses, from one table, using self join. Some sample data: ++-+++ | hit_no | page_name | ip | hit_time | ++-+++ | 6649 | printing| 10.77.1.128| 1061362239 | | 6650 | wireless| 10.77.28.100 | 1061365331 | | 6651 | after_hours | 10.77.31.101 | 1061365461 | | 6632 | labtimes| 10.77.25.102 | 1061350012 | | 6633 | after_hours | 10.77.25.102 | 1061350017 | | 6634 | labtimes| 10.77.25.102 | 1061350325 | | 6635 | practise| 10.77.30.114 | 1061350609 | | 6636 | support | 10.19.7.155| 1061352345 | | 6637 | help| 203.35.134.16 | 1061352351 | | 6638 | support | 10.19.7.156| 1061352352 | | 6639 | support | 10.19.7.151| 1061352387 | | 6640 | support | 10.19.7.159| 1061352411 | | 6621 | support | 10.19.7.158| 1061348961 | | 6620 | support | 10.19.6.112| 1061348628 | | 7318 | labtimes| 202.137.192.7 | 1063262879 | | 6284 | conditions | 10.77.31.109 | 1060605402 | | 7317 | practise| 202.137.192.7 | 1063262789 | | 7316 | wireless| 203.59.185.185 | 1063262707 | | 7315 | wireless| 10.77.28.121 | 1063256685 | ++-+++ I'm trying to generate a report like this: (Which I've done in TWO querys, the copied and pasted together) +-+--+--+ | Page Name | Internal | External | +-+--+--+ | after_hours | 615 | 105 | | conditions | 332 | 50 | | faq | 89 |2 | | help| 458 | 174 | | labtimes| 682 | 143 | | support | 2151 | 383 | | passwords | 154 | 22 | | practise| 497 | 99 | | printing| 801 | 85 | | wireless| 926 | 180 | +-+--+--+ Using a query like this: SELECT DISTINCT i.page_name AS 'Page Name', COUNT(i.page_name) as 'Internal', COUNT(e.page_name) as 'External' FROM ip_logs i, ip_logs e WHERE (i.hit_no != e.hit_no) AND (i.ip LIKE '10.%' OR i.ip LIKE '139.230.%') #Internal Addresses AND (e.ip NOT LIKE '10.%' AND e.ip NOT LIKE '139.230.%') #External Addresses GROUP BY i.page_name; The query takes between 30 seconds and 4.5 minutes to process 6000 rows, depending upon what extra WHERE conditions I put in (like i.hit_no = e.hit_no, or i.page_name = e.page_name etc), the result of which looks like: +-+--+--+ | Page Name | Internal | External | +-+--+--+ | after_hours | 638520 | 638520 | | conditions | 353064 | 353064 | | faq | 108924 | 108924 | | help| 355568 | 355568 | | labtimes| 674828 | 674828 | | oracle | 2213536 | 2213536 | | passwords | 165264 | 165264 | | practise| 498296 | 498296 | | printing| 896432 | 896432 | | wireless| 933992 | 933992 | +-+--+--+ Can anyone offer any suggestions as to the problem with my logic? Regards, James Katarski Systems Administrator School of Computer & Information Science Edith Cowan University ML -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join query for 3 tables
I have three tables: users <- holds user details courses <- holds course information registrations <- holds id values which specify which course did a user register to eg. user #1 registered for course #4 I tried but to no avail to create a query where all users are selected, all their registrations and the course data except so far I can only do it to indicate those who registered, I would like to do it the way LEFT JOIN does so I end up with all users, their registrations and names of the courses they registered in one query. eg. Robert Smith | French Mark Mark | Danny Dan | French no registrations would mean empty value. Thanks is advance R> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
left outer join query -- maybe?
Hello all, I have three queries that I need to join together and cannot figure out how. The first query returns 161 records and I want all these records with nulls in the fields added with queries 2 and 3. Any help will be greatly appreciated!! -- QUERY 1 -- Returns 161 rows -- #customer data -- SLM = 110 select s.address_name as SALESMAN, c.slm_number, c.cust_code, c.ship_to_name, format(c.mtd_status_inv_sale,2) as MTD_SATUS, format((c.mtd_status_inv_sale - c.mtd_status_inv_cost) / c.mtd_status_inv_sale,2) as MTD_MARGIN, format(c.ytd_status_inv_sale,2) as YTD_STATUS, format((c.ytd_status_inv_sale - c.ytd_status_inv_cost) / c.ytd_status_inv_sale,2) as YTD_MARGIN, format(c.ly_sales,2) as LY_SALES, format((c.ly_sales - c.ly_cost) / c.ly_sales,2) as LY_MARGIN from customer c, salesman s where s.slm_number = c.slm_number and s.slm_number = 110 order by s.address_name, c.ytd_status_inv_sale desc; -- QUERY 2 -- Returns 89 rows -- #wbw_line data -- YTD -- SLM = 110 select w.slm_number, w.cust_code, DATE_FORMAT(w.INV_DATE, '%Y') AS "YEAR", format(sum((w.UT_PRICE * w.MULTIPLIER) * w.INV_QTY),2) as "GROSS_SALES" from wbw_line w, salesman s where s.slm_number = w.slm_number AND YEAR(w.inv_date) = YEAR(CURRENT_DATE) and s.slm_number = 110 group by s.slm_number, cust_code order by s.address_name, GROSS_SALES desc; -- QUERY 3 -- Returns 96 rows -- #wbw_line data -- last year -- SLM = 110 select w.slm_number, w.cust_code, DATE_FORMAT(w.INV_DATE, '%Y') AS "YEAR", format(sum((w.UT_PRICE * w.MULTIPLIER) * w.INV_QTY),2) as "GROSS_SALES" from wbw_line w, salesman s where s.slm_number = w.slm_number AND YEAR(w.inv_date) = YEAR(CURRENT_DATE)-1 and s.slm_number = 110 group by w.slm_number, cust_code order by s.address_name, GROSS_SALES desc; Thanks, Charles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select with join query question
Richard Bolen wrote: This gives the count per job which is always 1. Oops! Quite right. I don't see a way to get the total off-hand. Bruce -Original Message- From: Bruce Feist [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 7:42 PM To: Richard Bolen Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Select with join query question Richard Bolen wrote: This works! I was then wondering how to get the total number of all jobs that this condition is true for? Just include count(distinct j.jobid) in the SELECT list. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Select with join query question
This gives the count per job which is always 1. I'm using the 'having' clause which requires the 'status' field in the select list. This makes it difficult to get a total. I'll play with the 'where' clause example to see if that works. Thanks again! -Original Message- From: Bruce Feist [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 7:42 PM To: Richard Bolen Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Select with join query question Richard Bolen wrote: >This works! I was then wondering how to get the total number of all >jobs that this condition is true for? > Just include count(distinct j.jobid) in the SELECT list. Bruce >>select j.* >>FROM Jobs j LEFT JOIN Submissions s ON j.jobid = s.jobid GROUP BY /* >>all selected columns */ HAVING min(abs(s.status - 1)) > 0 >> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select with join query question
Richard Bolen wrote: This works! I was then wondering how to get the total number of all jobs that this condition is true for? Just include count(distinct j.jobid) in the SELECT list. Bruce select j.* FROM Jobs j LEFT JOIN Submissions s ON j.jobid = s.jobid GROUP BY /* all selected columns */ HAVING min(abs(s.status - 1)) > 0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Re: Select with join query question
This works! I was then wondering how to get the total number of all jobs that this condition is true for? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 5:26 PM To: Bruce Feist Cc: Richard Bolen; [EMAIL PROTECTED] Subject: Re: Re: Select with join query question [snip] > Rich's solution, which I edited out, was a good one. But, if you > really > want to do it with a single JOIN, try this: > > select j.* > FROM Jobs j LEFT JOIN Submissions s ON j.jobid = s.jobid GROUP BY /* > all selected columns */ HAVING min(abs(s.status - 1)) > 0 > > I leave it as an exercise to the reader to figure out why this works > (if > it does -- I haven't tested it!). If status = 1 is the lowest possible > value for status, you can simplify this a bit. A quick test seems to show it works. Though it doesn't pick up the case where status IS NULL, which occurs when there's a job but no matching submission. One disadvantage to your method: it requires computing a formula for each tuple, which slows things down (in principle; not sure it really matters in practice). > > Bruce Feist > > > > -- > 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: Re: Select with join query question
[snip] > Rich's solution, which I edited out, was a good one. But, if you really > want to do it with a single JOIN, try this: > > select j.* > FROM Jobs j LEFT JOIN Submissions s ON j.jobid = s.jobid > GROUP BY /* all selected columns */ > HAVING min(abs(s.status - 1)) > 0 > > I leave it as an exercise to the reader to figure out why this works (if > it does -- I haven't tested it!). If status = 1 is the lowest possible > value for status, you can simplify this a bit. A quick test seems to show it works. Though it doesn't pick up the case where status IS NULL, which occurs when there's a job but no matching submission. One disadvantage to your method: it requires computing a formula for each tuple, which slows things down (in principle; not sure it really matters in practice). > > Bruce Feist > > > > -- > 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: Select with join query question
I was wondering if you were going to come back with that. Your going to need to using grouping then. Something like this should do it: SELECT *,SUM(s.status) AS ActiveJob FROM Jobs AS j LEFT JOIN Submissions AS s ON j.job_id=s.job_id WHERE ActiveJob<1 OR ActiveJob IS NULL GROUP BY j.job_id I'm not sure what type of data is in your status field, so I'm not sure if SUM is the right thing you are looking. The above query should give you all jobs without any Sumission records and those that have matching submission records but whose status ends up to be zero. You may not need to check for NULL values, I forget if MySQL considers NULL less than 1 or anything else. I don't think it does. On Monday, July 28, 2003, at 04:12 PM, Richard Bolen wrote: I think this gets me all the Jobs that have no submissions but I'm really looking for any job that doesn't have a submission with a status of 1. That means I need Jobs that don't have submissions plus jobs with submissions with exclusively non-1 statuses. The problem is when a job has more than one submission associated with it (and at least one submission has a non-1 status). -- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select with join query question
[EMAIL PROTECTED] wrote: From: "Richard Bolen" <[EMAIL PROTECTED]> Date: 2003/07/28 Mon PM 01:37:27 CDT I'm trying to write a select query that involves 2 tables. One table (Submissions) has a one to many relationship with the other table (Jobs). I'm trying to find all the records in Jobs that do NOT have a corresponding record in Submissions with a status of 1. I haven't been able to think of a clever way to do it using only JOINs. Rich's solution, which I edited out, was a good one. But, if you really want to do it with a single JOIN, try this: select j.* FROM Jobs j LEFT JOIN Submissions s ON j.jobid = s.jobid GROUP BY /* all selected columns */ HAVING min(abs(s.status - 1)) > 0 I leave it as an exercise to the reader to figure out why this works (if it does -- I haven't tested it!). If status = 1 is the lowest possible value for status, you can simplify this a bit. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select with join query question
> > From: "Richard Bolen" <[EMAIL PROTECTED]> > Date: 2003/07/28 Mon PM 01:37:27 CDT > To: <[EMAIL PROTECTED]> > Subject: Select with join query question > > I'm trying to write a select query that involves 2 tables. One table > (Submissions) has a one to many relationship with the other table > (Jobs). I'm trying to find all the records in Jobs that do NOT have a > corresponding record in Submissions with a status of 1. > > The problem I'm having is that when there is more than one record in > Submissions that match a record in Jobs and one Submissions record has a > status of 1 and one record doesn't, my query matches the one with status > != 1 and returns the record for Jobs (even though it has a record in > Submissions with a status of 1 also). I haven't been able to think of a clever way to do it using only JOINs. Moreover, there are probably clever/elegant solutions using subselects (that is, nested queries), but your version of MySQL doesn't have those. Here's how I would do it. (Sorry if my notation deviates from yours a little): CREATE TEMPORARY TABLE tmp SELECT jobs.id AS jobs_id FROM jobs LEFT JOIN sub ON jobId=jobs.id WHERE status=1; SELECT jobs.id FROM jobs LEFT JOIN tmp ON jobs.id=jobs_id WHERE jobs_id IS NULL; > > I've tried a variety of queries including left outer joins and more > simple join relationships. I'm using MySQL 3.23.47 on Windows. > > Here's an example query: > > select j.job_id from jobs j left outer join submissions s on (j.job_id = > s.job_id) where s.status_id != 1 group by j.job_id > > Any help is greatly appreciated. > > Rich > > -- > 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]
