Do you really mean 1:1 relationships? A song might have more than one
artist, album, or genre. Could a song have no album at all, such as
something that was released in MP3 format? Also, you might have more than
one version of a song. You might need a separate table for recordings, with
each song h
I think you're approaching this from the wrong angle. You'll want to put
the data at the highest level at which it changes.
i.e. If every song on an album is always the same year, put it at the
album level, however, if it changes from song to song on a particular
album, then you want it at the
You're left joining invoices, then left joining receipts. A left join will replicate rows on the left side to match the number of
rows it found in the join, or just leave 1 row with NULL values (as you probably know). This is where your problem is. You were
correct to try to use left joins becaus
On 7 Jun 2006, at 18:56, Peter Brawley wrote:
Join users a second time for bugs.submitter, eg ...
SELECT
bugs.id, bugs.title, UNIX_TIMESTAMP(bugs.submitted), bugs.fix,
bugs.assignee,
users.username,
users2.username AS Submitter,
bugs.category,
category.name,
bugs.version,
version.nam
Geoffrey,
>SELECT
> bugs.id, bugs.assignee, users.username, bugs.submitter
>FROM fb_bugs AS bugs,
>fb_users AS users
>WHERE users.id = bugs.assignee
>My problem is that I also want the users.username for bugs.submitter.
How can I do that?
Join users a second time for bugs.submitter, eg ...
"Mark" <[EMAIL PROTECTED]> wrote on 03/27/2006 09:45:57 AM:
> Hi i am emiling you hoping you can help me urgently,
> I have a football tipping script which works fine now except i want
> to update a field from one table to another.
>
> At the moment there is a table (leaderboard) which is update
Mark,
>here is my script that doesnt work (also there is no
>session involved, makes it harder)
What doesn't work?
What stops you from using $_SESSION[]?
PB
Mark wrote:
Hi i am emiling you hoping you can help me urgently,
I have a football tipping script which works fine now except i wa
"Guillaume Boissiere" <[EMAIL PROTECTED]> wrote on 02/21/2006 02:23:29
PM:
> This must have been asked before but I could not find the answer
searching
> the list archives.
> I have a simple table:
> CREATE TABLE `license` (
> `id` int(11) NOT NULL auto_increment,
> `firstname` varchar(100)
Guillaume Boissiere wrote:
This must have been asked before but I could not find the answer searching
the list archives.
I have a simple table:
CREATE TABLE `license` (
`id` int(11) NOT NULL auto_increment,
`firstname` varchar(100) NOT NULL default '',
`lastname` varchar(100) NOT NULL default
Just an update on this one... I changed the encryption method for the
strings being stored to a method that doesn't use funky characters. I then
removed the fields with the previous funky data and those weird id issues
have gone away. :-)
Jenifer
- Original Message -
From: "Subs
od_id = ag_admin_cart.paymethod_id
WHERE cartid = ".$_SESSION['cartid'];
Jenifer
- Original Message -
From: "Roger Baklund" <[EMAIL PROTECTED]>
To:
Cc: "Subscriptions" <[EMAIL PROTECTED]>
Sent: Monday, December 12, 2005 11:25 AM
Subject: Re: Joins on tables
Subscriptions aka Jenifer wrote:
query: SELECT tblCustomer.*, tblNotes.note FROM tblCustomer LEFT
JOIN tblNotes ON tblCustomer.customerid = tblNotes.noteID WHERE
tblCustomer.customerid = 123
You are joining the customerid from the customer table to the noteID
from your notes table. You should
rruption, but
the tables check out fine.
Jenifer
- Original Message -
From: [EMAIL PROTECTED]
To: Subscriptions
Cc: mysql@lists.mysql.com
Sent: Sunday, December 11, 2005 11:15 PM
Subject: Re: Joins on tables with funky data?
"Subscriptions" <[EMAIL PROTE
"Subscriptions" <[EMAIL PROTECTED]> wrote on 12/11/2005
10:46:08 PM:
> Okay, so I haven't been able to find any corrupted tables. Has
> anyone ever run into problems with joins between tables that contain
> funky data? I have a table that contains encrypted info and some of
> the characters be
Your query was:
SELECT distinct accomodation.accom_id FROM accomodation LEFT JOIN
student_accom ON accomodation.accom_id = student_accom.accom_id where
((student_accom.start_date Between '2005-10-01' And '2005-11-30') OR
(student_accom.leave_date Between '2005-10-01' And '2005-10-30'))
The logic:
Not sure what you are asking for. If you are trying to get the
opposite of what you are getting, try adding
AND student_accom.start_date IS NULL
to the end of the query. You can actually use just about any field
instead of start_date.
For easier readability, you can rephrase your query to
On Wed, Oct 05, 2005 at 09:36:51AM +1000, Ligaya Turmelle wrote:
> >Having asked that, another question arises...do my field names in different
> >tables within the same database have to overlap (same field name) in order
> >for joins to work? I'm currently naming fields in such a manner:
No, name
bit simplistic but http://sqlcourse2.com/joins.html
Rich wrote:
Hi folks. Any chance on a tutorial from joins? I find them totally
confusing and I know there's some power in them, so I need to learn them.
Having asked that, another question arises...do my field names in different
tables with
Thanks Shawn.
My naming convention (table_field) is more for my middleware development.
Much easier to see what to grab that way. It started when was doing
embedded inlines (pre-joins) and found the reference to 'id' was confusing.
Anyway, thanks for the join tutorial links, folks. Appreciate
Rich,
>Hi folks. Any chance on a tutorial from joins?
A few good ones are listed at
http://www.artfulsoftware.com/dbresources.html.
PB
-
Rich wrote:
Hi folks. Any chance on a tutorial from joins? I find them totally
confusing and I know there's some power in them, so I need to learn
My favorite SQL tutorial: http://sqlzoo.net/
Do your field names need to be globally unique: yes and no. Some design
philosophies require that every field on every table be uniquely
identified. The only uniqueness requirement you must obey is that no two
fields on the same table can have the s
> Hi folks. Any chance on a tutorial from joins? I find them totally
> confusing and I know there's some power in them, so I need to learn them.
http://www.w3schools.com/sql/default.asp
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Se
Graham wrote:
In the query below, I want to obtain all options_totals table records(500 or so), except for those
users_options records where the userID=3.
i.e. Not retrieve options the user has already selected.
If I understand correctly, user_options is a relational table for a
many-to-many rela
That worked perfectly. I've never had much luck with joins!
Thanks.
Ciaran.
-Original Message-
From: Roger Baklund [mailto:[EMAIL PROTECTED]
Sent: 07 April 2004 12:52
To: [EMAIL PROTECTED]
Cc: O'Neill, Ciaran
Subject: Re: Joins
* O'Neill, Ciaran
[...]
> I want to g
* O'Neill, Ciaran
[...]
> I want to get everything from this table, even if there is no
> corresponding record in the personnel table.
This is a typical task for a LEFT JOIN, try something like this:
select workorders.jcn AS "WO Number",
workorders.seq
So my problem below still exists, but I think I have a better way (and SQL example) to
describe what's going on. Consider the following SQL:
SELECT gc_info.award,amends.amend,payments.paid
FROM gc_info
LEFT JOIN amends ON gc_info.gc_number=amends.gc_number
LEFT JOIN payments ON amends.gc_number =
ust as well.
Thanks again,
Jason
- Original Message -
From: Patrick Shoaf <[EMAIL PROTECTED]>
Date: Tuesday, August 19, 2003 12:07 pm
Subject: Re: JOINs
> At 03:18 PM 8/19/2003, you wrote:
> >I may be trying to do too much in a single query here, but it
> would
At 03:18 PM 8/19/2003, you wrote:
I may be trying to do too much in a single query here, but it would be
nice if I could get it working! Apparently, the version of MySQL used by
my work does not support the WITH ROLLUP feature, otherwise I think that
would work.
What I have are 3 tables: gc_inf
Hi Kim,
no offense, but are you actually using the JOIN syntax correctly ?
Could you post your query please ?
My guess is you are doing a JOIN without any set conditions. hat would give
you the result as described (which is normal). You would use the WHERE clause
or for a LEFT JOIN ...the ON c
how can it be confusing ? nobody ever experienced it before?
simply when you join 2 tables together if you get 5 records from the second
table the first table will display 5 times aswell
> Hi Daniel,
>
> I am still not sure what exactly you are trying to achieve.
>
> If you specify the WHERE cond
Hi Kim,
You can create a temporary table by using a table alias. The temporary table
will only be accessable by the current connection. Any other connection will
not be able to use it.
If you want to make a table which can be used by several connections (f.e for
a join) you could create a HEAP
G'day Nils
> just send a reply a minute ago. I got it now. You want to change the way the
> data is presented at the screen and thats a different issue. You would have
> to make this in your apllication, not with mysql itself I believe.
> You basically want the DISTINCT function within the JOINS -
Hi Daniel,
I am still not sure what exactly you are trying to achieve.
If you specify the WHERE condition correctly (or ON or USING for the LEFT
JOIN) then there is no duplicate entry.
I believe what you are asking has more to do with the way how to present the
data in the output.
Best rega
Hi Daniel and Kim,
Isn't
> product_name product_category
> product_name product_category
> product_name product_category
> product_name product_category
> product_name product_category
the way its supposed to be ?
You are using the LEFT JOIN syntax, but actually using it just as a JOIN.
You c
he is getting the same problem as me as i explained
i use joins left join and inner join
SELECT * FROM products p LEFT JOIN product_cat_join pcj ON
p.productID=pcj.productID LEFT JOIN product_category pc ON
pc.catID=pcj.catID;
products
productID
product
product_cat_join
joinID
productID
catID
Kim Kohen wrote:
Comparing
> PageAd_num status stories
> 14312-T1308 Ad Complete 1-marchingband-A33Placed
> 14312-T1308 Ad Complete 1-jobs-T33
> Placed 14312-T1308 Ad Complete 1-illegalbuild-A32
> Plac
G'day Nils
Well, I can't speak for Daniel, and I'm not really up on his tables, but I'm
not using left joins. The situation I have is not one where I need to
display items that don't exist from one table or another.
The data I get from a basic join is the _data_ I want - I just can't get it
to di
well i'd like to join tables without duplicates, currently i cannot do this
unless i select the category table in a second loop to list all the
categories for that particular row/record
> Hi Daniel and Kim,
>
> Isn't
>
>> product_name product_category
>> product_name product_category
>> product_na
G'day Nils
> no offense, but are you actually using the JOIN syntax correctly ?
No offence taken - that's why I'm asking the question - I don't know whether
I'm approaching this correctly. As I said, I'm new to MySQL ...
> Could you post your query please ?
OK.
Adtrack - holds data a
> Adtrack - holds data about an ads status (but NOT page number)
> Dummy - holds data about ad position, geometry and page number
> Stories - holds data about stories and their page number
> Pages - holds data about pages and their status
>
> I want to be able to display a page and all its assoc
G'day Jim
> What about this?
That's it, thanks!
I'll be fiddling a bit to get a few other columns but so far this seems be
be what I want. The only book I have which handles Unions is the MySQL
Cookbook (Paul Dubois) and the examples didn't include any 'where' clauses
so I didn't realise t
Hi Kim,
just send a reply a minute ago. I got it now. You want to change the way the
data is presented at the screen and thats a different issue. You would have
to make this in your apllication, not with mysql itself I believe.
You basically want the DISTINCT function within the JOINS - but on
I have also had this trouble before if there are say 5 results from the
second table, the first table of results would be duplicated 5 times on a
join, i would also like to know how to get around this.
> G'day all,
>
> I'm pretty new to MySQL and very new to v4.
>
> This is probably going to sound
# [EMAIL PROTECTED] / 2003-06-29 08:14:28 +0100:
> The table customer_basket contains the products_id which is no good
> for my routine. What I need to check is the categories_id but there
> is no reference in the customers_basket.
>
> Presumably I need to do a join on the table that holds the ca
Hi,
I think there where 2 possibilities.
1. Add to the customers_basket table the categories_id
SELECT
cat.*
FROM
customers_basket AS cb,
categories AS cat
WHERE
products_id=7
AND
cb.categories_id=cat.categories_id
2. Make a relation table
CREATE TABLE relation_product_category (
Try a straight join with the cases table first. You have to play with your
queries to get the best results.
Creating a temporary, as you mentioned, table might be a very good option in
this case, but you need to make sure that your programming takes into
account that your page might be hit more t
On Wed, 2003-01-22 at 03:18, Steve Quezadas wrote:
>
> ([Defendant] Query WITH a join - 8.79 seconds!
> EXPLAIN SELECT Defendants.CaseNumber FROM Defendants, Cases WHERE
> Cases.CaseNumber = Defendants.CaseNumber AND Filed <= "1999-01-01" AND
> (Defendant LIKE "owen%" OR Defendant LIKE "pitts%"
Your joins are probably not using indexes and the tables could be joining in
an inefficient order. Try running an explain on your query to see how it is
being done.
-Original Message-
From: Steve Quezadas [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 21, 2003 1:31 AM
To: [EMAIL PROT
Steve Quezadas wrote:
However. I notice that if I do two separate searches it goes quicker
(about 2.5 seconds combined). I can do a criteria search on defendants
and then put all the resulting case numbers in a temporary table. Then
do a join of that temporary table to the much smaller Cases t
Steve,
> SQL joins are damn slow! I have a simple database of about 37,000
> records in [Court Cases] and 2,000,000 records in [Defendants] (soon
to
> be more).
Please post the structures of your 2 tables (at least, the relevant
parts), and the join query that gets so slow. Otherwise, we can only
* Paul DuBois
> At 17:00 -0700 1/16/03, Matthew Phillips wrote:
> >I am running 3.23 and am new to mysql
> >
> >I would like to do something like this:
> >
> >delete t1
> >from t1, t2
> >where t1.col = t2.col
> >and ...
> >
> >basically deleting every row in t1 that has a matching row in t2.
> >
>
At 17:00 -0700 1/16/03, Matthew Phillips wrote:
I am running 3.23 and am new to mysql
I would like to do something like this:
delete t1
from t1, t2
where t1.col = t2.col
and ...
basically deleting every row in t1 that has a matching row in t2.
4.x has mechanisms to join 2 or more tables in upd
> On 2 Aug 2002, at 11:43, Luc Foisy wrote:
>
> > FROM EVENTATTENDANCE
> > LEFT JOIN EVENT ON EVENTATTENDANCE.ID_EVENT = EVENT.ID
> > LEFT JOIN CONTACT ON EVENTATTENDANCE.ID_CONTACT = CONTACT.ID
> > LEFT JOIN STATUS ON EVENTATTENDANCE.ID_STATUS = STATUS.ID
> > LEFT JOIN ORGANIZATION ON CONTAC
> Luc Foisy wrote:
>
> >Since the below select query blew up on me, then I must be
> doing something wrong, perhaps it is because I am using the
> wrong type of join, dunno
> >I would like to join the same table twice, with different
> criteria as below
> >
> >Anyone got a solution to this?
>
On 2 Aug 2002, at 11:43, Luc Foisy wrote:
> FROM EVENTATTENDANCE
> LEFT JOIN EVENT ON EVENTATTENDANCE.ID_EVENT = EVENT.ID
> LEFT JOIN CONTACT ON EVENTATTENDANCE.ID_CONTACT = CONTACT.ID
> LEFT JOIN STATUS ON EVENTATTENDANCE.ID_STATUS = STATUS.ID
> LEFT JOIN ORGANIZATION ON CONTACT.ID_ORGANIZAT
Hi.
On Thu 2002-07-25 at 10:41:54 +0200, [EMAIL PROTECTED] wrote:
[...]
> >>
> >>Personally I find the former much easier to read though not sure about
> >>the practical differences between them when the statements are executed.
> >
> >AFAICT, there should be no differences on execution speed.
>
Benjamin Pflugmann wrote:
>Hi.
>
>On Wed 2002-07-24 at 09:46:03 +0100, [EMAIL PROTECTED] wrote:
>
>
>
>>As I understand it an ANSI92 join is written as;
>>
>>SELECT b.columnA
>>FROM tableB AS b JOIN ON tableC AS c ON b.id = c.id
>>
>>
>
>Ah. Okay. Was not aware that this was new in ANSI92
Hi.
On Wed 2002-07-24 at 09:46:03 +0100, [EMAIL PROTECTED] wrote:
> As I understand it an ANSI92 join is written as;
>
> SELECT b.columnA
> FROM tableB AS b JOIN ON tableC AS c ON b.id = c.id
Ah. Okay. Was not aware that this was new in ANSI92.
> Whereas the 'old' style would be;
>
> SELECT
sure about
the practical differences between them when the statements are executed.
Regards,
Matt
-Original Message-
From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]]
Sent: 23 July 2002 18:23
To: Defryn, Guy
Cc: '[EMAIL PROTECTED]'
Subject: Re: Joins, ANSI 92 or the "old
Hi.
On Wed 2002-07-17 at 14:16:39 +1200, [EMAIL PROTECTED] wrote:
>
> I was wondering what advantages one has over
> the other.
>
> I think the "old way" is easier but is it good enough?
I have no clue what you are talking about and I am sure I am not the
only one. Mind giving an example?
Gre
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, 17 July 2002 1:59 p.m.
To: Defryn, Guy
Subject: Re: Joins, ANSI 92 or the "old way"
Your message cannot be posted because it appears to be either spam or
simply off topic to our filter.
* [EMAIL PROTECTED]
>
> I have a question on how MySQL JOIN has effect on query (search)
> performance:
>
> Our database consists of about 250.000 datasets (rows).
> Right now, all of the 150 columns are in one big table.
>
> However, we do have certain columns that are empty for most rows (for
>
Hmm,
That's what I thought I was doing by putting the
common fields of each article type in a single table.
I do need to rethink the search functions. Was
thinking of a fulltext index on title and
article_content fields.
basic table is
id | type | author | date | last_update | title |
keywords
A quick note, I've read Tom's email about using a hanger table and simply don't agree.
From: olinux <[EMAIL PROTECTED]>
> I have a table with a large number of news articles.
For my curiosity, how many article records is "a large number"? A few thousand, or
tens of millions?
> The question
I disagree. Joins can be expensive. Now PERHAPS Tom's suggestion is a good
one, but its hard to say. Remember, excess fields can always be left out of a
SELECT, so they don't neccessarily cause a performance problem. Some things
are true though, fixed length records are more efficient, so if yo
Hello olinux,
If I were in your shoes, I would put not only paths to images into a separate
table, but also a field called "content" and probably title, summary,
author and linked all this stuff together via ID-like fields which are
primary keys in those new tables. Then articles table can be sea
* Roger Baklund
> >I have two tables, and I want to join them based on some priority rules:
>
* Christopher Book
> If you use UNIONS (in MySQL 4.0) then you can simply union your different
> cases together.
Thanks, but I need a solution for version 3.23.39.
Roger Baklund,
Mobiliant AS
> table a has ak1 and ak2 as primary key
> table b has bk1 and bk2 as primary key
> how do I join these tables?
>
> if i write
> where a.ak1 = b.bk1 and a.ak2 = b.bk2
> data are returned multiple times.
AnneMarie,
When you say "data are returned multiple times" please clarify what you mean:
a)
> table a has ak1 and ak2 as primary key
> table b has bk1 and bk2 as primary key
> how do I join these tables?
>
> if i write
> where a.ak1 = b.bk1 and a.ak2 = b.bk2
> data are returned multiple times.
AnneMarie,
When you say "data are returned multiple times" please clarify what you mean:
a)
[EMAIL PROTECTED] schrieb am Freitag, 24. August 2001, 13:43:15:
> table a has ak1 and ak2 as primary key
> table b has bk1 and bk2 as primary key
> how do I join these tables?
> if i write
> where a.ak1 = b.bk1 and a.ak2 = b.bk2
> data are returned multiple times.
Well this should be. You sho
Yes. No problem with that.
Cal
*
* Cal Evans
* Senior Internet Dreamer
* http://www.calevans.com
*
- Original Message -
From: "Nick Davies" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, July 16, 2001 07:59
Subject: Joins.
>
> Hi,
>
> I'm just needing a bit of help with Join
select a.id, status1, detail
from history a, statusText b
where a.id = b.id;
rob anderson wrote:
> I am new to MySQL so don't laugh!
>
>
> I have a table 2 fields that relate to another table (that stores the
> description)
>
> i.e
>
> history
> id
> status1
> status2
>
>
> statusText
> id
72 matches
Mail list logo
