How to get one column from 2 different tables

Question

I have 2 tables in MySQL registerSMSusers and GroupsSMS. Both the tables have a column named as mobile. From an HTML form I am getting comma separated values like test,alltest,john. These comma separated values will be present in either of the 2 tables. For example test (name column) is present in registerSMSusers and alltest is present in GroupsSMS (GroupName column).

In Java I can split with comma and then check if its present in any of the tables or not.If present then get the mobile. Just wanted to know are there any SQL queries for the same.

This is SQL schema

DROP TABLE IF EXISTS `GroupsSMS`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `GroupsSMS` (
  `Name` varchar(50) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `mobile` varchar(20) DEFAULT NULL,
  `GroupName` varchar(20) DEFAULT NULL,
  `GroupID` int(11) NOT NULL AUTO_INCREMENT,
  `dataselected` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`GroupID`)
) ENGINE=MyISAM AUTO_INCREMENT=191 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `registerSmsUsers`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `registerSmsUsers` (
  `name` varchar(50) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `mobile` varchar(20) DEFAULT NULL,
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`uid`),
  UNIQUE KEY `mobile` (`mobile`),
  UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=83 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

And this is the sqlfiddle

@DhruvJoshi for GroupsSMSusers Groupname and For registersmsusers name — SpringLearner, Jan 29 '14 at 06:48
you should try this link http://stackoverflow.com/questions/471914/can-you-split-explode-a-field-in-a-mysql-query — DhruvJoshi, Jan 29 '14 at 06:52

score 1 · Answer 1 · edited May 23 '17 at 10:29

1

MySQL does not have a ready made function for splitting a CSV string. You have to do it manually using SUBSTRING using SUBSTRING_INDEX or using a REGEXP. See details on a similar problem here

After you have say split the CSV into actual strings which are stored in a table 'CSVTable' {id, strvalue}, you can check like

SELECT G.mobile as mobilenumber 
FROM 'GroupsSMS' G LEFT JOIN 'CSVTable' C 
on G.GroupName =C.strvalue 
WHERE C.strvalue is NOT NULL

UNION 

SELECT R.mobile as mobilenumber 
FROM 'registerSMSusers' R LEFT JOIN 'CSVTable' C 
on R.name=C.strvalue 
WHERE C.strvalue is NOT NULL

Note I have not used UNION ALL to get distinct set values

Pseudo code for getting values into temp table

DECLARE @CSVTABLE TABLE ( id int not null, strvalue NVARCHAR(400) NOT NULL)
DECLARE @var int
SET @var=1
DECLARE @STREXP NVARCHAR(MAX)
DECLARE @BUFF NVARHCAR(400)
SET @BUFF=SUBSTRING_INDEX(@STREXP,',',1)
SET @STREXP=REPLACE(@STREXp,@BUFF+',','')
    WHILE @BUFF IS NOT NULL DO
    INSERT INTO @temp VALUES(@var,@BUFF)
    @var=@var+1
    @VUFF
    END WHILE

edited May 23 '17 at 10:29

Community

1
1

answered Jan 29 '14 at 06:54

DhruvJoshi

17,041
6
41
60

But after the value is splitted how can I check if it is present in which table and the mobile number.For example I splitted the full string `test,alltest,john` and Now I want to get the mobile number of john.Then can you tell me how? – SpringLearner Jan 29 '14 at 06:57
I've updated my answer. Please let me know if you need any help/clarification – DhruvJoshi Jan 29 '14 at 09:00
After splitting I am not storing in the table.I will use `String.split(",");` to split using java.After the string is splitted what will be the sql query.See after splitting I get **test** as one data.Now how to use sql query.Hope you understood my problem – SpringLearner Jan 29 '14 at 09:07
This will cause SQL query to be run each time for each split part and also you will have to get distinct values after combining result set for each split part – DhruvJoshi Jan 29 '14 at 09:13
The CSVTable will be temporary table? – SpringLearner Jan 29 '14 at 09:20
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/46317/discussion-between-jquerylearner-and-dhruvjoshi) – SpringLearner Jan 29 '14 at 09:25

score 1 · Accepted Answer · edited Jun 26 '16 at 10:00

I you have split the string in Java

String names[] = csv.split(',');

You can search for the corresponding mobile number in either registerSmsUsers or GroupsSMS with

PreparedStatement stmt = conn.prepareStatment("select u.mobile from registerSmsuser u where u.name = ? union select g.mobile from GroupsSMS g where g.groupname = ?");
stmt.setString(1, names[0]);
stmt.setString(2, names[0]);
ResultSet rs = stmt.executeQuery();
if (rs.first()) {
    // do something with the mobile number
}

This will select entries from both the user and the groups table. If you need to know, where the number is from, you can add a fixed string to your select

select u.mobile, 'user' as origin from registerSmsuser u ...
union
select g.mobile, 'groups' as origin from GroupsSMS g ...

How to get one column from 2 different tables

2 Answers2