CODEWITHSUNDEEP
phpmysql
maverabil
maverabil

Reputation: 188

fetch data from multiple tables

I have 4 table for my Ads Portal. Firstly main table name is ads

+-------------+
| Field       +
+-------------+
| id      +
| ads_title   +
+-------------+

example data for ads table;

+----+-----------------+
| id | ads_title       |
+----+-----------------+
| 20 | 2006 CITROEN C4 |
| 27 | EMEKLI OGRETMEN |
| 28 | Harika 10 n     |
| 34 | HATASIZ BOYASIZ |
| 49 | BAYANDAN 2009   |
+----+-----------------+

for ads specifications stored in a table. table name is ads_spc

+---------+
| Field   |
+---------+
| id      |
| key_name|
+---------+

data example for ads_spc table;

+----+-------------+
| id | key_name    |
+----+-------------+
|  1 | Date        |
|  2 | Km          |
|  3 | Colr        |
|  4 | Engine      |
|  5 | Pw. Engine  |
|  6 | Oil         |
|  7 | Speed       |
|  8 | Boody Type  |
|  9 | Traction    |
| 10 | Warranty    |
+----+-------------+

Lastly stored specification values for ads_spc by ads_id. table name is ads_values

+------------+
| Field      |
+------------+
| id         |
| ads_id     |
| spc_id     |
| value      |
+------------+

for example this table data

+----+--------+--------+-------+
| id | ads_id | spc_id | value |
+----+--------+--------+-------+
| 25 |     49 |      9 |  2119 |
| 26 |     49 |     10 |  2131 |
| 27 |     34 |      1 |  2010 |
| 28 |     34 |      2 |  8900 |
| 29 |     34 |      3 |     4 |
| 30 |     34 |      4 |    22 |
| 31 |     34 |      5 |    40 |
| 32 |     34 |      6 |    60 |
| 33 |     34 |      7 |    65 |
| 34 |     34 |      8 |    66 |
+----+--------+--------+-------+

I want to using these tables list ads (table: ads) with specifications (table:ads_spc) and ads values (table:ads_values) in a data row.

Union to; ADS + ADS_SPC + ADS_VALUES (All values searchable)

I'm using this case: 

SELECT SQL_CALC_FOUND_ROWS 
*
FROM ads AS a
LEFT JOIN ads_spc AS aspc 
LEFT JOIN ads_values AS aval ON aval.ads_id=a.id AND aval.spc_id=aspc.spc_id

How can I union to one row? Sorry for my english.

Upvotes: 2

Views: 478

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

Why union there is no sense for the union you should join these tables 

SELECT v.id, a.ads_title,s.key_name,v.value FROM `ads` a
INNER JOIN `ads_values`  v ON (a.id =v.ads_id)
INNER JOIN `ads_spc` s ON(v.spc_id =s.id) WHERE s.key_name='Color'

Upvotes: 1

Related Questions