-3

I'm doing a left outer join on two tables, A and B, and trying to get one value from B and rename it as the id of its previous column. So it looks like this:

SELECT A.*, B.value AS B.id FROM A LEFT OUTER JOIN B ON ...

AS B.id does not work. I've tried a couple of variants and searched Google and SO but can't find it. Is there a way to do this?

EDIT: I'm sorry, I should have mentioned that the table I was trying to join was always a one row table. When I joined a one row table, I wanted to use the id of that only row as an identifier. I was planning on joining each row separately, and having their values be under columns identified by their previous id. I got the answer I wanted though. It looks like what I wanted to do is impossible (and was a bad approach anyway), and my solution was really just to take a different approach.

Isaac
  • 2,246
  • 5
  • 21
  • 34
  • 1
    I need clarification. could you show some data and expected results? Are you wanting a dynamic number of columns (perhaps a pivot) or are you wanting to name the column based on the name of data in a table.... which would mean b.value is the same on all records? Based on comments you want a dynamic SQL statement and a pivot. like this: http://stackoverflow.com/questions/12630128/mysql-dynamic-pivot So yes there is a way to do this; however it involves DYNAMIC SQL – xQbert Jan 02 '15 at 20:23
  • @xQbert I want a column named based on data in the table (B's "id" column). It looks like this task is not possible though. – Isaac Jan 02 '15 at 20:28
  • It is possible. look at the link in the first comment and it's example. It is possible though Dynamic SQL – xQbert Jan 02 '15 at 20:28
  • Oh sorry, that link wasn't there when I posted. I'll check it out. – Isaac Jan 02 '15 at 20:29
  • 1
    and here's a working [SQL fiddle](http://sqlfiddle.com/#!2/0a068/6) courtesy of @bluefeet in http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns – xQbert Jan 02 '15 at 20:31
  • I disagree with the vote to close. I think it's quite clear what's being asked. Doesn't mean I know the answer. – Dan Bracuk Jan 02 '15 at 20:41
  • The links xQbert has given me are sending me down the right path... I think. They're doing something a little different than what I want, but I think the answers could still get me to the right place. I'm going to keep this open for a little longer. – Isaac Jan 02 '15 at 20:49
  • 1
    If I understand your question correctly, this has nothing to do with dynamic SQL (i.e. creating queries based on some application code), but you are looking to receive some results with different column names. You wish to get some results when the column name for each record is different (i.e. based on some other column results of a given record)? If so, I think you should rethink your approach. – user1032531 Jan 02 '15 at 20:55
  • This is my conclusion: What I wanted to do (the question I asked) is not possible. But! I can tackle this a different way and use a dynamic pivot as seen in the second link xQbert provided and get exactly what I am actually trying to currently accomplish. I guess my biggest problem was approaching this problem the wrong way. I'm going to accept radshop's answer, but comment on it and link to the dynamic pivot in case anyone else stumbles across this problem in the same way I did. – Isaac Jan 02 '15 at 21:21

4 Answers4

2

No, you can't do so. If you wish to do so, use application code (PHP, etc).

EDIT. Why you would ever want to do so, I don't know, but something like the following would work. (untested)

$rs=array(
array('a_id'=>'one','b_value'=>1),
array('a_id'=>'two','b_value'=>2),
array('a_id'=>'three','b_value'=>3)
);
foreach($rs as $key=>$arr) {
$rs[$key][$arr[$arr['a_id']]=$rs[$key]['b_value'];
unset($rs[$key]['b_value']);
}
user1032531
  • 24,767
  • 68
  • 217
  • 387
  • Aww man. That's what it's looking like. I'm going to hold my breath and wait a little longer before accepting your answer. I'm already going to start thinking of an alternative way to approach my problem though. – Isaac Jan 02 '15 at 20:04
  • I don't want to use application code, so I'll have to find another way to do my queries. But basically, I'm working on a Rails project where one of my models, model A, has a has_many relationship with model B. There is a system set up to filter out some A's depending on whether they have the right B's. The previous implementation works with OR, but not AND because the query was just a LEFT OUTER JOIN. So every row in the table was an A with one B value. I'm trying to join all B's to a single row of A, but once the B's are joined, each B value has to have a different identifier. – Isaac Jan 02 '15 at 20:17
1

A statement like yours cannont be done in SQL. Keep in mind that in SQL you have to think in terms of a set (a set of rows) and elements (columns) of the set. You can't return a single column but name that element something different for each row. If you want it named differently, you have to return it as a different column.

(Also your query has 2 FROMs in the same select, which doesn't work.)

For example, if you want a different column for each value of B, you could do something like this:

SELECT 
  A.*,
  case when B.id = 1 then B.value end as B1,
  case when B.id = 2 then B.value end as B2,
  (etc...)
FROM A LEFT OUTER JOIN B ON ...
radshop
  • 602
  • 5
  • 19
  • Sorry about the FROM's, typo. That's been fixed. I guess this is a solution, but this won't work for me either as B's id could be anything. Thank you though! – Isaac Jan 02 '15 at 20:24
  • In the end, what I was trying to do is impossible, but I'll be able to solve my problem using a dynamic pivot. See: http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns?lq=1. It is kind of similar to your answer. Except yours is static and I needed a dynamic version. – Isaac Jan 02 '15 at 21:28
  • @Isaac - not presuming to tell you what your requirements are, but be aware that the dynamic pivot approach will not scale well to large data volumes. An alternative is to use that same approach to generate the SQL from which you create a view or stored procedure, which gets an execution plan optimized at the time you create it and so performs better. New values of B are not added dynamically, so you have to update the view or proc as new values of B emerge. Depends on your situation. – radshop Jan 02 '15 at 22:54
0

One of the following should work:

AS `B.id`

or

AS id

the dot isn't valid in the alias name

snow_FFFFFF
  • 3,235
  • 17
  • 29
0

Try maybe:

SELECT A.*, B.value AS B_id from A LEFT OUTER JOIN B ON...