On 10/22/2015 11:48 AM, Don Wieland wrote:
On Oct 20, 2015, at 1:24 PM, shawn l.green wrote:
Which release of MySQL are you using?
Version 5.5.45-cll
How many rows do you get if you remove the GROUP_CONCAT operator? We don't need
to see the results. (sometimes it is a good idea to look
> On Oct 20, 2015, at 1:24 PM, shawn l.green wrote:
>
> Which release of MySQL are you using?
Version 5.5.45-cll
> How many rows do you get if you remove the GROUP_CONCAT operator? We don't
> need to see the results. (sometimes it is a good idea to look at the raw,
> unprocessed results)
>
On 10/20/2015 1:54 PM, Don Wieland wrote:
Hi all,
Trying to get a query working:
SELECT
ht.*,
CONCAT(o.first_name, " ", o.last_name) AS orphan,
GROUP_CONCAT(DISTINCT hti.rec_code ORDER BY hti.rec_code ASC SEPARATOR ", ") AS
alloc
FROM hiv_transactions ht
LEFT JOIN tk_orphans o ON ht.orphan
On 2015-10-20 12:54 PM, Don Wieland wrote:
Hi all,
Trying to get a query working:
SELECT
ht.*,
CONCAT(o.first_name, " ", o.last_name) AS orphan,
GROUP_CONCAT(DISTINCT hti.rec_code ORDER BY hti.rec_code ASC SEPARATOR ", ") AS
alloc
FROM hiv_transactions ht
LEFT JOIN tk_orphans o ON ht.orphan_
Perfect! Thank you Larry et all.
Have a great weekend.
2013/4/19 Larry Martell
> On Fri, Apr 19, 2013 at 8:24 AM, Richard Reina
> wrote:
> > Hello All,
> >
> > Happy Friday! I know how to do the following query:
> >
> >>select count(*) from sales where WEEK(sale_date)=15 AND
> > YEAR(sale_dat
On Fri, Apr 19, 2013 at 8:24 AM, Richard Reina wrote:
> Hello All,
>
> Happy Friday! I know how to do the following query:
>
>>select count(*) from sales where WEEK(sale_date)=15 AND
> YEAR(sale_date)=2013;
>
> But can someone tell me I can do a query that will give me:
>
> the count(*) for each w
Hello Richard,
>the count(*) for each week of 2013 so that I end up with:
http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html
Ilya.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
of course, "Group By"
bill
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
On 3/31/2013 7:32 AM, william drescher wrote:
I have a table, schedule:
CREATE TABLE `schedule` (
`schedule_id` mediumint(9) NOT NULL AUTO_INCREMENT,
`provider` varchar(15) NOT NULL,
`apptTime` datetime NOT NULL,
`location` varchar(10) NOT NULL,
`duration` smallint(5) unsigned NOT
9 AM
> To: 'Richard Reina'; mysql@lists.mysql.com
> Subject: RE: query help
>
> I think this will get you there:
>
> SELECT LEAD, COUNT(*) FROM ORDERS GROUP BY LEAD;
>
> It'll give you something more like:
>
> | LEAD | COUNT(*) |
> | F | 44
I think this will get you there:
SELECT LEAD, COUNT(*) FROM ORDERS GROUP BY LEAD;
It'll give you something more like:
| LEAD | COUNT(*) |
| F | 44 |
| S | 122 |
| R | 32 |
-Original Message-
From: Richard Reina [mai
2012/07/31 15:02 -0700, Haluk Karamete
So, in a case like this
shop.orders.32442
shop.orders.82000
shop.orders.34442
It would be the record whose source_recid is shop.orders.82000. Why? Cause
82000 happens to be the largest integer.
Now, if they are always 5-digit-long integer
Hi,
Use LIMIT 1 to limit the number of output to single record.
Regards,
Vikas Shukla
On Wed, Aug 1, 2012 at 3:56 AM, Paul Halliday wrote:
> On Tue, Jul 31, 2012 at 7:02 PM, Haluk Karamete
> wrote:
> > I've got a text field called source_recid. It stores half string half
> > number like str
L
> Subject: Re: query help
>
> On Tue, Jul 31, 2012 at 7:02 PM, Haluk Karamete
> wrote:
> > I've got a text field called source_recid. It stores half string half
> > number like strings in it.
> >
> > Example
> >
> > shop.orders.32442
> >
On Tue, Jul 31, 2012 at 7:02 PM, Haluk Karamete wrote:
> I've got a text field called source_recid. It stores half string half
> number like strings in it.
>
> Example
>
> shop.orders.32442
>
> the syntax is DATABASENAME.TABLENAME.RECID
>
> My goal is to scan this col and find out the biggest RECI
2012/05/28 08:03 -0700, Don Wieland
Any assistance would be appreciated. Thanks!
Maybe something like this:
SELECT usie.client_id, first_name, last_name, COUNT(anie.client_id)
FROM
(SELECT client_id, first_name, last_name, time_start
FROM tl_appt
JOIN tl_rooms USING(room_id)
On 2012-05-17 9:37 AM, Don Wieland wrote:
Hi folks,
I am trying to compile a query that does statistics on appointments
based on specific criteria. Here is my starting query:
SELECT
u.user_id,
c.client_id,
c.first_name,
c.last_name,
a.time_start AS stime,
FROM_UNIXTIME
For (1), ponder:
Group-by trick example: Find the most populous city in each state:
SELECT state, city, population, COUNT(*) AS num_cities
FROM
( SELECT state, city, population
FROM us
ORDER BY state, population DESC ) p
GROUP BY state
ORDER BY stat
On 2/29/2012 1:15 PM, Don Wieland wrote:
Little help...
In my mySQL query editor, I am trying to return a value of 0 when
there is no related rows from this query:
(select if(count(ip.payment_amount) IS NOT NULL,
count(ip.payment_amount) , 0) FROM tl_trans_pmt_items ip WHERE
t.transaction_id =
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull
On Wed, Feb 29, 2012 at 13:15, Don Wieland wrote:
> Little help...
>
> In my mySQL query editor, I am trying to return a value of 0 when there is
> no related rows from this query:
>
> (select if(count(ip.payment_
lto:neil.tompk...@googlemail.com]
>Sent: Wednesday, March 02, 2011 10:12 AM
>To: Jerry Schwartz
>Cc: [MySQL]
>Subject: Re: Query help
>
>Thanks for the response. This is what I was after. Although, I am looking
>to find out the email addresses used to login from the same IP
Thanks for the response. This is what I was after. Although, I am looking
to find out the email addresses used to login from the same IP ?
On Wed, Mar 2, 2011 at 2:49 PM, Jerry Schwartz wrote:
>
> >-Original Message-
> >From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
> >Sent:
>-Original Message-
>From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
>Sent: Wednesday, March 02, 2011 6:00 AM
>To: [MySQL]
>Subject: Query help
>
>Hi
>
>I've the following basic table
>
>login_id
>email_address
>ip_address
>
>I want to extract all records from this table in which
Hi Neil,
select
login_id,
ip_address
from
basic_table
group by
login_id,ip_address
having
count(login_id,ip_address)>1
this should work
in case you want to see also the list of emails add:
group_concat(email_address,',') as list_of_used_emails
to the select fields.
Claudio
On 10/27/2010 6:55 AM, Nuno Mendes wrote:
I have 3 tables: (1) Companies, (2) locations and (3) employees:
CREATE TABLE `companies` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(75) NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `locations ` (
`id` i
For sure here is some sample data
home_teams_id,away_teams_id,home_goals,away_goals,home_users_id,away_users_id
100,200,2,1,5,6
200,100,1,1,6,5
Here is two rows of data for the same fixture both home and away
Let me know if you need any more info.
Cheers
Neil
On Mon, Sep 6, 2010 at 1:08 PM,
Also, can u please lets u know the value's in this table.
Just one row, an example would do.
regards
anandkl
On Mon, Sep 6, 2010 at 5:35 PM, Tompkins Neil
wrote:
> These two fields
>
> home_goals and away_goals
>
> Cheers
> Neil
>
>
> On Mon, Sep 6, 2010 at 12:58 PM, Ananda Kumar wrote:
>
>>
These two fields
home_goals and away_goals
Cheers
Neil
On Mon, Sep 6, 2010 at 12:58 PM, Ananda Kumar wrote:
> Tompkins,
> Which field stores the result of matches.
>
> regards
> anandkl
>
> On Mon, Sep 6, 2010 at 4:45 PM, Tompkins Neil <
> neil.tompk...@googlemail.com> wrote:
>
>> Hi,
>>
>> I
Tompkins,
Which field stores the result of matches.
regards
anandkl
On Mon, Sep 6, 2010 at 4:45 PM, Tompkins Neil
wrote:
> Hi,
>
> I've the following fields within a table :
>
> fixtures_results_id
> home_teams_id
> away_teams_id
> home_goals
> away_goals
> home_users_id
> away_users_id
>
> From
Something like this might work:
insert into domains
select a.accountid, reverse(a.domainid), a.mailname
from domains a
left outer join domains b on b.domainid = reverse(a.domainid) and
b.accountid = a.accountid and b.mailname = a.mailname
where b.domainid is null;
-Travis
Table 1
Product_id | Product_Name
1| Product A
2| Product B
3| Product C
Table 2
Category_id | Category_Name
1 | Admin
2 | Marketing
3 | Support
4 | IT
Table 3
Product_id
[snip]
For the life of me I cannot remember how to make a query like this and
what
it is called.
I know it is fairly basic though.
Table 1
Product_id Product_Name
Table 2
Category_id, Category_name
Table 3
Product_id, Category_id
Each product can have one or more categories.
So I want a result
I believe you're describing a crosstab query. This should help you put it
together:
http://rpbouman.blogspot.com/2005/10/creating-crosstabs-in-mysql.html
---Michael
On Friday, July 09, 2010 07:37:41 pm Phillip Baker wrote:
> Hello All,
>
>
> For the life of me I cannot remember how to make a
Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
> From: rich...@rushlogistics.com
> To: joerg.bru...@sun.com; mysql@lists.mysql.com
> Subject: Re: query help
>
Thank you very much for all the insightful replies. I think I can get it to
work with a join.
Joerg Bruehe wrote:
>
> Hi!
>
>
> Jay Blanchard wrote:
> > [snip]
> > I have a table similar to this:
> >
> > -
> > |transactions |
> > |ID |DATE |
Hi!
Jay Blanchard wrote:
> [snip]
> I have a table similar to this:
>
> -
> |transactions |
> |ID |DATE |EMPLOYEE|
> |234 |2010-01-05| 345|
> |328 |2010-04-05| 344|
> |239 |2010-01-10| 344|
>
> Is there a way to query such a table to gi
[snip]
I have a table similar to this:
-
|transactions |
|ID |DATE |EMPLOYEE|
|234 |2010-01-05| 345|
|328 |2010-04-05| 344|
|239 |2010-01-10| 344|
Is there a way to query such a table to give the days of the year that
employee 344 did not
Hi Richard,
This is a LEFT JOIN, but with only one table you may be wondering what to join.
Typically you'll have to create a new table that contains all the dates in the
range you want to check. Then you left join your transaction table, and all
the rows from your dates table that don't have
Not tested, but I think it can help you or at least give you an ideia on how
to do it.
select
EndDateTime + INTERVAL 1 SECOND as startLazy,
(select StartDateTime - INTERVAL 1 SECOND from table t2 where
t2.StartDateTime > t1.EndDateTime limit 1) as endLazy
from
table t1
where
(select Star
Hmm. You seem to have overlap, too. I suspect this would be easiest to do in
code - the data you're looking for doesn't exist in the data you have, only
the opposite of that data does.
You could try populating a table with a full day, using the resolution you
need (1 minute resolution means 1440 r
SELECT ID, check_no, amount FROM payables UNION SELECT ID, check_no, amount
FROM paychecks;
Regards,
Gavin Towey
-Original Message-
From: Richard Reina [mailto:rich...@rushlogistics.com]
Sent: Tuesday, February 09, 2010 9:23 AM
To: mysql@lists.mysql.com
Subject: query help
I am trying
SELECT count(distinct trans_no) from SEARCHES WHERE comp_id=675 and
result='o';
- Original Message -
From: "Richard Reina"
To:
Cc:
Sent: Sunday, December 13, 2009 11:36 AM
Subject: Query help
I was wondering if someone could lend a hand with the following query. I
have table.
On December 13, 2009 01:36:41 pm Richard Reina wrote:
> I was wondering if someone could lend a hand with the following query. I
> have table.
>
> SEARCHES
>
> |ID |trans_no|comp_id|result
>
> 13 | 455| 675| o
> 15 | 302| 675| o
> 16 | 455| 675| o
> 12 | 225|
Depending on whether you just need to count or the transaction numbers, one of
the following three should get you where you want/need to be:
To identify the count for comp_id = 675:
select count(distinct trans_no) from trans where comp_id = 675 and result = 'o';
To identify the transactions:
sel
: mysql@lists.mysql.com
Subject: RE: Query Help
"Ben Wiechman" wrote on 02/10/2009 01:30:14 PM:
> Thanks for the input! That is close to what I need, however not exactly.
It
> will give me the last time a user logged into the host in question but I
> want to prune users who hav
"Ben Wiechman" wrote on 02/10/2009 01:30:14 PM:
> Thanks for the input! That is close to what I need, however not exactly.
It
> will give me the last time a user logged into the host in question but I
> want to prune users who have since logged into a different host.
Basically
> find out how man
Alright to reply to myself I can return the information but have been unable
to return the last row... instead it always returns the first row. Tried
max, tried min, tried converting the datetime to a timestamp with the same
results...
mysql> SELECT da_userinfo.UserName, da_userinfo.Name, radacct
he host in question the information is
returned. This produces too many results as some of those users have since
migrated to a different access point.
-Original Message-
From: Andrew Wallo [mailto:theme...@microneil.com]
Sent: Tuesday, February 10, 2009 12:05 PM
To: Ben Wiechman
Subject: Re:
Anders,
>I also want to find out the user's position relative to others
depending on the result.
For a given pUserID, something like this?
SELECT userid,result,rank
FROM (
SELECT o1.userid,o1.result,COUNT(o2.result) AS rank
FROM object o1
JOIN object o2 ON o1.result < o2.result OR (o1.resu
On Dec 11, 2007, at 10:46 AM, Rob Wultsch wrote:
On Dec 11, 2007 8:38 AM, Anders Norrbring <[EMAIL PROTECTED]> wrote:
I'm looking at a situation I haven't run into before, and I'm a bit
puzzled by it.
I have this table structure:
Table USERS: userid, class
Table OBJECT: userid, class, result
On Dec 11, 2007 8:38 AM, Anders Norrbring <[EMAIL PROTECTED]> wrote:
> I'm looking at a situation I haven't run into before, and I'm a bit
> puzzled by it.
>
> I have this table structure:
>
> Table USERS: userid, class
> Table OBJECT: userid, class, result
>
> Now I want to query the database for
I only want to view the product per product name
which has the lowest price.
SELECT product_name,MIN(price)
FROM tbl
GROUP BY product_name;
PB
uYe wrote:
Hi,
I have a table with product_name, price field. The product name may be exist
in more than one record. I only want to view the product p
Thank u so much. It woked now!
- Original Message -
From: "Visolve DB Team" <[EMAIL PROTECTED]>
To: "Visolve DB Team" <[EMAIL PROTECTED]>; "Renish"
<[EMAIL PROTECTED]>;
Sent: Tuesday, October 10, 2006 1:42 PM
Subject: Re: Query
ctober 10, 2006 11:03 AM
Subject: Re: Query Help plss
Hi
The .err file shows that the service is stopped Normally and no error was
found. sure the service is properly shutdown.
Try,
a)
net stop mysql
mysqld-nt remove
mysqld-nt install
net start mysql
b)
Also run 'services.msc' a
isolve DB Team ; mysql@lists.mysql.com
Sent: Tuesday, October 10, 2006 12:35 PM
Subject: Fw: Query Help plss
- Original Message -
From: Renish
To: mysql@lists.mysql.com ; Visolve DB Team
Sent: Tuesday, October 10, 2006 12:34 PM
Subject: Re: Query Help plss
b) And what
Xp
- Original Message -
From: "Visolve DB Team" <[EMAIL PROTECTED]>
To: "Renish koshy" <[EMAIL PROTECTED]>
Sent: Tuesday, October 10, 2006 11:42 AM
Subject: Re: Query Help plss
Hi,
On which platform?
Thanks
ViSolve DB Team.
- Original Message ---
Renish koshy wrote:
Hi all,
I have installed MySql 4.1 in my system. when I tried to run Mysql , I
always gets this error.
Could not start MySql service on a local computer
Error 1067: the process terminated unexpectedly.
Stop sending this message!
We get the idea!
The more you send it, th
27; OR (NST.VAL = 'Fred' AND NSV.REF_ID IS NULL)
)
How do I generally simplify this?
R.
-Original Message-
From: Robert DiFalco [mailto:[EMAIL PROTECTED]
Sent: Friday, August 04, 2006 4:12 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: RE: Query Help for Loosely Coupl
e-
From: Jay Pipes [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 02, 2006 9:37 AM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re: Query Help for Loosely Couple Properties
On Tue, 2006-08-01 at 16:23 -0700, Robert DiFalco wrote:
> The question is, how do I query this? Say I w
On Tue, 2006-08-01 at 16:23 -0700, Robert DiFalco wrote:
> The question is, how do I query this? Say I want all records from table
> T whose COLOR property value is ORANGE.
>
> The only thing I can come up with (and I'm no SQL expert and this looks
> wrong to me) is the following:
>
> SELECT *
>
They are user defined properties.
-Original Message-
From: Jay Pipes [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 01, 2006 8:11 PM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re: Query Help for Loosely Couple Properties
On Tue, 2006-08-01 at 16:23 -0700, Robert DiFalco
On Tue, 2006-08-01 at 16:23 -0700, Robert DiFalco wrote:
> I have a table that contains properties that can be associated with any
> table whose primary key is a LONG. Lets say that there is just one kind
> of property. The table looks something like this:
>
> TABLE StringVal
> REF_ID B
l.com
Subject: Re: query help-multiple joins
Date: Tue, 23 May 2006 13:16:33 +0200
Hi,
I guess you should be able to do something like:
SELECT t1.term_id, t1.name, r.type_id, t2.term_id, t2.name
FROM term t1
LEFT JOIN relationTerm r ON r.term_id1 = t1.term_id
LEFT JOIN term t2 ON r.term_id2 = t2.ter
Hi,
I guess you should be able to do something like:
SELECT t1.term_id, t1.name, r.type_id, t2.term_id, t2.name
FROM term t1
LEFT JOIN relationTerm r ON r.term_id1 = t1.term_id
LEFT JOIN term t2 ON r.term_id2 = t2.term_id
/Johan
mel list_php skrev:
Hi!
I'm stuck with a join query
2 tables
Thanks that got it.
Robert Gehrig
Webmaster at www.gdbarri.com
e-mail: [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT id, count(*) AS cnt
FROM `table_name`
GROUP BY id
ORDER BY cnt DESC
[ LIMIT 1 ]
--
Gabriel PREDA
Senior Web Developer
2wsxdr5 wrote:
> I have a table of people. Some of the people in this table are
> related. You can find out who is related by comparing a familyID
> number. I have a query to select a certain group of people from the
> table and I want to also select anyone who is related to them, even
> though
OKAN ARI wrote:
I have 3 tables
Table 1: user(id, name, surname)
Table 2: crime(id, detail)
Table 3: user_crime(id, user_id, crime_id)
Table 1
1, OKAN, ARI
Table 2
1, "detail 1"
2, "Detail 2"
Table 3
1, 1, 1
1, 1, 2
So user 1 takes 2 crime from crime table...
I want to receive info with 1 qu
Actually I am looking for duplicates (vedor_no, date), but I think I can
hopefully adapt the solution you have given me.
[EMAIL PROTECTED] wrote:
If you are looking just for duplicate (ID,vendort_no) combinations, this will
find them:
SELECT ID, vendor_no, count(1) as dupes
FROM table_name
If you are looking just for duplicate (ID,vendort_no) combinations, this
will find them:
SELECT ID, vendor_no, count(1) as dupes
FROM table_name_here
GROUP BY ID, vendor_no
HAVING dupes >1;
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Richard Reina <[EMAIL PROTECTED]> wr
I's so sorry. You are very correct. The sample data is bad. ID should be
unique. Here it is corrected.
|ID| vendor_no| date|
|2354 | 578 | "2005-12-23"|
|2355 | 334 | "2005-12-24"|
|2356 | 339 | "2005-12-26"|
|2357 | 339
Richard,
If you mean with _both_ the same id _and_ vendor id, try this:
Select id, vendor_id, count(*) from tablename group by id, vendor_id;
If you just want separate counts for id and vendor_id, use:
Select id, count(*) from tablename group by id;
Select vendor_id, count(*) from tablename gr
> I am a novice when it come to queries such as this and was hoping
> someone could help me write a query that tells me how many records
> have the same ID and vendor number.
>
> |ID| vendor_no| date|
> |2354 | 578 | "2005-12-23"|
> |2355 | 334 |
rk [mailto:[EMAIL PROTECTED]]
Sent: 24 January 2006 09:50 PM
To: Ian Barnes
Cc: mysql@lists.mysql.com
Subject: Re: Query Help
Ian Barnes wrote:
Hi,
This is my current query which works in mysql 4, but not in 5. Its from
mambo, but im trying to modify it because they don't officially s
R BY
> cc.ordering, cc.title, c.ordering LIMIT 0,10;
>
> Thanks,
> Ian
>
> -Original Message-
> From: gerald_clark [mailto:[EMAIL PROTECTED]
> Sent: 24 January 2006 09:50 PM
> To: Ian Barnes
> Cc: mysql@lists.mysql.com
> Subject: Re: Query Help
>
> Ian
1' ORDER BY
cc.ordering, cc.title, c.ordering LIMIT 0,10;
Thanks,
Ian
-Original Message-
From: gerald_clark [mailto:[EMAIL PROTECTED]
Sent: 24 January 2006 09:50 PM
To: Ian Barnes
Cc: mysql@lists.mysql.com
Subject: Re: Query Help
Ian Barnes wrote:
>Hi,
>
>This is my curr
Ian Barnes wrote:
Hi,
This is my current query which works in mysql 4, but not in 5. Its from
mambo, but im trying to modify it because they don't officially support
mysql5 yet.
The original query:
SELECT c.*, g.name AS groupname, cc.name, u.name AS editor, f.content_id AS
frontpage, s.title A
grKumaran wrote:
> Hello,
>
> CREATE TABLE t (
> dt datetime
> )
>
> Please assume this is the table structure and contains thousands of
> records. And I want to list them only last 12 months (that mean last 1
> year) records exactly.
>
> For that I tried using the following query, but it list som
R,
>I tried using the following query, but it list sometimes 13 months
>when the current date is in the middle of the month.
SELECT * FROM t WHERE DATE_ADD(dt, INTERVAL 1 YEAR) >= NOW();
Try...
DATE_ADD( dt, INTERVAL
IF(YEAR(NOW())%4>0 AND YEAR(NOW())%100=0,366,365)
DAY ) >= NO
[EMAIL PROTECTED] wrote on 10/18/2005 06:28:03 AM:
> Hello,
>
>
> CREATE TABLE t (
> dt datetime
> )
>
> Please assume this is the table structure and contains thousands of
records.
> And I want to list them only last 12 months (that mean last 1 year)
records
> exactly.
>
> For that I tried
[EMAIL PROTECTED] wrote on 10/11/2005 01:27:12 PM:
>
>
> lets say i have two tables:
>
> module
> -
> VARCHAR module_name
> INTEGER module_id
>
> module_config
>
> INTEGER module_id
> VARCHAR config_name
> VARCHAR config_value
>
>
> config item names and values are r
[EMAIL PROTECTED] wrote:
lets say i have two tables:
module
-
VARCHAR module_name
INTEGER module_id
module_config
INTEGER module_id
VARCHAR config_name
VARCHAR config_value
config item names and values are rather arbitrary and depend on the module.
each module can h
Balazs Bagi <[EMAIL PROTECTED]> wrote on 08/24/2005 05:10:55 AM:
> Hi there, I'm kind of new to joining two tables. Please bear with me
> with this simple example. I have three tables.
>
> Guests is a list of guests that are coming to the party. The primary
> key of this table is 'id' and the
Jason Chan wrote:
Jason Chan wrote:
I have a student Table and a SubjectGrade table
Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)
Create Table SubjectGrade(
StudentID INT NOT NULL,
Subject VARCHAR(30) NOT NULL,
Grade CHAR(1)
)
let's say have following record in
Thanks Scott!
"Scott Noyes" <[EMAIL PROTECTED]> ???
news:[EMAIL PROTECTED] ???...
> SELECT s.StudentID, StudentName
> FROM Student s
> JOIN StudentGrade sg1 USING (StudentID)
> JOIN StudentGrade sg2 USING (StudentID)
> WHERE
> sg1.Subject = 'Maths' AND sg1.Grade = 'A'
> AND sg2.Subject = 'C
Jason Chan wrote:
> I have a student Table and a SubjectGrade table
>
> Create Table Student(
> StudentID INT NOT NULL,
> StudentName VARCHAR(30)
> )
>
> Create Table SubjectGrade(
> StudentID INT NOT NULL,
> Subject VARCHAR(30) NOT NULL,
> Grade CHAR(1)
> )
>
> let's say have following record in
> SELECT s.StudentID, StudentName
> FROM Student s
> JOIN StudentGrade sg1 USING (StudentID)
> JOIN StudentGrade sg2 USING (StudentID)
> WHERE
> sg1.Subject = 'Maths' AND sg1.Grade = 'A'
> AND sg2.Subject = 'Chem' AND sg2.Grade = 'A'
> So i have to write 3 join if I have 3 conditions and s
So i have to write 3 join if I have 3 conditions and so on, right?
"Scott Noyes" <[EMAIL PROTECTED]> ???
news:[EMAIL PROTECTED] ???...
> Select s.StudentID, s.StudentName from Student s, SubjectGrade sg where
> s.StudentID = sg.StudentID and sg.Subject = 'Maths' and sg.Subject =
'Chem'
> and sg.Gr
I havn't write my schema clearly , (StudentID, Subject) is the key of
SubjectGrade
"Philippe Poelvoorde" <[EMAIL PROTECTED]> ???
news:[EMAIL PROTECTED] ???...
> Alternatively, use an IN list for the subject, then use a HAVING clause
> to limit the results to students with 2 matching rows, like th
Arno Coetzee wrote:
Jason Chan wrote:
I have a student Table and a SubjectGrade table
I want to find out students who have got A in both Maths and Chem
How the SQL look like?
select s.StudentID , s.StudentName
from Student as s , SubjectGrade as sj
where s.studentID = sj.studentID and s
Jason Chan wrote:
>I have a student Table and a SubjectGrade table
>
>Create Table Student(
>StudentID INT NOT NULL,
>StudentName VARCHAR(30)
>)
>
>Create Table SubjectGrade(
>StudentID INT NOT NULL,
>Subject VARCHAR(30) NOT NULL,
>Grade CHAR(1)
>)
>
>let's say have following record in SubjectGrad
Alternatively, use an IN list for the subject, then use a HAVING clause
to limit the results to students with 2 matching rows, like this:
SELECT StudentID, StudentName
FROM Student s
JOIN SubjectGrade sg ON s.StudentID = sg.StudentID
WHERE sg.Subject IN ('Maths', 'Chem')
AND sg.Gra
Jason Chan wrote:
I am using mysql 4.0.25 with no subquery support : (
"Jason Chan" <[EMAIL PROTECTED]> ¦b¶l¥ó news:[EMAIL PROTECTED]
¤¤¼¶¼g...
I have a student Table and a SubjectGrade table
Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)
Create Table SubjectGrade(
St
Jason Chan wrote:
I have a student Table and a SubjectGrade table
Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)
Create Table SubjectGrade(
StudentID INT NOT NULL,
Subject VARCHAR(30) NOT NULL,
Grade CHAR(1)
)
let's say have following record in SubjectGrade
1Math
> Select s.StudentID, s.StudentName from Student s, SubjectGrade sg where
> s.StudentID = sg.StudentID and sg.Subject = 'Maths' and sg.Subject = 'Chem'
> and sg.Grade = 'A'
Take a close look at the WHERE clause: "sg.Subject = 'Maths' and
sg.Subject = 'Chem'" will never return a result - how could
Jason Chan wrote:
I have a student Table and a SubjectGrade table
Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)
Create Table SubjectGrade(
StudentID INT NOT NULL,
Subject VARCHAR(30) NOT NULL,
Grade CHAR(1)
)
let's say have following record in SubjectGrade
1Maths
Select s.StudentID, s.StudentName from Student s, SubjectGrade sg where
s.StudentID = sg.StudentID and sg.Subject = 'Maths' and sg.Subject = 'Chem'
and sg.Grade = 'A'
The key here is using the aliases for the tables. The linking of the tables
comes in the s.StudentID = sg.StudentID statement.
I am using mysql 4.0.25 with no subquery support : (
"Jason Chan" <[EMAIL PROTECTED]> ¦b¶l¥ó news:[EMAIL PROTECTED]
¤¤¼¶¼g...
I have a student Table and a SubjectGrade table
Create Table Student(
StudentID INT NOT NULL,
StudentName VARCHAR(30)
)
Create Table SubjectGrade(
StudentID INT NOT NULL,
m i l e s <[EMAIL PROTECTED]> wrote on 08/08/2005 05:05:46 PM:
> Shawn,
>
> Took me a bit to digest what you were sayin but if I get it the way
> you splain'd it
> then the following should work:
>
> ++
>
> SELECT
> cmc_search_members.Pro
Shawn,
Took me a bit to digest what you were sayin but if I get it the way
you splain'd it
then the following should work:
++
SELECT
cmc_search_members.PropertyName AS pNAME,
cmc_search_members.PropertyEmail AS pEMAIL,
cmc_
1 - 100 of 240 matches
Mail list logo