the thing with JOINs are you gotta JOIN a table ON another table where
something matches something else (in most cases). I tried to clean this up a
bit but im rather new to mysql.
SELECT DISTINCT
(td.td_id),
td.venue_id as ven_id,
td.td_date as td_date,
art.NAME as art_name,
art.WEB as art_url,
a
Thank you all so much for your help, here is my solution:
(I'm sure I can do a little more optimization)
SELECT DISTINCT (td.td_id) ,td.venue_id as ven_id, td.td_date as td_date,
art.NAME as art_name,art.WEB as art_url, artd.artist_id as art_id, tv.ID,
tv.NAME as ven_name, tv.ADDR1 ven_add0, tv.A
Paul,
>SELECT ...
>FROM
> tourdates td,
> tbl_ARTST as art,
> artist_tourdate artd ,
> tbl_VENUES tv,
> tbl_VENUE_CAPACITY tvc ,
> tbl_VENUE_AGE_XREF tvax,
> tbl_VENUE_AGES tvage
>LEFT JOIN tbl_VENUE_CAPACITY ON (tv.ID=tvc.VENUE_ID)
>LEFT JOIN tbl_VENUE_AGE_XREF ON (tv.ID=tvax.VENUE_ID)
>L
I ammend my previous post.
Paul Nowosielski wrote:
Dear All,
I've been hashing out this query for awhile with no luck as of yet.
Basically the query works if I put a limit of 500 or so but when I do the full
query it takes up so many resource that the database engine is useless.
Here is the
rtist_id = art.PKEY AND td.venue_id=tv.ID)
LIMIT 500
- Original Message -
From: "Paul Nowosielski" <[EMAIL PROTECTED]>
To:
Sent: Friday, June 23, 2006 3:27 PM
Subject: Left Join Help
Dear All,
I've been hashing out this query for awhile with no luck as of yet.
Basical
Paul Nowosielski wrote:
Dear All,
I've been hashing out this query for awhile with no luck as of yet.
Basically the query works if I put a limit of 500 or so but when I do the full
query it takes up so many resource that the database engine is useless.
Here is the query:
SELECT DISTINCT (td.
Dear All,
I've been hashing out this query for awhile with no luck as of yet.
Basically the query works if I put a limit of 500 or so but when I do the full
query it takes up so many resource that the database engine is useless.
Here is the query:
SELECT DISTINCT (td.td_id) ,td.venue_id as ven
You want a LEFT (OUTER) JOIN, which will return nulls for the columns if no
match on the join expression.
-Original Message-
From: Paul Nowosielski [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 14, 2006 4:44 PM
To: mysql@lists.mysql.com
Subject: Join help
Dear All,
I'm working
Dear All,
I'm working on a database that has a lot of inconsistencies. I have a large
query that pulls artist and venue information.
My problem is this: I'm trying to create a data feed that lists artists tour
dates, and the relation venue information corresponding to that tour date.
Unfortunat
E SA <[EMAIL PROTECTED]> wrote on 02/08/2005 06:40:40 PM:
>
> All,
>
> I have done some reading and research; however, I
> seem to be at a loss...
>
> And this time, I am not sure how to ask Google...
>
> Here is the problem:
>
> Table A: id INT
>valuevarchar (10)
>
>
All,
I have done some reading and research; however, I
seem to be at a loss...
And this time, I am not sure how to ask Google...
Here is the problem:
Table A: id INT
valuevarchar (10)
Table B: id INT
valuevarchar (10)
Table C: id INT
Robb:
http://dev.mysql.com/doc/mysql/en/JOIN.html
I am assuming all the information you need is student name + city
name. SELECT * FROM StudentTable AS s, CityTable AS c WHERE s.CityID =
c.CityID
Wes
On Wed, 8 Sep 2004 19:55:29 -0500, Robb Kerr
<[EMAIL PROTECTED]> wrote:
> Trying to get my mind
Trying to get my mind around JOINs. Please help.
Scenario...
StudentTable
Fields:
StudentID
StudentName
CityID
CityTable
Fields:
CityID
CityName
I will store personal information about the student in the first table. The
second table contains information about the city. I relate th
---
From: Michael Collins [mailto:[EMAIL PROTECTED]
Sent: 11 April 2004 05:14
To: [EMAIL PROTECTED]
Subject: sql join help
I suppose this would be easier with subselects but I am using MySQL 4:
I want all orders that are of orderStatus 2 and whose orderitems
contain a product that is in a productparent
ems that have no associated order?
The above should do what you state below, though - I think!
Cheers,
Matt
-Original Message-
From: Michael Collins [mailto:[EMAIL PROTECTED]
Sent: 11 April 2004 05:14
To: [EMAIL PROTECTED]
Subject: sql join help
I suppose this would be easier w
I suppose this would be easier with subselects but I am using MySQL 4:
I want all orders that are of orderStatus 2 and whose orderitems
contain a product that is in a productparent category greater than 2.
An orderitem can only have one product, and that product has a single
certain product par
Dan Hansen wrote:
> is essentially giving me what I need:
>
> CREATE TEMPORARY TABLE temptable
> SELECT state.name AS state , group.name AS group,
> group.zip AS zip, city.name AS city
> FROM city, group, zip
> LEFT JOIN state ON city.state_id = state.id
> WHERE group.zip = zip.zip
> AND zip.city_
For everyone who helped, THANK YOU!!
For anyone who might be interested, here's what finally did the trick and
is essentially giving me what I need:
CREATE TEMPORARY TABLE temptable
SELECT state.name AS state , group.name AS group,
group.zip AS zip, city.name AS city
FROM city, group, zip
LEFT J
* D. R. Hansen
> At 03:51 AM 10/15/03, Diana Soares wrote:
> >You're confusing the left/right "sides" of LEFT JOIN...
> >Using LEFT JOIN, it is the right table that is dependent on the left
> >table. All results from left table are selected.
> >So you may try:
[...]
> I believe I tried that -- but
I believe I tried that -- but when I did (and I just repeated it with the
same result) mysql effectively hangs (i.e. the query takes interminably
long -- I let it run for 20 minutes before killing it).
So should I be looking at an indexing issue? Right now the only things
indexed in the tables
You're confusing the left/right "sides" of LEFT JOIN...
Using LEFT JOIN, it is the right table that is dependent on the left
table. All results from left table are selected.
So you may try:
SELECT state.name AS state , group.name AS group,
group.zip AS zip, city.name as city
FROM state
LEF
Uberdumb question - but I'm still enough of a newbie that this is giving
me fits...
I have four tables, with relevant columns as follows:
++ ++
group zip
-- --
name varchar city_id int
zip mediumint zip mediumint
Hi Fongo,
Steve gave a perfect explanation ( and made me realize my and Egor's small
mistake ;-)
2003年 8月 15日 金曜日 17:08、Nils Valentin さんは書きました:
> Hi Fongo,
>
> http://www.mysql.com/doc/en/JOIN.html
>
>
> Should work like this (untested):
>
> SELECT Customers.Name, Customers.City, Orders.Produc
10:14
An: [EMAIL PROTECTED]
Betreff: Re: Query with inner join (Help!)
"B. Fongo" <[EMAIL PROTECTED]> wrote:
>
> I ' m trying to extra some information from 2 tables using inner join,
> but receive an error warning. Am newbie so I' m not able to feat
"B. Fongo" <[EMAIL PROTECTED]> wrote:
>
> I ' m trying to extra some information from 2 tables using inner join,
> but receive an error warning. Am newbie so I' m not able to feature out
> why my queries don't work.
>
> Scenario:
>
> I have 2 tables: Customers and orders. The have following str
rder.Price FROM
Customers inner join Orders USING (cust_id) WHERE customers.cust_id = 2
HTH.
Regards
Steve.
- Original Message -
From: "B. Fongo" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, August 15, 2003 8:44 AM
Subject: Query with inner join (Help
Hi Fongo,
http://www.mysql.com/doc/en/JOIN.html
Should work like this (untested):
SELECT Customers.Name, Customers.City, Orders.Product, Order.Price from
Customers, Orders WHERE Customers.cust_id = Orders.cust_id AND
cust_id = 2 inner join
Best regards
Nils Valentin
Tokyo/Japan
2003年 8
Hello!
I ' m trying to extra some information from 2 tables using inner join,
but receive an error warning. Am newbie so I' m not able to feature out
why my queries don't work.
Scenario:
I have 2 tables: Customers and orders. The have following structures:
Customers
Rick Pasotto wrote:
One of these days I will maybe understand...
A "left join" (t1 LEFT JOIN t2 ON ) is defined as follows.
For each row in t1, find all matching rows in t2 and return the
combination of t1 and t2 found. If there are no t2s for a t1, leave the
t2 values NULL in the result.
Try
One of these days I will maybe understand...
Using MYSQL 4.0.13, debian linux
create table members (
id unsigned int autoincrement,
name
)
create table activity (
id unsigned int autoincrement,
description
)
create table history (
id unsigned in autoincre
Multiple table update is only supported from 4.04 or above. If you are using
3.x, I think you should put the result into another table, delete MSI_List
and rename the table to MSI_List.
I would also very interested to know whether there exist a more elegant
solution for 3.x.
Best regards,
> s
sql
I have a zip code db named "MSI_Zipcodes that contains
city
state
zipcode (index)
I also have a db named "MSI_List" that contains
ID (index)
email
city(empty)
state(empty)
zip
My problem is:
How can i bring the proper info from "MSI_Zipcodes"(city and state)
and enter it (city and state)
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Frank --
...and then Frank Peavy said...
%
% >Even so, that still doesn't answer the question of how to have data of
% >different magnitude in the same table. If I have one class with one
% >person and another with two people, how would I have a sin
Even so, that still doesn't answer the question of how to have data of
different magnitude in the same table. If I have one class with one
person and another with two people, how would I have a single record for
each which lists the client(s)?
Easy,
Your scheduling query results, as I said:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Frank --
...and then Frank Peavy said...
%
% David,
% Just some thoughts..
% See my comments below...
Thanks!
%
% >A scheduling, or a booking, eventually has to have a class type (private
% >or one of many groups -- so I suppose I could simply ma
David,
Just some thoughts..
See my comments below...
A scheduling, or a booking, eventually has to have a class type (private
or one of many groups -- so I suppose I could simply make a group class
type 'private' and that type has only one slot), an instructor, a place,
a time slot, and the clien
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Frank, et al --
...and then Frank Peavy said...
%
% David,
% I am unsure if I followed your example completely, but maybe this might
% help. Not knowing your complete database structure, I am unsure if my
% comments will be entirely valid but here
David,
I am unsure if I followed your example completely, but maybe this might
help. Not knowing your complete database structure, I am unsure if my
comments will be entirely valid but here goes.
I think you could achieve your goal if you think of your groups as
containing one or many clients.
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi, all --
I need, I think, some pointers to basic JOIN tutorials. I don't really
know how to approach this query. I should say early on that I don't
expect the list to write my code for me, though any help anyone can send
is VERY much appreciated;
>Is there a good tutorial somewhere on the join command. No matter what
I do
>it just doesn't work. Obviously I'm doing something wrong, but the
MySQL
>manual just doesn't help at all.
Try this link.
http://www.devshed.com/Server_Side/MySQL/Join/page1.html
One of the best books for understanding
Hi,
Is there a good tutorial somewhere on the join command. No matter what I do
it just doesn't work. Obviously I'm doing something wrong, but the MySQL
manual just doesn't help at all.
TIA
-
Before posting, please check:
h
I figured it out ...
At 3:22 PM +0100 5/20/02, ds wrote:
>On Mon, 2002-05-20 at 13:34, Ray Zimmerman wrote:
>...
> > For example, given the following data ...
>>
>> CREATE TABLE Object (
>> id int(11) NOT NULL auto_increment,
>> PRIMARY KEY (id)
>> );
>>
>> INSERT INTO O
On Mon, 2002-05-20 at 13:34, Ray Zimmerman wrote:
...
> For example, given the following data ...
>
>CREATE TABLE Object (
> id int(11) NOT NULL auto_increment,
> PRIMARY KEY (id)
>);
>
>INSERT INTO Object (id) VALUES (1);
>INSERT INTO Object (id) VALUES (2);
>INSE
Dear list,
I've done basic SQL for a number of years, but I recently came up
against a query I can't quite seem to figure out the join syntax for.
Suppose I have rows in an Object table which are linked in a
hierarchy by the entries in a Link table and I want to find all
Objects which have gi
: Probably OT - SQL join help needed
Hello Robert,
> Hello experts, I've got a small problem with an sql query
here that's got
> me completely stuck.
>
> In my MySQL database I've got two tables here
Hello experts, I've got a small problem with an sql query here that's got
me completely stuck.
In my MySQL database I've got two tables here that have identical design,
e.g.
table 'detail' - columns sales-order, quantity, part-number, price,
date-sent
and
table 'archived' - columns sales-order,
Hello Robert,
> Hello experts, I've got a small problem with an sql query here that's got
> me completely stuck.
>
> In my MySQL database I've got two tables here that have identical design,
> e.g.
> table 'detail' - columns sales-order, quantity, part-number, price,
> date-sent
> and
> table 'ar
Hello experts, I've got a small problem with an sql query here that's got
me completely stuck.
In my MySQL database I've got two tables here that have identical design,
e.g.
table 'detail' - columns sales-order, quantity, part-number, price,
date-sent
and
table 'archived' - columns sales-order,
Hi Butch Bean,
What if you use a temporary table .
Issue the following queries.
Create temporary table tmptable as select sent_id,count(*) cnt
from tbl_sent group by sent_id;
Select sum(if(sent_id < idvar,1,0)) as lessthan,
sum(if(sent_id >idvar,1,0)) as greaterthan
from tmptable;
Let us kn
I have a table with 2.9 mil records which represents 197k sentences stored
vertically. I do this because I need to know information about each word
and its relationship to other tables.
I want to know how many words have a particular word-group ID before and a
particular word-group ID after the
>Hi All,
>
>I have a JOIN statement:
>
>SELECT d.*, b.invoice_id FROM domain_info d LEFT JOIN billing_info b ON
>d.domain_id=b.domain_id
>WHERE billing_cycle = '12' OR billing_cycle = 'Z' OR billing_cycle = 'C'
>GROUP BY domain_name
>
>In addition to the fields this statement returns, I would also
Hi All,
I have a JOIN statement:
SELECT d.*, b.invoice_id FROM domain_info d LEFT JOIN billing_info b ON
d.domain_id=b.domain_id
WHERE billing_cycle = '12' OR billing_cycle = 'Z' OR billing_cycle = 'C'
GROUP BY domain_name
In addition to the fields this statement returns, I would also like it
t
52 matches
Mail list logo
