creating a new table from two existing tables ?

  • (2 Pages)
  • +
  • 1
  • 2

19 Replies - 1308 Views - Last Post: 09 March 2015 - 12:53 PM Rate Topic: -----

#1 Nana1991   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 53
  • Joined: 06-April 11

creating a new table from two existing tables ?

Posted 09 March 2015 - 10:12 AM

I'v been trying to create a table by combing two column from different tables but the result isn't the required
this is my sql query :

SELECT process1.ProcessId , Persons1.UserID
FROM process1   cross join Persons1 where 
process1.ProcessId='2' or process1.ProcessId='4' or
 process1.ProcessId='3' ;




the required output is :
2 1
4 2
3 1


but I got
2 1
2 2
3 1
3 2
4 1
4 2

if any one could please help me with this and let me know where my mistake is ,
andd I want to know if there is another way using create instead of select .

Is This A Good Question/Topic? 0
  • +

Replies To: creating a new table from two existing tables ?

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14174
  • View blog
  • Posts: 56,776
  • Joined: 12-June 08

Re: creating a new table from two existing tables ?

Posted 09 March 2015 - 10:17 AM

Why would you be using a cross join? Are you certain that is what you want to use?

http://blog.codingho...n-of-sql-joins/
Was This Post Helpful? 0
  • +
  • -

#3 Nana1991   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 53
  • Joined: 06-April 11

Re: creating a new table from two existing tables ?

Posted 09 March 2015 - 10:25 AM

no actually ,but I'v tried different ways and it all give me wrong output
Was This Post Helpful? 0
  • +
  • -

#4 JackOfAllTrades   User is offline

  • Saucy!
  • member icon

Reputation: 6246
  • View blog
  • Posts: 24,014
  • Joined: 23-August 08

Re: creating a new table from two existing tables ?

Posted 09 March 2015 - 10:36 AM

Think you want a UNION.

http://www.tutorials...ions-clause.htm
Was This Post Helpful? 0
  • +
  • -

#5 Nana1991   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 53
  • Joined: 06-April 11

Re: creating a new table from two existing tables ?

Posted 09 March 2015 - 10:44 AM

union is one of the options that I'v used but it combine all of them in one column
and I also want it to be in this exact order:
2 1
4 2
3 1
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14174
  • View blog
  • Posts: 56,776
  • Joined: 12-June 08

Re: creating a new table from two existing tables ?

Posted 09 March 2015 - 10:45 AM

How about you explain, in words, what you are looking to do. I am not sure what "2 1" means or how they are supposed to be related. It was never explained how you tried other joins and how those were not providing the output you wanted.
Was This Post Helpful? 0
  • +
  • -

#7 Nana1991   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 53
  • Joined: 06-April 11

Re: creating a new table from two existing tables ?

Posted 09 March 2015 - 11:01 AM

okay , I have two tables Persons1 and process 1:
Persons1:
------------------------
age | name | userID
------------------------
12 | john | 1
14 | jamal | 2


Process1:

-------------------------------------
Process | ProcessID
--------------------------------------
creating account | 1
Modify name | 2
enter location | 3
drop data | 4



these are the two tables I'm trying to create a new table from them

the new table should be :

-------------------------------------
ProcessID | UserID
-------------------------------------
2 | 1
4 | 2
3 | 1



my problem with this new table I'm unable to create it

This post has been edited by Nana1991: 09 March 2015 - 11:02 AM

Was This Post Helpful? 0
  • +
  • -

#8 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14174
  • View blog
  • Posts: 56,776
  • Joined: 12-June 08

Re: creating a new table from two existing tables ?

Posted 09 March 2015 - 11:13 AM

You are missing a big gap. Unless there is a table linking process ids to user ids you cannot just willy-nilly join your current two tables.. those have zero relation.
Was This Post Helpful? 0
  • +
  • -

#9 Nana1991   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 53
  • Joined: 06-April 11

Re: creating a new table from two existing tables ?

Posted 09 March 2015 - 11:31 AM

you mean by making one of the column a foreign key and reference it to the other one while creating the main tables


is that what u mean by linking ??
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14174
  • View blog
  • Posts: 56,776
  • Joined: 12-June 08

Re: creating a new table from two existing tables ?

Posted 09 March 2015 - 11:37 AM

I mean if you draw your user table on a piece of paper, and draw the process table on a piece of paper.. there is zero relationship between them. You need to create a table that makes the connection between user id and process id. You cannot create a relationship between user and process without it.
Was This Post Helpful? 0
  • +
  • -

#11 Nana1991   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 53
  • Joined: 06-April 11

Re: creating a new table from two existing tables ?

Posted 09 March 2015 - 11:47 AM

ahaa I guess I understand you I'll try it and see the result .
Was This Post Helpful? 0
  • +
  • -

#12 Nana1991   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 53
  • Joined: 06-April 11

Re: creating a new table from two existing tables ?

Posted 09 March 2015 - 12:06 PM

I'v linked it as yo said


create table link
(

Process_ID int,
User_ID int,
FOREIGN KEY (Process_ID)references process1(ProcessID),
FOREIGN KEY (User_ID) references Persons1(UserID)


);






then I tried to join it again and it showed the same result before
Was This Post Helpful? 0
  • +
  • -

#13 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14174
  • View blog
  • Posts: 56,776
  • Joined: 12-June 08

Re: creating a new table from two existing tables ?

Posted 09 March 2015 - 12:12 PM

What is the content of your table there?

What join did you use?
Was This Post Helpful? 0
  • +
  • -

#14 Nana1991   User is offline

  • D.I.C Head

Reputation: -1
  • View blog
  • Posts: 53
  • Joined: 06-April 11

Re: creating a new table from two existing tables ?

Posted 09 March 2015 - 12:16 PM

a cross join



SELECT process1.ProcessId , Persons1.UserID
FROM process1   cross join Persons1 where
process1.ProcessId='2' or'4' or'3' ;


Was This Post Helpful? 0
  • +
  • -

#15 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14174
  • View blog
  • Posts: 56,776
  • Joined: 12-June 08

Re: creating a new table from two existing tables ?

Posted 09 March 2015 - 12:17 PM

again - I really do not think you want to use a cross join. If you want the relationship between the tables then that third table I was talking about WOULD BE WHAT YOU WANT.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2