MySQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 
Go Back   Dev Articles Community Forums > Databases > MySQL Development
Reload this Page

SQL Select Distinc() from 2 tables?

Discuss SQL Select Distinc() from 2 tables? in the MySQL Development forum on Dev Articles.
SQL Select Distinc() from 2 tables? MySQL Development forum to discuss administration, SQL syntax, and other MySQL-related topics. Find help with installing, configuring, and maintaining your MySQL databases.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old October 5th, 2005, 03:27 AM
Laban Laban is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Jun 2005
Posts: 40 Laban User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 8 h 15 m 32 sec
Reputation Power: 4
SQL Select Distinc() from 2 tables?
2 Tables, result_p & result_nw, stores 2 different kinds of results... however... both tables contains cid that represent the id for a team... Both tables can contain the same Id (cid) more than one time.

What I like to do is create a list of all cid's from both tables and only select them once...

Code:
$sql = "SELECT t1.DISTINCT(cid), t1.cname, t2.DISTINCT(cid), t2.cname 
FROM 
result_p AS t1 
result_nw AS t2 
GROUP BY cid ORDER BY cname ASC";


This one obviously doesn't work...

Any suggestions?
Reply With Quote
  #2  
Old October 5th, 2005, 08:01 AM
MadCowDzz's Avatar
MadCowDzz MadCowDzz is offline
I'm Internet Famous
Dev Articles Frequenter (2500 - 2999 posts)
  
Join Date: Jan 2003
Location: Toronto, Canada
Posts: 2,890 MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level)MadCowDzz User rank is Lance Corporal (50 - 100 Reputation Level) 
Time spent in forums: 1 Week 16 h 14 m 9 sec
Reputation Power: 8
Would this work?

Code:
SELECT DISTINCT t1.cid, t1.cname, t2.cid, t2.cname 
FROM result_p AS t1, result_nw AS t2 
GROUP BY t1.cid ORDER BY cname ASC

Is there a relation between cid's?
Reply With Quote
  #3  
Old October 5th, 2005, 12:07 PM
Madpawn Madpawn is offline
My beat is correct.
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Dec 2004
Posts: 339 Madpawn User rank is Private First Class (20 - 50 Reputation Level)Madpawn User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 2 Days 22 h 3 m 33 sec
Reputation Power: 4
A UNION may suit you:

Code:
(SELECT DISTINCT cid, cname FROM result_p) 
UNION ALL
(SELECT DISTINCT cid, cname FROM result_nw)
ORDER BY cname ASC
Comments on this post
Itsacon agrees: I knew there was a command for that. Was looking at an alternate JOIN syntax
__________________
"A pawn is the most important piece on the chessboard -- to a pawn"
Reply With Quote
  #4  
Old October 5th, 2005, 03:57 PM
Itsacon's Avatar
Itsacon Itsacon is offline
Command Line Warrior
Click here for more information. Click here for more information
 
Join Date: Aug 2004
Location: Sector ZZ9 Plural Z Alpha
Posts: 992 Itsacon User rank is Lance Corporal (50 - 100 Reputation Level)Itsacon User rank is Lance Corporal (50 - 100 Reputation Level)Itsacon User rank is Lance Corporal (50 - 100 Reputation Level)  Folding Points: 734494 Folding Title: Super Ultimate Folder - Level 2Folding Points: 734494 Folding Title: Super Ultimate Folder - Level 2Folding Points: 734494 Folding Title: Super Ultimate Folder - Level 2Folding Points: 734494 Folding Title: Super Ultimate Folder - Level 2Folding Points: 734494 Folding Title: Super Ultimate Folder - Level 2Folding Points: 734494 Folding Title: Super Ultimate Folder - Level 2Folding Points: 734494 Folding Title: Super Ultimate Folder - Level 2
Time spent in forums: 6 Days 13 h 37 m 16 sec
Reputation Power: 5
Send a message via ICQ to Itsacon
Note: to get every record only ONCE, you shouldn't do UNION ALL, but UNION DISTINCT
__________________
This is my code. Is it not nifty?

"The biggest problem encountered while trying to design a system that was completely foolproof, was, that people tended to underestimate the ingenuity of complete fools."
---Douglas Adams

Join the Itsacon fanclub!    
Zero Tolerance: Spammers banned so far: 275
Reply With Quote
  #5  
Old October 7th, 2005, 02:27 AM
Laban Laban is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Jun 2005
Posts: 40 Laban User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 8 h 15 m 32 sec
Reputation Power: 4
IRT: MadCow,
Relation between cids?
Not really, they just apear in both tables. Sure, they represent the same "team" in both tables but I am currently not using the cid's to pick data from another table.

Did I answer that question or is your question more advanced?


IRT: MadPawn
I'll give that a shot...will let you know about the outcome.
Reply With Quote
  #6  
Old October 7th, 2005, 03:23 AM
Laban Laban is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Jun 2005
Posts: 40 Laban User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 8 h 15 m 32 sec
Reputation Power: 4
Quote:
Originally Posted by Itsacon
Note: to get every record only ONCE, you shouldn't do UNION ALL, but UNION DISTINCT


Yes, this is the finished code, and it works perfect. Thanks guys.

$sql = "(SELECT DISTINCT cid , cname FROM result_p) UNION DISTINCT (SELECT DISTINCT cid, cname FROM result_nw)

Decided I wanted to count them aswell:

$sql = "(SELECT DISTINCT COUNT(cid) FROM result_p) UNION DISTINCT (SELECT DISTINCT COUNT(cid) FROM result_nw)";
Reply With Quote
Reply

Viewing: Dev Articles Community Forums > Databases > MySQL Development > SQL Select Distinc() from 2 tables?

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 3 hosted by Hostway.