Solved

mysql join

Posted on 2009-06-30
2
290 Views
Last Modified: 2012-05-07
hi
i want to join two tables together currently i am selecting from the code table

SELECT userip, datef, quiz, username, sum(category) as category, count(qid) as qid FROM codes group by quiz,username ORDER BY category
but i also wants to select fields from the enterused table where code and enterused table quiz value is the same
CREATE TABLE `enterused` (
  `id` int(10) NOT NULL auto_increment,
  `randomcode` varchar(100) NOT NULL,
  `category` varchar(50) NOT NULL,
  `quiz` varchar(100) NOT NULL,
  `datef` varchar(30) default NULL,
  `type` int(10) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;
0
Comment
Question by:mattibutt
2 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 24747316
If there is always a match or you only want rows that have both tables for data, use an INNER JOIN.

e.g.

SELECT c.username, e.type
FROM codes c
INNER JOIN enterused e ON e.quiz = c.quiz

If there may not be a value in enterused, then use LEFT OUTER JOIN.

e.g.

SELECT c.username, e.type
FROM codes c
LEFT OUTER JOIN enterused e ON e.quiz = c.quiz

When there is not a match, e.type in this example will be NULL.

If other way around, you can use RIGHT OUTER JOIN or just switch the order of the tables with LEFT OUTER JOIN.
0
 
LVL 11

Author Closing Comment

by:mattibutt
ID: 31598413
thanks buddy its good to hear from you after a while i did manage to do it
0

Featured Post

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you've heard about htaccess and it sounds like it does what you want, but you're not sure how it works... well, you're in the right place. Read on. Some Basics #1. It's a file and its filename is .htaccess (yes, with a dot in the front). #…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question