extracting data from multiple tables in sql db

Question

Hi i have a database which consists of 2 tables

userData table

userID  |  userPass | Firstname |  Surname | Status | IPAddress
--------------------------------------------------------------  
110110  |  123456   | James     |  Jackson | Online |  11.112.1

contacts table

    userID  |  contactID |
    --------------------- 
    110110  |  112114    |
    112112  |  110110    |
    110110  |  112444    |

The first table is the userdata table which I am using with my java application for login. I also have another table which stores the users contacts , I am trying to write a query to allow me to take the users contacts and display them by the first name, surname and ID in a JList, I am extremely puzzled any help would be appreciated. I had a look at inner joins but seem to be getting more stuck. how can I construct a query to get the data i need? I am also trying to make a separate query to display only the online users.

ok i have managed to use the following query:

SELECT userName
FROM userdata, contacts
WHERE userID = "110110" AND userData.userID = contacts.contactID

This is taking the userID "110110" from the contacts tables and the contactID's which correspond and returning the userName (from the userdata table)of the contacts. i have encountered a new problem where userID 112112 added 110110 as a friend. because of this 112112 is in the userID column and 110110 is in the contactID column (contacts table). this shouldnt be a problem as it still shows they are contacts. however I have tried to modify my query to also give me the name for the userID 112112. can someone please help me with this?

Do you want to just show all the users?

Mohsen Kamrani
– Mohsen Kamrani

2014-04-24 16:59:13 +00:00
Commented Apr 24, 2014 at 16:59 — Mohsen Kamrani
– Mohsen Kamrani, Commented Apr 24, 2014 at 16:59
You should read about JOIN SQL

Germann Arlington
– Germann Arlington

2014-04-24 16:59:31 +00:00
Commented Apr 24, 2014 at 16:59 — Germann Arlington
– Germann Arlington, Commented Apr 24, 2014 at 16:59

isah · Accepted Answer · 2014-04-24 17:06:03Z

1

name, surname and ID

Assuming by ID you mean contactID, otherwise additional join should be included to fetch data from Contacts.

select u.Firstname, u.Surname, uc.contactID from User u 
inner join UserContact uc on u.userID = uc.userID;

Comments

Amit · Accepted Answer · 2014-04-24 17:05:49Z

0

To get all the users -

select Firstname, Surname, u.contactID from userdata u inner join usercontacts uc 
on u.userID = uc.userID order by Firstname, Surname, u.userID

To get online users -

select Firstname, Surname, u.contactID from userdata u inner join usercontacts uc 
on u.userID = uc.userID where status = 'Online' order by Firstname, Surname, u.userID

answered Apr 24, 2014 at 17:05

Amit

2883 silver badges11 bronze badges

2 Comments

user3252991 Over a year ago

the contact ID is essentially a userID , I think I have struxtured my db wrong

user3252991 Over a year ago

I have made some changes and updated my question slightly please take a look, thanks.

Collectives™ on Stack Overflow

extracting data from multiple tables in sql db

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related