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.quantit
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
- 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 quer
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 varc
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
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 j
r:
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 jo
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
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
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 OU
Airport table
SlNoName Code AuthLocation status
1 ChennaiCHN Yes India 1
2. Hydarabed HYD Yes India 0
3 walkerWAK Yes uk1
common table
SlNoName Code
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 summar
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
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 i
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
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
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, M
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: qu
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: questi
se 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 the
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_acn
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, especia
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`
Wh
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 wil
>> 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,
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.
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
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
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 so
emory 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 <
hanged 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
: "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
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 (pr
Hi All,
Given this table:
+-+-+++-
|sip_status | sip_method | sip_callid | username |
fromtag| totag
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_na
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 Pla
gt; 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
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 App
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' );
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
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 J
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" <
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 a
"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 a
"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 a
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.
--
"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
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
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, S
[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)
ipvarch
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 te
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" <[EMA
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
ticular 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 boo
uces 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:
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 thornbur
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
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
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
- 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 struggl
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
MAIL 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
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:
SEL
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 1
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
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
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, t
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=MyISA
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
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
>
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 G
'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 fo
- 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 cont
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:
h
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
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
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
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_t
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 oth
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
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 al
Fax to:
07/10/2004 08:18 Subject: Need help with my LEFT JOIN
query...
: "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 =
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 |
++
| Prod
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, do
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
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&
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 |
> +-+--+-
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
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 |
++-+
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 registra
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!!
ECTED]; [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 Lis
-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 numbe
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
: 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
[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
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
[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 d
>
> 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)
1 - 100 of 159 matches
Mail list logo
