CODEWITHSUNDEEP
sqlmysqlsql-server
Night Walker
Night Walker

Reputation: 21280

How can I do the following query to get needed information

I have two tables

I want to get following result:

rows only that CompList.CompId == BookingTable.CompId (only CompId that is in both tables)

and I need in the result columns from CompList: CompId , McID , station , slot ,subslot and from BookingTable: LineID , McID , station , slot ,subslot

And how will I be able to distinguish between same columns with same table in the result table them in the result table?

Thanks for help.

Upvotes: 0

Views: 101

Answers (6)

foekall
foekall

Reputation: 79

SELECT 
   cl.CompId , cl.McID , cl.station, cl.slot, cl.subslot, 
   bt.LineID, bt.McID, bt.station, bt.slot, bt.subslot 
FROM 
   CompList cl 
INNER JOIN 
   BookingTable bt ON cl.McID=bt.McID

Upvotes: 0

Sushant Prasad
Sushant Prasad

Reputation: 122

According to your question, to distinguish columns of CompList and BookingTable use alias name for the columns you want to get in the result.

Eg: Let there are two table t1 & t2, both having same names in cols say cid, name, roll and address.

to get t1.cid == t2.cid (only cid that is in both tables)

we write: select t1.cid, t1.name as name1, t1.roll as roll1, t1.address as address1, t2.name as name2, t2.roll as roll2, t2.address as address2 from t1, t2 where t1.cid=t2.cid;

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

You do an inner join between the tables and you use column alias to distinguish between columns with the same name from different tables. http://msdn.microsoft.com/en-us/library/ms187731.aspx

select T1.Name as T1Name, T2.Name as T2Name
from T1
  inner join T2
    on T1.ID = T2.ID

Upvotes: 0

bniwredyc
bniwredyc

Reputation: 8839

Use aliases:

SELECT 
    CL.CompId,
    CL.McID,
    CL.station,
    CL.slot,
    CL.subslot,
    BT.LineID,
    BT.McID     as BookingMcId, 
    BT.station  as BookingStation, 
    BT.slot     as BookingSlot, 
    BT.subslot  as BookingSubslot
FROM CompList as CL
JOIN BookingTable as BT ON BT.CompId = CL.CompId

Upvotes: 2

lweller
lweller

Reputation: 11327

select c.CompId,c.D,c.station,c.slot,c.subslot,b.neID,b.McID,b.station,b.slot,b.subslot from CompList c join BookingTable b on c.ComId=b.CompId

Upvotes: 0

leeny
leeny

Reputation: 626

preface all column names with table name in your select statement, and use aliases to disambiguate the columns in the result set:

http://www.w3schools.com/SQl/sql_alias.asp

that said, i am unclear on why you have so much seemingly redundant data.

Upvotes: 2

Related Questions