I have five tables "ArtBook,Eng,comm,Law,mgt" and every table has a same column and i want to be search all the information that particular book by its id e.g-
select * from ArtBook,Eng,Comm,Law,mgt where @BookId=ArtBooks.BookId
or @BookId=CommBooks.BookId
or @BookId=Eng.BookId
or @BookId=Law.BookId
or @BookId=mgt.BookId
If all the tables store books of different categories, then combine them back into one table and add a 'category' column. Categorising into separate tables like this isn't a good idea for the reasons you've demonstrated.
select *
from ArtBook
where BookId = @BookId
union all
select *
from Eng
where BookId = @BookId
union all
select *
from Comm
where BookId = @BookId
union all
select *
from Law
where BookId = @BookId
union all
select *
from Mgt
where BookId = @BookId
One could use the UNION or UNION ALL operator.
(SELECT * FROM ArtBook)
UNION ALL
(SELECT * FROM Eng)
...
WHERE @BookId=ArtBooks.BookId or
@BookId=CommBooks.BookId or
@BookId=Eng.BookId or
@BookId=Law.BookId or
@BookId=mgt.BookId
et cetera, et cetera. Though you may consider restructuring how your data is stored to make this sort of thing easier.
WHERE clause just needs one condition in each part of the UNION ALL not 5.
UNION ALLrather than aJOINbut it doesn't seem like the correct structure to me. Why not one table for books with either a column for genre or a separate junction table if you want to allow for books that span multiple genres.