By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

using union, temp columns, in a SQL based search string

P: 2
Trying to wrap my head around how a good SQL search string would work. I have three tables (two in the example below for learning) with different column names (but similar data.) I'm using aliases to make uniform columns. I can get all the matching rows from the different tables, but I need to know what table the row came from for the results to be actually useful (linking, etc)

How can I ADD a completely fabricated column to my result set so I can know downstream which rows are stories, and which ones are gallery, etc. Here's a simplified version of my SQL:

SELECT storiesTbl.heroName as name, storiesTbl.writername as author FROM storiesTbl WHERE storiesTbl.heroname like '%marley%'
UNION
SELECT gallerytbl.title as name, gallerytbl.artist as author FROM gallerytbl WHERE gallerytbl.title like '%marley%'



Or is this backwards logic and is there a better way to do this?

Thanks in advance to anyone who can help!
Aug 7 '09 #1
Share this Question
Share on Google+
3 Replies


Delerna
Expert 100+
P: 1,134
I am assuming your query is working ok and you just want to add a field identifying the table

Expand|Select|Wrap|Line Numbers
  1. SELECT 'storiesTbl' as Tbl, 
  2.        storiesTbl.heroName as name, 
  3.        storiesTbl.writername as author 
  4. FROM storiesTbl 
  5. WHERE storiesTbl.heroname like '%marley%'
  6.  
  7. UNION 
  8.  
  9. SELECT 'gallerytbl' as Tbl,
  10.        gallerytbl.title as name, 
  11.        gallerytbl.artist as author 
  12. FROM gallerytbl 
  13. WHERE gallerytbl.title like '%marley%'
  14.  
Aug 7 '09 #2

P: 2
Ah ha! That makes a whole lot of sense. Thank you much...

Also, I haven't researched this yet (so I guess I'm just being lazy by asking) but if I had two columns in a row and I wanted to concatenate the strings and return them in a single column, how could I accomplish this?
Aug 7 '09 #3

Delerna
Expert 100+
P: 1,134
Expand|Select|Wrap|Line Numbers
  1. Select CharField1 + 'optional sparator characters' + CharField2 as ConactenatedField
  2. From TheTable
  3.  
  4. or if the field is not of type char or varchar
  5.  
  6. Select convert(varchar(8),IntField1) + 'optional sparator characters' + CharField2 as ConactenatedField
  7. From TheTable
  8.  
I encourage you to research first, you will learn most from researching things for yourself and asking for help where you have difficulty ;)
Aug 9 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.