1

I am relatively new to SQL Server so I hope you can help.

Basically I have two tables of information. The first looks like:

  • table1 [ID, Name]
  • table2 [FKID, ID, Name]

I want to do a statement like:

SELECT table1.Name, table2.Name
FROM table1
LEFT JOIN table2 ON table2.FKID = table1.ID

This results in something like:

['Name 1a', 'Name 2a']
['Name 1b', NULL]
['Name 1c', NULL]

But what I want is to just select ONE column, that either being Name 1x or Name 2x if it wasn't null (there was no key match on the left join).

I imagine the typical response will be to solve this using my programming language, but this is proving to be extremely difficult in Visual Web Developer using GridViews and all this nonsense, so what would be very helpful would be a database solution.

Thanks a lot!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
deed02392
  • 4,799
  • 2
  • 31
  • 48

2 Answers2

7
SELECT coalesce(table1.Name, table2.Name) as YourName
FROM table1 
LEFT JOIN table2 ON 
table2.FKID = table1.ID

COALESCE() will return the first NOT NULL value. Is this what you're looking for?

  • 2
    You probably meant to write 'coalesce' (edit can't fix less than 6 chars :) – PinnyM Jan 26 '12 at 15:47
  • @PinnyM thanks! Fixed!! That's always an odd function to write. :) –  Jan 26 '12 at 16:01
  • Hmm, I did look at this but then I thought how can I apply this to my problem when table1's value is not null first. Perhaps if I swap them around in the COALESCE then I'll get table2's value first if it exists? – deed02392 Jan 27 '12 at 08:51
0

COALESCE() will merge rows. If you want to merge columns try something like:

SELECT CONCAT(ColA, ColB) AS ColAB
pabloab
  • 37
  • 4