CODEWITHSUNDEEP
mysqlsqljoinunion
Dave_P
Dave_P

Reputation: 173

Mysql query to get columns from 2 Tables into one result set

I am trying to create a query that displays a4, a2, a3 from TableA and date from TableC for all Items connected by b.b1 = 2. The output should be in chronological order by date descending.

TableA                 
___________________________________________
| a1   | a2   | a3   | a4   | a5   | a6   |
-------------------------------------------
| 1    |  222 | 333  | 444  | 555  | 665  |
-------------------------------------------
| 2    |  asd | sdf  | ert  | jkl  | xcv  |
-------------------------------------------
| 3    |  111 | tyu  | bnm  | abc  | xyz  |
-------------------------------------------
| 4    |  222 | yyy  | dfd  | hgg  | 544  |
-------------------------------------------  
| 5    |  222 | y56  | df5  | hg7  | 234  |
------------------------------------------- 

TableB
___________________
| b1  | b2  | b3  |
-------------------
| 1   | 717 | c   |
------------------- 
| 2   | 727 | d   |
------------------- 
| 3   | 737 | c   |
------------------- 
| 4   | 747 | d   |
------------------- 

TableC - c2 is foreign key to a.a1 - c3 is foreign key to b.b1
_____________________________
| c1  | c2  | c3  |  date   |
-----------------------------
| 1   | 2   | 2   |12-01-12 | 
-----------------------------
| 2   | 1   | 1   |12-05-12 | 
-----------------------------
| 3   | 5   | 2   |12-07-12 | 
-----------------------------
| 4   | 3   | 1   |12-14-12 | 
-----------------------------
| 5   | 4   | 2   |12-18-12 | 
-----------------------------

It should look Like this...

____________________________
| a4  | a2  | a3  |  date  |
----------------------------
| ert | 222 | 333 |12-01-12| 
----------------------------
| df5 | 222 | y56 |12-07-12| 
----------------------------
| dfd | 222 | yyy |12-18-12| 
----------------------------

I got it down to the point that If I split my query into two pieces I get the info from the two tables, and the "UNION ALL" makes my date add rows at the bottom of the table (with two other columns I dont want, c2 and c1), I actually want a column called date next to a3 in my results. How do I get that column "date" into the result set?

SELECT a.a4, a.a2, a.a3
FROM TableA a
JOIN TableC c ON c.c2 = a.a1
JOIN TableB b ON b.b1 = c.c3 and c.c3 = 2
UNION ALL
SELECT d.date,d.c2,d.c1 
From TableC d
JOIN TableB b ON b.b1 = d.c3 and d.c3 = 2

Please ask any questions you need to, this is killing me.

Upvotes: 0

Views: 892

Answers (1)

Matthew
Matthew

Reputation: 9949

There are other confusing items here, but without digging too deep this is as easy as:

SELECT a.a4, a.a2, a.a3, c.date
FROM TableA a
JOIN TableC c ON c.c2 = a.a1
JOIN TableB b ON b.b1 = c.c3 and c.c3 = 2

The UNION does not do you were thinking it did.

Also, is there any need to join table b?

SELECT a.a4, a.a2, a.a3, c.date
FROM TableA a
JOIN TableC c ON c.c2 = a.a1 and c.c3 = 2

Upvotes: 2

Related Questions