CODEWITHSUNDEEP
mysql
Henrique Müller
Henrique Müller

Reputation: 157

Select Multiple Columns From Multiple Tables

I'm a beginner at MySQL and I'm having a hard time trying to figure out how to solve this problem:

I have two tables with many entries each. Let's say these are the tables:

   Table 1       ||          Table 2
-------------    ||    -------------------
| dt1 | dt2 |    ||    | dt3 | dt4 | dt5 |
-------------    ||    -------------------
|  1  | abc |    ||    |  3  | wsx | 123 |
|  7  | asd |    ||    |  3  | qax | 456 |
| 19  | zxc |    ||    |  4  | rfv | 789 |
-------------    ||    -------------------

What I want to do is to have as a result one table with columns "dt2", "dt4" and "dt5" and with only one entry. For that, the query I'll apply to each table may even have to LIMIT the results. To get the results I want from each table separetelly I would do the following:

SELECT `dt2` FROM `table1` WHERE `dt1`=7;

and

SELECT `dt4`,`dt5` FROM `table2` WHERE `dt3`=3 LIMIT 0,1;

One more thing, I don't want to use a subquery for each column, because in the real thing I'm trying to solve, I'm calling 5 or 6 columns from each table.

Just to make clear, what I want to get is something like this:

-------------------
| dt2 | dt4 | dt5 |
-------------------
| asd | qax | 456 |
-------------------

Upvotes: 6

Views: 59386

Answers (4)

Rasht Nightmare
Rasht Nightmare

Reputation: 1

In here there is smth called INNER JOIN , CROSS JOIN , LEFT JOIN and RIGHT JOIN in MYSQL and also SQL Server that allows you yo get data from different tables as much you want via conditions based on your columns;

So let's start :

First Let's create our tables (sample1,sample2) :

--Create Table sample1 :
CREATE TABLE sample1 (id BIGINT NOT NULL AUTO_INCREMEN , name_sample1 VARCHAR(100),age INT);

--Create Table sample2 :
CREATE TABLE sample2 (id BIGINT NOT NULL AUTO_INCREMEN , name_sample2 VARCHAR(100));

-- Now Let's put an trigger in order to avoid getting incorrect values :

DELIMITER $$

CREATE TRIGGER IF NOT EXISTS insert_sample1_trigger BEFORE INSERT ON sample1 
FOR EACH ROW
BEGIN

IF NEW.name_sample1 <> "" AND NEW.age <> "0" THEN

INSERT INTO sample2 (name_sample2) VALUES (NEW.name_sample1);

ELSE
INSERT INTO sample1 (name_sample1,age) VALUES ("Unknown" , 10);
INSERT INTO sample2 (name_sample2) VALUES ("Unknown"); 

END IF;

END$$    

DELIMITER ;

After U ran this query , trigger will be added;

--- Now Inserting 

INSERT INTO sample1(name_sample1,age) VALUES ("SomeOne Name" , 15);

After running this query the name will be added to sample2 table, because id is auto increment it's not needed to be called in the insert query

id name_sample1 age
1 SomeOne Name 15

But if I give another value ...

INSERT INTO sample1(name_sample1,age) VALUES ("SomeOne Name" , 0);
id name_sample1 age
1 Unknown 10

-- Now at last Select query what we were waiting for :

SELECT * FROM sample1 s1 INNER JOIN sample2 s2 USING(id) GROUP BY s1.name_sample1 
ORDER BY s1.name_sample1 DESC

This query selects all columns from tables sample1 and sample2 if you want to show some other columns change * via your column name.

That's it

Upvotes: 0

JJarrard
JJarrard

Reputation: 56

Ben's answer solved my similar issue.

SELECT t1.dt2, t2.dt4, t2.dt5, t2.dt3 #get dt3 data from table2
FROM table1 t1, table2 t2
WHERE t1.dt2 = 'asd' AND t2.dt4 = 'qax' AND t2.dt5 = 456


| asd | qax | 456 | 3 |

'3' being the data I require by querying the 'qax', 456 data in table2, otherwise you're specifying exactly what data will be returned from the columns.

I only had 2 tables to query in my instance, so the AND expression I can get away with using, it probably isn't best practice and there's most likely a better way for matching data from multiple tables.

EDIT: I've just realised this question is 5 years old.. I hope you achieved what you wanted to by now.

Upvotes: 4

coder_strange
coder_strange

Reputation: 298

SELECT a.dt2, b.dt4, b.dt5
FROM table1 a, table2 b
WHERE a.dt2 = 'asd'
LIMIT 0,1;

Ben's answer is good, you can use more tables just by separating them by comma (,) , but if there's relationship between those tables then you should use some Sub Query or JOIN

Upvotes: 2

Ben
Ben

Reputation: 2024

SELECT a.dt2, b.dt4, b.dt5
FROM table1 a, table2 b
WHERE a.dt2 = 'asd'
LIMIT 0,1;

Upvotes: 12

Related Questions