how to select the same from 2 tables

[atferraz]

How to select records from two or more tables that have a set of equal attributes.

Ex: from 3 tables that have the same attributes first name, last name and age, wont to get all records that has the BILL, KILL. and order by age.

Any one can help on this issue

 

[nigelrivett]

select *
from
(
select source = 'tbl1', fname, lname, age from tbl1
union all
select source = 'tbl2',fname, lname, age from tbl2
union all
select source = 'tbl3',fname, lname, age from tbl3
) a
where fname = 'kill' and lname = 'bill'
order by age

or

select source = 'tbl1', fname, lname, age from tbl1
where fname = 'kill' and lname = 'bill'
union all
select source = 'tbl2',fname, lname, age from tbl2
where fname = 'kill' and lname = 'bill'
union all
select source = 'tbl3',fname, lname, age from tbl3
where fname = 'kill' and lname = 'bill'
order by age


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[atferraz]

Think I prefer the second one. By the way forgot to say: the age is in fact the birthday date that is a string and need to be converted in the process.

Thanks

 

[nigelrivett]

Getting age from birthdate - always a pain. just do it in all the parts of the unions.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[atferraz]

Not that. The problem is that my date fields are strings and order on string isn’t very much what I want. So I need to know how to convert date strings in SQL date format

 

[dbomrrsm]

SELECT CONVERT(VARCHAR(10), GETDATE(), 101)--will return 08/17/2004

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) --will return 17/08/2004

--Style 101 is USA and 103 is British.

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

SORRY

SELECT CAST(YOURDATE AS DATETIME)

[blue]DBomrrsm[/blue]