Re: Selecting data from 2 tables.
I think so: CREATE TEMPORARY TABLE tmpList SELECT DISTINCT magazine FROM pages; INSERT tmpList SELECT DISTINCT magazine FROM pdflog; SELECT DISTINCT magazine FROM tmpList; DROP TABLE tmpList; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ed Curtis <[EMAIL PROTECTED]> wrote on 08/09/2004 12:33:28 PM: > > > I think a quick way to write this query would be > > (http://dev.mysql.com/doc/mysql/en/UNION.html): > > > > ( SELECT magazine FROM pages ) > > UNION DISTINCT > > ( SELECT magazine FROM pdflog ) > > ORDER BY magazine; > > Thanks for all the help on this one. I just also realized that the server > I'm working with has version 3.23.x of MySQL. My only other option now is > to create a temp table with distinct data from both tables, is it not? > > Ed > >
Re: Selecting data from 2 tables.
> I think a quick way to write this query would be > (http://dev.mysql.com/doc/mysql/en/UNION.html): > > ( SELECT magazine FROM pages ) > UNION DISTINCT > ( SELECT magazine FROM pdflog ) > ORDER BY magazine; Thanks for all the help on this one. I just also realized that the server I'm working with has version 3.23.x of MySQL. My only other option now is to create a temp table with distinct data from both tables, is it not? Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting data from 2 tables.
That depends on your definition of a "join"... I don't call it a join without a join condition. It gives you tableA * tableB results - that's a carthesian product. Hardly a normal join. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com He does have a join. He has an *implied* INNER JOIN (http://dev.mysql.com/doc/mysql/en/JOIN.html): FROM pages, pdflog What he is really missing is the WHERE clause that matches something from pages with something from pdflogWithout it he is requesting a Cartesian product of his tables (every combination of each row from both tables). I prefer to define my JOINS *explicitly*. It makes it harder to accidentally define Cartesian products): SELECT DISTINCT company FROM pages INNER JOIN pdflog ON ...some condition goes here ORDER BY company Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Martijn Tonies" <[EMAIL PROTECTED]> wrote on 08/09/2004 11:38:31 AM: > Hi, > > > God, I feel real stupid this morning and know I should know this. I have > > 2 tables in the same database and I'm trying to select distinct data from > > a row with the same name in each table. > > > > SELECT DISTINCT company FROM pages, pdflog ORDER BY company ASC > > > > I'm missing something I'm sure because it doesn't work. > > Feel stupid again ;-) > > Where's your JOIN? >
Re: Selecting data from 2 tables.
I think a quick way to write this query would be (http://dev.mysql.com/doc/mysql/en/UNION.html): ( SELECT magazine FROM pages ) UNION DISTINCT ( SELECT magazine FROM pdflog ) ORDER BY magazine; Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ed Curtis <[EMAIL PROTECTED]> wrote on 08/09/2004 12:10:16 PM: > > > > What he is really missing is the WHERE clause that matches something from > > pages with something from pdflogWithout it he is requesting a > > Cartesian product of his tables (every combination of each row from both > > tables). > > > > I prefer to define my JOINS *explicitly*. It makes it harder to > > accidentally define Cartesian products): > > > > SELECT DISTINCT company > > FROM pages > > INNER JOIN pdflog > > ON ...some condition goes here > > ORDER BY company > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > OK now I really really feel stupid. Now that I've been given the correct > way this particular person wants this done. What I need to produce is a > distinct list from pages.magazine and pdflog.magazine without a condition. > Just a list of all data in these table columns without duplicates. > > Ed > >
Re: Selecting data from 2 tables.
> What he is really missing is the WHERE clause that matches something from > pages with something from pdflogWithout it he is requesting a > Cartesian product of his tables (every combination of each row from both > tables). > > I prefer to define my JOINS *explicitly*. It makes it harder to > accidentally define Cartesian products): > > SELECT DISTINCT company > FROM pages > INNER JOIN pdflog > ON ...some condition goes here > ORDER BY company > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine OK now I really really feel stupid. Now that I've been given the correct way this particular person wants this done. What I need to produce is a distinct list from pages.magazine and pdflog.magazine without a condition. Just a list of all data in these table columns without duplicates. Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting data from 2 tables.
On Mon, 9 Aug 2004 [EMAIL PROTECTED] wrote: > He does have a join. He has an *implied* INNER JOIN > (http://dev.mysql.com/doc/mysql/en/JOIN.html): > > FROM pages, pdflog > > What he is really missing is the WHERE clause that matches something from > pages with something from pdflogWithout it he is requesting a > Cartesian product of his tables (every combination of each row from both > tables). > > I prefer to define my JOINS *explicitly*. It makes it harder to > accidentally define Cartesian products): > > SELECT DISTINCT company > FROM pages > INNER JOIN pdflog > ON ...some condition goes here > ORDER BY company > > Shawn Green Like I said I knew I was missing something. I just couldn't for the life of me remember what it was. All I needed was a nudge. Thanks all, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting data from 2 tables.
He does have a join. He has an *implied* INNER JOIN (http://dev.mysql.com/doc/mysql/en/JOIN.html): FROM pages, pdflog What he is really missing is the WHERE clause that matches something from pages with something from pdflogWithout it he is requesting a Cartesian product of his tables (every combination of each row from both tables). I prefer to define my JOINS *explicitly*. It makes it harder to accidentally define Cartesian products): SELECT DISTINCT company FROM pages INNER JOIN pdflog ON ...some condition goes here ORDER BY company Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Martijn Tonies" <[EMAIL PROTECTED]> wrote on 08/09/2004 11:38:31 AM: > Hi, > > > God, I feel real stupid this morning and know I should know this. I have > > 2 tables in the same database and I'm trying to select distinct data from > > a row with the same name in each table. > > > > SELECT DISTINCT company FROM pages, pdflog ORDER BY company ASC > > > > I'm missing something I'm sure because it doesn't work. > > Feel stupid again ;-) > > Where's your JOIN? > > With regards, > > Martijn Tonies > Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL > Server. > Upscene Productions > http://www.upscene.com > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >
Re: Selecting data from 2 tables.
> > Feel stupid again ;-) > > Where's your JOIN? > > With regards, > > Martijn Tonies Thanks, that makes me feel better :) Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting data from 2 tables.
Hi, > God, I feel real stupid this morning and know I should know this. I have > 2 tables in the same database and I'm trying to select distinct data from > a row with the same name in each table. > > SELECT DISTINCT company FROM pages, pdflog ORDER BY company ASC > > I'm missing something I'm sure because it doesn't work. Feel stupid again ;-) Where's your JOIN? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
