Re: outer join question
Thanks much! ViSolve DB Team-2 wrote: > > Hello, > > Try this... > > select a.rhrqid,a.rhrqsid,a.rhrssid,b.sid,b.rlsid > from rqhistory a left join relay b > on (a.rhrqid = b.rqid and (a.rhrqsid = b.rlsid or a.rhrqsid = b.sid or > a.rhrssid = b.rlsid or a.rhrssid = b.sid)) > where a.rhrqsid = 101 or a.rhrssid = 101 > > Thanks, > ViSolve DB Team > > > > - Original Message - > From: "KMiller" <[EMAIL PROTECTED]> > To: > Sent: Tuesday, February 06, 2007 8:37 AM > Subject: outer join question > > >> >> This query isn't what I want... >> >> select a.rhrqid,a.rhrqsid,a.rhrssid,b.sid,b.rlsid >> from rqhistory a left join relay b >> on a.rhrqsid = 101 or a.rhrssid = 101 >> and (a.rhrqid = b.rqid and (a.rhrqsid = b.rlsid or a.rhrqsid = b.sid or >> a.rhrssid = b.rlsid or a.rhrssid = b.sid)) >> >> because it returns all rows from 'a' regardless of the criteria 101 >> >> Any advice on how would I get only rows from 'a' that match 101 and any >> in >> 'b' that match if they exist? >> >> -km >> -- >> View this message in context: >> http://www.nabble.com/outer-join-question-tf3178361.html#a8819711 >> Sent from the MySQL - General mailing list archive at Nabble.com. >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/[EMAIL PROTECTED] >> >> >> >> -- >> No virus found in this incoming message. >> Checked by AVG Free Edition. >> Version: 7.1.411 / Virus Database: 268.17.25/669 - Release Date: 2/4/2007 >> >> > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- View this message in context: http://www.nabble.com/outer-join-question-tf3178361.html#a8821432 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: outer join question
Hello, Try this... select a.rhrqid,a.rhrqsid,a.rhrssid,b.sid,b.rlsid from rqhistory a left join relay b on (a.rhrqid = b.rqid and (a.rhrqsid = b.rlsid or a.rhrqsid = b.sid or a.rhrssid = b.rlsid or a.rhrssid = b.sid)) where a.rhrqsid = 101 or a.rhrssid = 101 Thanks, ViSolve DB Team - Original Message - From: "KMiller" <[EMAIL PROTECTED]> To: Sent: Tuesday, February 06, 2007 8:37 AM Subject: outer join question This query isn't what I want... select a.rhrqid,a.rhrqsid,a.rhrssid,b.sid,b.rlsid from rqhistory a left join relay b on a.rhrqsid = 101 or a.rhrssid = 101 and (a.rhrqid = b.rqid and (a.rhrqsid = b.rlsid or a.rhrqsid = b.sid or a.rhrssid = b.rlsid or a.rhrssid = b.sid)) because it returns all rows from 'a' regardless of the criteria 101 Any advice on how would I get only rows from 'a' that match 101 and any in 'b' that match if they exist? -km -- View this message in context: http://www.nabble.com/outer-join-question-tf3178361.html#a8819711 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.411 / Virus Database: 268.17.25/669 - Release Date: 2/4/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
outer join question
This query isn't what I want... select a.rhrqid,a.rhrqsid,a.rhrssid,b.sid,b.rlsid from rqhistory a left join relay b on a.rhrqsid = 101 or a.rhrssid = 101 and (a.rhrqid = b.rqid and (a.rhrqsid = b.rlsid or a.rhrqsid = b.sid or a.rhrssid = b.rlsid or a.rhrssid = b.sid)) because it returns all rows from 'a' regardless of the criteria 101 Any advice on how would I get only rows from 'a' that match 101 and any in 'b' that match if they exist? -km -- View this message in context: http://www.nabble.com/outer-join-question-tf3178361.html#a8819711 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
First, you database design. You don't need to separate actresses from actors... Why do that? They are the same entity, a person, with only one different attribute: the genre. So, you should join them in one single table: ... Actually, it is possible to be female and to be an Actor. For example, the Screen Actors' Guild officially considers the term "Actor" to be gender-neutral and applies this term to all people. ... Craig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: Re: Outer join question]
Sorry, i meant "gender", not "genre".
-Forwarded Message-
First, you database design. You don't need to separate actresses from
actors... Why do that? They are the same entity, a person, with only one
different attribute: the genre. So, you should join them in one single
table:
Actors
==
act_id
name
genre ENUM('m','f')
Then, the table DVD. If we mantain things as they are, we would need one
entry in table DVD for each actor/actress in the movie, we would be
repeating the title N times... Whenever you change it, you would need to
change N records... And is redundant information.
So, let's take DVD as an entity by itself. DVD would be:
DVD
===
dvd_id
title
description
year
other_fields_related...
and then, you relate the 2 tables with this one:
DVD_Actors
==
dvd_id
act_id
leader ENUM('yes','no')
(the table name may not be the happiest :-p)
The field leading tells you if that actor is the leading one or not...
> > I want to select the title of each movie, along with the corresponding
> > leading actor and/or actress name, but a straightforward join will only
> > return those movie titles that have NOT NULL values in BOTH the acto_id
> > and actr_id fields in the DVD table.
With this design, you could use:
SELECT M.title, A.name, A.genre, DA.leader
FROM DVD AS M
LEFT JOIN DVD_Actors AS DA ON (M.dvd_id=DA.dvd_id)
LEFT JOIN Actors AS A ON (DA.act_id=A.act_id)
Please, read the manual about LEFT JOIN.
> > My grey-haired memory tells me that an outer join for both the actor table
> > and the actress table is the answer, in that the query will return all
> > titles *even if* one or both fields are NULL. (At least that was the case
> > when I was using Oracle!)
And you were right.
> > So, can somebody please correct the following query (and explain the
> > syntax) so that it will work please? (I haven't tried putting an outer
> > join in it because I don't understand the syntax.)
In this example, you are using inner join... please, read the manual
about JOINs.
> > Select
> > actr.name,
> > acto.name,
> > dvd.title
> > from
> > actresses actr,
> > actors acto,
> > dvd
> > where
> > actr.actr_id = dvd.actr_id
> > and
> > acto.acto_id = dvd.acto_id
> > order by dvd.title;
> >
> > (I used to put (+) at the end of the outer join line, but don't think this
> > will work in MYSQL - at least I don't see it in the manual.)
> >
> > Thanks in advance for your kind help and sorry for the wordy question!
--
Diana Soares
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
First, you database design. You don't need to separate actresses from
actors... Why do that? They are the same entity, a person, with only one
different attribute: the genre. So, you should join them in one single
table:
Actors
==
act_id
name
genre ENUM('m','f')
Then, the table DVD. If we mantain things as they are, we would need one
entry in table DVD for each actor/actress in the movie, we would be
repeating the title N times... Whenever you change it, you would need to
change N records... And is redundant information.
So, let's take DVD as an entity by itself. DVD would be:
DVD
===
dvd_id
title
description
year
other_fields_related...
and then, you relate the 2 tables with this one:
DVD_Actors
==
dvd_id
act_id
leader ENUM('yes','no')
(the table name may not be the happiest :-p)
The field leading tells you if that actor is the leading one or not...
> > I want to select the title of each movie, along with the corresponding
> > leading actor and/or actress name, but a straightforward join will only
> > return those movie titles that have NOT NULL values in BOTH the acto_id
> > and actr_id fields in the DVD table.
With this design, you could use:
SELECT M.title, A.name, A.genre, DA.leader
FROM DVD AS M
LEFT JOIN DVD_Actors AS DA ON (M.dvd_id=DA.dvd_id)
LEFT JOIN Actors AS A ON (DA.act_id=A.act_id)
Please, read the manual about LEFT JOIN.
> > My grey-haired memory tells me that an outer join for both the actor table
> > and the actress table is the answer, in that the query will return all
> > titles *even if* one or both fields are NULL. (At least that was the case
> > when I was using Oracle!)
And you were right.
> > So, can somebody please correct the following query (and explain the
> > syntax) so that it will work please? (I haven't tried putting an outer
> > join in it because I don't understand the syntax.)
In this example, you are using inner join... please, read the manual
about JOINs.
> > Select
> > actr.name,
> > acto.name,
> > dvd.title
> > from
> > actresses actr,
> > actors acto,
> > dvd
> > where
> > actr.actr_id = dvd.actr_id
> > and
> > acto.acto_id = dvd.acto_id
> > order by dvd.title;
> >
> > (I used to put (+) at the end of the outer join line, but don't think this
> > will work in MYSQL - at least I don't see it in the manual.)
> >
> > Thanks in advance for your kind help and sorry for the wordy question!
--
Diana Soares
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
> [EMAIL PROTECTED] wrote: >>> DVD_Actor: >>> dvd_ID REFERENCES DVD >>> actor_ID REFERNCES Actor >> >> Is this how you setup a join table ? > > Yes. > > >> what exactly is the references keyword ? > > It indicates a foreign key. Full syntax is something like: > dvd_ID CONSTRAINT dvc_fk FOREIGN KEY REFERENCES DVD (dvd_ID) > > Read the manual *very* carefully before using foreign keys in > MySQL when you have an Oracle background. > > Jochem Ok i'm setting up the foreign key relations in the main table is that bad ? I use a join table for a one to many situation. The cool thing about setting it up in the main table is, say you delete an actor you can setup a cascade delete to delete all the dvd titles aswell ;) I'm using sqlyog which has a nice relationship setup feature. > > -- > I don't get it > immigrants don't work > and steal our jobs > - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
[EMAIL PROTECTED] wrote: DVD_Actor: dvd_ID REFERENCES DVD actor_ID REFERNCES Actor Is this how you setup a join table ? Yes. what exactly is the references keyword ? It indicates a foreign key. Full syntax is something like: dvd_ID CONSTRAINT dvc_fk FOREIGN KEY REFERENCES DVD (dvd_ID) Read the manual *very* carefully before using foreign keys in MySQL when you have an Oracle background. Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
> > DVD_Actor: > dvd_ID REFERENCES DVD > actor_ID REFERNCES Actor > Is this how you setup a join table ? what exactly is the references keyword ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
Bjorn Barton-Pye wrote: I am using a test database to teach myself MYSQL and am using my DVD collection as the subject. I have 3 tables in this example: Actresses === actr_id name Actors == acto_id name DVD == Title acto_id actr_id The acto_id and actr_id in the DVD table indicates the id for the leading actor or actress of the movie in question. Obviously, in the case of some movies, it may be an all-male or all-female cast, so the id fields in the DVD table are allowed to be NULL. If you want to learn about outer joins this is a fine model. If you want to index your DVD collection, you should consider the following data model: DVD: dvd_ID PRIMARY KEY title NOT NULL Actor: actor_ID PRIMARY KEY name NOT NULL gender NOT NULL DVD_Actor: dvd_ID REFERENCES DVD actor_ID REFERNCES Actor Add more tables if you want to plan for 1 DVD having more as 1 movie or 1 movie having more as 1 DVD ;-) Jochem -- I don't get it immigrants don't work and steal our jobs - Loesje -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
> so soemthing like > > select * from dvd left join actresses actr on actr.actr_id=dvd.actr_id > left join actors acto on acto.acto_id=dvd.acto_id or by dvd.title > > totally forgot, to get a really good query especially when you use Innodb it doesnt like null values on foreign keys, i'd setup a row in the actors and actresses table like No Actor or No actress and then use that key for the null values and use INNER JOIN, check EXPLAIN aswell , it'll show what indexes are being used. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Outer join question
> So, can somebody please correct the following query (and explain the > syntax) so that it will work please? (I haven't tried putting an outer > join in it because I don't understand the syntax.) > > Select > actr.name, > acto.name, > dvd.title > from > actresses actr, > actors acto, > dvd > where > actr.actr_id = dvd.actr_id > and > acto.acto_id = dvd.acto_id > order by dvd.title; > > (I used to put (+) at the end of the outer join line, but don't think > this will work in MYSQL - at least I don't see it in the manual.) > > Thanks in advance for your kind help and sorry for the wordy question! i have not really used outer join, i'm still trying to fine tune my sql aswell but to get null values i use left join, to return not null values i use inner join, i select from the first table first though, is it better to select it at the end ? so soemthing like select * from dvd left join actresses actr on actr.actr_id=dvd.actr_id left join actors acto on acto.acto_id=dvd.acto_id or by dvd.title -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Outer join question
> Hi, > > I'm just getting into MYSQL after nearly 12 years away from relational > databases and I'm trying to blow the cobwebs away. So please bear with me > if this is a simple question! > > I am using a test database to teach myself MYSQL and am using my DVD > collection as the subject. I have 3 tables in this example: > > Actresses > === > actr_id > name > > Actors > == > acto_id > name > > DVD > == > Title > acto_id > actr_id > > The acto_id and actr_id in the DVD table indicates the id for the leading > actor or actress of the movie in question. Obviously, in the case of some > movies, it may be an all-male or all-female cast, so the id fields in the > DVD table are allowed to be NULL. > > I want to select the title of each movie, along with the corresponding > leading actor and/or actress name, but a straightforward join will only > return those movie titles that have NOT NULL values in BOTH the acto_id > and actr_id fields in the DVD table. > > My grey-haired memory tells me that an outer join for both the actor table > and the actress table is the answer, in that the query will return all > titles *even if* one or both fields are NULL. (At least that was the case > when I was using Oracle!) > > So, can somebody please correct the following query (and explain the > syntax) so that it will work please? (I haven't tried putting an outer > join in it because I don't understand the syntax.) > > Select > actr.name, > acto.name, > dvd.title > from > actresses actr, > actors acto, > dvd > where > actr.actr_id = dvd.actr_id > and > acto.acto_id = dvd.acto_id > order by dvd.title; > > (I used to put (+) at the end of the outer join line, but don't think this > will work in MYSQL - at least I don't see it in the manual.) > > Thanks in advance for your kind help and sorry for the wordy question! > > > Bjorn Barton-Pye > > Email: [EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Outer join question
Hi, I'm just getting into MYSQL after nearly 12 years away from relational databases and I'm trying to blow the cobwebs away. So please bear with me if this is a simple question! I am using a test database to teach myself MYSQL and am using my DVD collection as the subject. I have 3 tables in this example: Actresses === actr_id name Actors == acto_id name DVD == Title acto_id actr_id The acto_id and actr_id in the DVD table indicates the id for the leading actor or actress of the movie in question. Obviously, in the case of some movies, it may be an all-male or all-female cast, so the id fields in the DVD table are allowed to be NULL. I want to select the title of each movie, along with the corresponding leading actor and/or actress name, but a straightforward join will only return those movie titles that have NOT NULL values in BOTH the acto_id and actr_id fields in the DVD table. My grey-haired memory tells me that an outer join for both the actor table and the actress table is the answer, in that the query will return all titles *even if* one or both fields are NULL. (At least that was the case when I was using Oracle!) So, can somebody please correct the following query (and explain the syntax) so that it will work please? (I haven't tried putting an outer join in it because I don't understand the syntax.) Select actr.name, acto.name, dvd.title from actresses actr, actors acto, dvd where actr.actr_id = dvd.actr_id and acto.acto_id = dvd.acto_id order by dvd.title; (I used to put (+) at the end of the outer join line, but don't think this will work in MYSQL - at least I don't see it in the manual.) Thanks in advance for your kind help and sorry for the wordy question! Bjorn Barton-Pye Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A LEFT OUTER JOIN question.
You're welcome, Ian. Being new to this list, I'm just trying to gather enough credit to get people to look at my recent post ;-) Peter Normann -Original Message- From: mySQL list [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 18:29 To: Peter Normann; [EMAIL PROTECTED] Subject: RE: A LEFT OUTER JOIN question. Hi Peter, Thanks a lot - that does the trick! Incredibly quick response :) Ian > -Original Message- > From: Peter Normann [mailto:[EMAIL PROTECTED]] > Sent: 16 June 2002 17:06 > To: 'mySQL list'; [EMAIL PROTECTED] > Subject: RE: A LEFT OUTER JOIN question. > > > Try > > SELECT Items.ItemID, Stuff.info FROM Items LEFT JOIN Stuff ON > (Items.ItemID = Stuff.ItemID && (type=0 || type IS NULL)); > > > Peter Normann > > -Original Message- > From: mySQL list [mailto:[EMAIL PROTECTED]] > Sent: 16. juni 2002 17:59 > To: [EMAIL PROTECTED] > Subject: A LEFT OUTER JOIN question. > > > I have two tables, 'items' and 'stuff'. > > Items has a primary key of itemid. and looksmlike this > > ItemID other fields... > > 1 ... > 2 > 3 > 4 > 5 > 6 > > Stuff contains something like this: > > ItemID type info > 1 0blah blah > 2 0something > 2 1... > 3 1... > > I want a query which returns all the items, and if it exists, the info > field from related 'stuff' of type 0. > > So far I've got: > > SELECT Items.ItemID, Stuff.info FROM Items LEFT OUT JOIN Stuff ON > Items.ItemID = Stuff.ItemID WHERE (type = 0 OR type = NULL); > > This works if there are no Stuff records with a type other than 0, eg, > the last one in the example. The Query returns items 1,2,4,5, & 6, no > 3. > > How can I make a query which returns all items and type 0's info (if > it exists)? > > ian > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: A LEFT OUTER JOIN question.
Hi Peter, Thanks a lot - that does the trick! Incredibly quick response :) Ian > -Original Message- > From: Peter Normann [mailto:[EMAIL PROTECTED]] > Sent: 16 June 2002 17:06 > To: 'mySQL list'; [EMAIL PROTECTED] > Subject: RE: A LEFT OUTER JOIN question. > > > Try > > SELECT Items.ItemID, Stuff.info FROM Items LEFT JOIN Stuff ON > (Items.ItemID = Stuff.ItemID && (type=0 || type IS NULL)); > > > Peter Normann > > -Original Message- > From: mySQL list [mailto:[EMAIL PROTECTED]] > Sent: 16. juni 2002 17:59 > To: [EMAIL PROTECTED] > Subject: A LEFT OUTER JOIN question. > > > I have two tables, 'items' and 'stuff'. > > Items has a primary key of itemid. and looksmlike this > > ItemID other fields... > > 1 ... > 2 > 3 > 4 > 5 > 6 > > Stuff contains something like this: > > ItemID type info > 1 0blah blah > 2 0something > 2 1... > 3 1... > > I want a query which returns all the items, and if it exists, the info > field from related 'stuff' of type 0. > > So far I've got: > > SELECT Items.ItemID, Stuff.info FROM Items LEFT OUT JOIN Stuff ON > Items.ItemID = Stuff.ItemID WHERE (type = 0 OR type = NULL); > > This works if there are no Stuff records with a type other than 0, eg, > the last one in the example. The Query returns items 1,2,4,5, & 6, no 3. > > How can I make a query which returns all items and type 0's info (if it > exists)? > > ian > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: A LEFT OUTER JOIN question.
Try SELECT Items.ItemID, Stuff.info FROM Items LEFT JOIN Stuff ON (Items.ItemID = Stuff.ItemID && (type=0 || type IS NULL)); Peter Normann -Original Message- From: mySQL list [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 17:59 To: [EMAIL PROTECTED] Subject: A LEFT OUTER JOIN question. I have two tables, 'items' and 'stuff'. Items has a primary key of itemid. and looksmlike this ItemID other fields... 1 ... 2 3 4 5 6 Stuff contains something like this: ItemID type info 1 0blah blah 2 0something 2 1... 3 1... I want a query which returns all the items, and if it exists, the info field from related 'stuff' of type 0. So far I've got: SELECT Items.ItemID, Stuff.info FROM Items LEFT OUT JOIN Stuff ON Items.ItemID = Stuff.ItemID WHERE (type = 0 OR type = NULL); This works if there are no Stuff records with a type other than 0, eg, the last one in the example. The Query returns items 1,2,4,5, & 6, no 3. How can I make a query which returns all items and type 0's info (if it exists)? ian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
A LEFT OUTER JOIN question.
I have two tables, 'items' and 'stuff'. Items has a primary key of itemid. and looksmlike this ItemID other fields... 1 ... 2 3 4 5 6 Stuff contains something like this: ItemID type info 1 0blah blah 2 0something 2 1... 3 1... I want a query which returns all the items, and if it exists, the info field from related 'stuff' of type 0. So far I've got: SELECT Items.ItemID, Stuff.info FROM Items LEFT OUT JOIN Stuff ON Items.ItemID = Stuff.ItemID WHERE (type = 0 OR type = NULL); This works if there are no Stuff records with a type other than 0, eg, the last one in the example. The Query returns items 1,2,4,5, & 6, no 3. How can I make a query which returns all items and type 0's info (if it exists)? ian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: outer join question
"Harlan Feinstein" <[EMAIL PROTECTED]> wrote: > I've joined them an SQL statement that looks like this: > > select val, status > from A > left outer join B on val=id; > > What I'd LIKE is a 90-row result set, with the "status" field from table B > when there was data available. What I'm getting is essentially an INNER > join, only returning me 2 rows. The following should work (and is probably the same as the syntax you used). If the datatype of id is numeric and that of val is character then it might not work. I believe the joined fields will need to be of the same type. Are they? SELECT val, status FROM A LEFT JOIN B ON val=id -- Steve Werby President, Befriend Internet Services LLC http://www.befriend.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
outer join question
I'm having difficulty doing a LEFT OUTER JOIN, and was hoping someone could shed a little light on what I'm doing: Table A has one column, "val", that's an integer. There are 90 records, with values of "val" from 1-90. Looks like this: val --- 1 2 . . . 90 Table B is more sparse, with 2 columns: id status -- -- 2Good 4Bad I've joined them an SQL statement that looks like this: select val, status from A left outer join B on val=id; What I'd LIKE is a 90-row result set, with the "status" field from table B when there was data available. What I'm getting is essentially an INNER join, only returning me 2 rows. What am I doing wrong? --Harlan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
