Sql select data from two table with different columns

Question

I have 2 table with different column names.

Table1

alias_id | key | keyword
1          a=1   xx
2          b=1   xxxx

Table2

product_id | store_id
1            1   
2            7

I want the query the all keyword row from the table1 where the key a=1 and in the table2 the product_id=1 and store_id=7. In the table1 the key column contains like this key_id=20 but in the table2 I have a column product_id which is contains 20

I tried a lot of option like union and join, but somehow don't want to work for me.

How does the two tables relate? Is it that Table1.alias_id == Table2.product_id ? — Sach
– Sach, Commented Jun 15, 2015 at 23:08
This is my problem, the two table is not related. So I don't know any solution is exist? — Robert
– Robert, Commented Jun 15, 2015 at 23:11
So why do you need to query both tables at once? Basically unions are used to combine result sets w3schools.com/sql/sql_union.asp and joins to combine tables using a common fields w3schools.com/sql/sql_join.asp . — Sach
– Sach, Commented Jun 15, 2015 at 23:16
I tried with union. SELECT * FROM table1 WHERE key LIKE "a=%" UNION SELECT store_id FROM table2 WHERE store_id=7 but i get error The used SELECT statements have a different number of columns. So need for me query the all keyword, but only when both criteria is exist. at table1 LIKE a=% and at table2 store_id=7 the more query is can be null — Robert
– Robert, Commented Jun 15, 2015 at 23:20
What you want exatly ?! alias_id and product_id are linking two table together ? — user4920811
– user4920811, Commented Jun 15, 2015 at 23:30

Mark · Accepted Answer · 2015-11-19 19:11:08Z

1

If you are querying two tables without any of the same fields and have a "where clause", use this:

WITH query1 as (Select t1.a from t1 where t1.a = 'xxxxx'),
     query2 as (Select t2.b from t2 where t2.b = 'yyyyy')
Select * FROM query1 FULL OUTER JOIN query2 on t1.a <> t2.b

answered Nov 19, 2015 at 19:11

Mark

111 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

score 0 · Accepted Answer · 2015-06-15 23:51:13Z

0

According to your description in comments, try this:

select key,keyword from table1 where key='a=1'
union all
select product_id,store_id from table2 where product_id='1' and store_id='7'

edited Jun 15, 2015 at 23:51

answered Jun 15, 2015 at 23:37

user4920811

Comments

54l3d · Accepted Answer · 2015-06-15 23:57:38Z

0

To use Union we have to make the two select statement have the same number of columns and the same data types,

Selected columns listed in corresponding positions of each SELECT statement should have the same data type.

select `alias_id`as c1, `key` as c2, `keyword` as c3
from Table1 where `key` LIKE 'a=%'
union all
select `product_id` as c1, CAST(`store_id` as CHAR(4)) as c2, '' as c3 
from Table2 where store_id=7

Live DEMO

edited Jun 15, 2015 at 23:57

answered Jun 15, 2015 at 23:47

54l3d

3,9734 gold badges35 silver badges59 bronze badges

3 Comments

user4920811 Over a year ago

What does CAST in your query ?

54l3d Over a year ago

@stack They need to have same data type, even if there is auto cast, I put it to make clear answer.

Robert Over a year ago

Sorry probably i can't explain very well this. So only need for me the query if 3 criteria is exist. Table1 LIKE a=|1| Table2 product_id=|1| store_id=7 Table1 LIKE a=|1| = Table2 product_id=|1| And after need for me a row with the keyword. Maybe this is something if statement. Need to query the keyword when all criteria is meets. Both mentioned solution gave for me back 2 rows.

Collectives™ on Stack Overflow

Sql select data from two table with different columns

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related