sql join two tables

JCWEBHOST
JCWEBHOST used Ask the Experts™
on
hey guys i have two table and need to join in one table using a select statement.

table one : menu
id
parent_id
title

table two: products
id
menu_id
name

my problem is the indentity in the id for bought table, so if they are enterd twice a erorr will oucur.

how can i fix the problem?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2013

Commented:
Use the table names as prefixes to distinguish the IDs:

SELECT Table1.ID, Table2.ID, etc...
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.Menu_ID
Anuradha GoliSystems Development / Support Specialist

Commented:
Output
CREATE TABLE [dbo].[TableMenu](
	[Id] [int] NOT NULL,
	[ParentId] [int] NULL,
	[Title] [nchar](20) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[TableProducts](
	[Id] [int] NULL,
	[Menu_Id] [int] NULL,
	[Name] [nchar](20) NULL
) ON [PRIMARY]

GO

SELECT	* 
FROM	TABLEMENU

SELECT	* 
FROM	TABLEPRODUCTS

SELECT	TM.ID,TITLE		AS MAINMENU,
		NAME			AS SUBMENU 
FROM	TABLEMENU TM
LEFT 
JOIN	TABLEPRODUCTS TP 
ON		TM.ID = TP.MENU_ID

Open in new window

Information Technology Specialist
Commented:
Besides qualifying the id fields with their table names, you need to create an alias for the column name - select table1.id as id1, table2.id as id2 ...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial