[SOLVED] Joining mySQL queries.

[Buddyb3ar]

I'm having some troubles with joining the following mySQL query:

 

$contactrows = $db->GetAll("SELECT user_contacts.*, online.* FROM user_contacts, online WHERE user_contacts.contact != online.onlineusr AND user_contacts.user='$user' ORDER BY user_contacts.contact ASC");

(using adoDB).

 

I'm trying to pull a list off contacts (from the table "user_contacts") and checking if the contacts aren't online (from table "online").

I'm having problem with that query, though.

Instead of coming out with a list of users that are offline, it returns with the user's contacts repeated for each row in the online table.

 

I'm not exactly sure if that is extremely clear (I'm having a little trouble explaining this), but (if for example, three people are viewing the site), instead of putting a list of:

online

user 1

offline

user 2

user 3

 

It's showing something like

online

user 1

offline

user 1

user 1

user 2

user 2

user 2

user 3

user 3

user 3

 

I think that clears it up a little (if you can't tell, I'm not exactly an expert with mySQL).

Can someone help me with this query problem? I'd appreciate it a lot. I've been trying to fix this for a few days, haha. (:

[teng84]

dude im bad reading the long story so i guess the error that u have is you dont define the similarity of the two table say in their fields instead u use != you to declare first the = where you will like saying  im joining this table because this fields is = to the fields of second table then do another condition you want

 

if thats not clear narrow explanation and ill try to code for you

[btherl]

What teng is saying is that you need a condition to sa how user_contacts and online are connected.  It will look something like this:

 

WHERE user_contacts.user = online.user

 

Without that, MySQL thinks that user_contacts and online are totally unrelated tables, and it generates EVERY possible combination of results.

[Buddyb3ar]

Alright, I see what you are both saying.

 

Then, I guess I'm looking for a way to make a "does not equal" join.

[teng84]

then joining is not for that issue from the word join think what does it mean

 

any how show the fields of your table and explain abit

[Buddyb3ar]

on the "user_contacts" table, there are:

"id",

"user",

"contact",

"label".

 

and on "online" there are:

"onlineusr",

"onlineIP",

"onlinetime".

 

I tried to make a different query, however, it still doesn't work.

$mailrows = $db->GetAll("
	SELECT user_contacts.* FROM user_contacts 
	LEFT JOIN online ON user_contacts.contact = online.onlineusr
	WHERE user_contacts.contact IS NULL AND user_contacts.user='$user' 
	ORDER BY user_contacts.contact ASC");

[teng84]

SELECT * FROM user_contact

INNER JOIN online ON onlineusr=user

WHERE user_contacts.contact != online.onlineusr AND user_contacts.user='$user' ORDER BY user_contacts.contact ASC

[Buddyb3ar]

Well, that sort of worked.

 

Now it's showing all of of $users contacts, even the ones that are currently in the online table.

[btherl]

If you want "In table A but not in table B", then left join is the way.  It looks like this:

 

SELECT user_contacts.* FROM user_contacts 
	LEFT JOIN online ON user_contacts.contact = online.onlineusr
	WHERE online.onlineusr IS NULL AND user_contacts.user='$user' 
	ORDER BY user_contacts.contact ASC

 

That will do this:

 

1.  Take all rows from user_contacts

2.  Filter by user_contacts.user = $user

3.  Join with online, setting online's columns to NULL where no matching row exists

4.  Filter for rows where online.onlineusr is null (that is, all rows which DON'T exist in table B but DO exist in table A)

[Buddyb3ar]

WOO! Thank you very much!

As I've been working with this, I've been looking into joining queries, and it seems like I've been making this a bigger problem then it should be.

 

Well, my problem is solved, so thank you all very much!