[SOLVED] Sql help needed

[freeloader]

Hi guys,

 

My mysql knowledge isn't too great, I prefer the coding of the php scripts I would really appreciate some help here.

 

I have two tables. One is called MC_memberlist (with rows Name and ForumAlt), the other is called MC_forum (with row ForumName).

 

MC_memberlist.Name is their member name, and ForumAlt is a column that's only used if they register on our forum with a different name. MC_forum is a forum memberlist and contains everybody who is registered on the forum as MC_forum.ForumName.

 

I would like 2 different queries.

 

1) Query that can tell me who from memberlist hasn't registered on our forum yet. (I.e.: output MC_memberlist.Name where MC_memberlist.Name or MC_memberlist.ForumAlt does not match MC_forum.ForumName).

 

This is what I came up with:

SELECT MC_memberlist.Name FROM MC_memberlist, MC_forum WHERE ((MC_memberlist.Name) OR (MC_memberlist.ForumAlt) != MC_forum.ForumName)

 

But that didn't work.

 

2) Query that tells me who is registered on forum but is no longer a member. (I.e.: output MC_forum.ForumName where MC_forum.ForumName does not match MC_memberlist.Name or MC_memberlist.ForumAlt.

 

I hope this was clear enough.

 

Any help would be appreciated, thank you in advance

[kickstart]

Hi

 

First one, try this:-

 

SELECT DISTINCT MC_memberlist.Name
FROM MC_memberlist
LEFT OUTER JOIN MC_forum.ForumName ON MC_memberlist.Name = MC_forum.ForumName OR MC_memberlist.ForumAlt = MC_forum.ForumName
WHERE MC_forum.ForumName IS NULL

 

Basically join the 2 together on the fields you want to match, doing an outer join but only return those where there isn't a record found the onthe MC_forum table. The DISTINCT may not be needed (depends on if 2 different Names could share a ForumName, or the other way round).

 

Similarly for your 2nd query:-

 

SELECT MC_forum.ForumName
FROM MC_forum
LEFT OUTER JOIN MC_memberlist ON MC_forum.ForumName = MC_memberlist.Name OR MC_forum.ForumName = MC_memberlist.ForumAlt
WHERE MC_memberlist.Name IS NULL AND MC_memberlist.ForumAlt IS NULL

 

Might be a typo or 2 in there but hopefully gives you an idea.

 

All the best

 

Keith

[freeloader]

The second one worked great, thanks a bunch!

 

The first one however returned following error:

#1142 - SELECT command denied to user '#censored#' for table 'ForumName'

 

Not sure what that means, first time I'm getting this error. As said previously, my knowledge of mysql is limited to the very basic commands, so I might've missed something here...

 

Thanks in advance for any feedback.

[kickstart]

Hi

 

Think that is my fault for a typo:-

 

SELECT DISTINCT MC_memberlist.Name

FROM MC_memberlist

LEFT OUTER JOIN MC_forum.ForumName ON MC_memberlist.Name = MC_forum.ForumName OR MC_memberlist.ForumAlt = MC_forum.ForumName

WHERE MC_forum.ForumName IS NULL

 

Bit in red shouldn't be there.

 

All the best

 

Keith

[freeloader]

It worked perfectly, thanks for the help and feedback