Solved

mysql join

Posted on 2009-06-30
2
291 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help needed with Powershell  XML to MySQL 5 75
two ways encryption with php 3 44
Import MySQL data into MS Access using VB.Net interface 5 52
MySQL Persistent Connections 10 35
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Creating and Managing Databases with phpMyAdmin in cPanel.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 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