Need MySQL Query help

[Falcon Capt]

I am trying to find users who have had one subscription but not the other.

I am using this query to locate one of the subscription types:

SELECT username
FROM subscriptionlog
LEFT JOIN user ON (user.userid = subscriptionlog.userid)
WHERE subscriptionid = 6 AND status = 0 AND usergroupid = 2

Now out of this group I need to find ONLY the users who have NOT had subscriptionid = 1.

Basically, if there userid exists in subscriptionlog more than once (indicating more than one subscription has existed for the user) I don't want the username returned. ONLY users who have had subscriptionid 6 and NOT 1.

Any help would be greatly appreciated!

[ThorstenA]

For sql queries, you'll find good answers in this forum:

http://www.vbulletin.org/forum/forumdisplay.php?f=15

This forum is about vBulletin 3.8 Questions, Problems and Troubleshooting on a not so technical basis.

[Falcon Capt]

For sql queries, you'll find good answers in this forum:

http://www.vbulletin.org/forum/forumdisplay.php?f=15

This forum is about vBulletin 3.8 Questions, Problems and Troubleshooting on a not so technical basis.

Thanks. I have seen answers to MySQL queries here and was hoping to possibly receive some helpful info. I have also posted the same question at VB.org.

[Edwin Brown]

I am trying to find users who have had one subscription but not the other.

I am using this query to locate one of the subscription types:

SELECT username
FROM subscriptionlog
LEFT JOIN user ON (user.userid = subscriptionlog.userid)
WHERE subscriptionid = 6 AND status = 0 AND usergroupid = 2

Now out of this group I need to find ONLY the users who have NOT had subscriptionid = 1.

Basically, if there userid exists in subscriptionlog more than once (indicating more than one subscription has existed for the user) I don't want the username returned. ONLY users who have had subscriptionid 6 and NOT 1.

Any help would be greatly appreciated!

This is complex enough that a bit of testing is required, but I would so another self-join. The first join can be inner, and the second a left. I'm not sure if the "status" applied to both joins, but I think so.


SELECT username
FROM subscriptionlog
inner JOIN user AS user1 ON (user.userid = subscriptionlog.userid and subscriptionid = 6 AND user1.usergroupid = 2 and status = 0)
LEFT JOIN user AS user2 ON (user.userid = subscriptionlog.userid subscriptionid = 1 AND user2.usergroupid = 2 and status = 0)
WHERE user2.userid is null;

[ThorstenA]

Wow, that is an amazing first post! Thanks

[Falcon Capt]

SELECT username
FROM subscriptionlog
inner JOIN user AS user1 ON (user.userid = subscriptionlog.userid and subscriptionid = 6 AND user1.usergroupid = 2 and status = 0)
LEFT JOIN user AS user2 ON (user.userid = subscriptionlog.userid subscriptionid = 1 AND user2.usergroupid = 2 and status = 0)
WHERE user2.userid is null;

Will this still require the use of "WHERE" conditional? I noticed you didn't include it.

Should it possibly be:

SELECT username
FROM subscriptionlog
inner JOIN user AS user1 ON (user.userid = subscriptionlog.userid WHERE subscriptionid = 6 AND user1.usergroupid = 2 and status = 0)
LEFT JOIN user AS user2 ON (user.userid = subscriptionlog.userid WHERE subscriptionid = 1 AND user2.usergroupid = 2 and status = 0)
WHERE user2.userid is null;

I will test it when I get home later today. Any further inputis greatly apprciated!

[Kevin Sours]

Will this still require the use of "WHERE" conditional? I noticed you didn't include it.

Should it possibly be:

SELECT username
FROM subscriptionlog
inner JOIN user AS user1 ON (user.userid = subscriptionlog.userid WHERE subscriptionid = 6 AND user1.usergroupid = 2 and status = 0)
LEFT JOIN user AS user2 ON (user.userid = subscriptionlog.userid WHERE subscriptionid = 1 AND user2.usergroupid = 2 and status = 0)
WHERE user2.userid is null;

I will test it when I get home later today. Any further inputis greatly apprciated!

WHERE isn't used in JOIN clauses at all (that's not a valid query). ON essentially takes the place of WHERE in the join.
Kevin

[Falcon Capt]

This is complex enough that a bit of testing is required, but I would so another self-join. The first join can be inner, and the second a left. I'm not sure if the "status" applied to both joins, but I think so.


SELECT username
FROM subscriptionlog
inner JOIN user AS user1 ON (user.userid = subscriptionlog.userid and subscriptionid = 6 AND user1.usergroupid = 2 and status = 0)
LEFT JOIN user AS user2 ON (user.userid = subscriptionlog.userid subscriptionid = 1 AND user2.usergroupid = 2 and status = 0)
WHERE user2.userid is null;

OK, got a couple errors...

When using this query:

SELECT user
FROM subscriptionlog
inner JOIN user AS user1 ON (user.userid = subscriptionlog.userid AND subscriptionid = 6 AND user1.usergroupid = 2 AND status = 0)
LEFT JOIN user AS user2 ON (user.userid = subscriptionlog.userid AND subscriptionid = 1 AND user2.usergroupid = 2 AND status = 0)
WHERE user2.userid is null;

I got this error:

An error occurred while attempting to execute your query. The following information was returned.
error number: 1054
error desc: Unknown column 'user' in 'field list'

When I changed the query to this:

SELECT *
FROM subscriptionlog
inner JOIN user AS user1 ON (user.userid = subscriptionlog.userid AND subscriptionid = 6 AND user1.usergroupid = 2 AND status = 0)
LEFT JOIN user AS user2 ON (user.userid = subscriptionlog.userid AND subscriptionid = 1 AND user2.usergroupid = 2 AND status = 0)
WHERE user2.userid is null;

I got this error:

An error occurred while attempting to execute your query. The following information was returned.
error number: 1054
error desc: Unknown column 'user.userid' in 'on clause'

Any help would be greatly appreciated!

[Falcon Capt]

Anyone have any updates/other ideas?

[Kevin Sours]

You've aliased both user tables so there is no table "user" in the query... just user1 and user2.
Kevin

[Falcon Capt]

Ok, I am getting closer...

Using this query:

SELECT user1.username
FROM subscriptionlog
inner JOIN user AS user1 ON (user1.userid = subscriptionlog.userid AND subscriptionid = 6 AND status = 0 AND user1.usergroupid = 2)
LEFT JOIN user AS user2 ON (user2.userid = subscriptionlog.userid AND subscriptionid = 1 AND status = 0 AND user2.usergroupid = 2)
WHERE user2.userid is null;

I don't get any errors BUT it isn't returning the proper data. I want ONLY members who have had a subscriptionid=6 that have NOT had a subscriptionid=1 (active or expired). The above query is returning only everyone who has had a subscriptionid=6 that is inactive (regardless if they have had a subscriptionid=1 or not).

I am guessing something minor needs to be adjusted to get the proper data.

Any help is greatly appreciated!

[Kevin Sours]

I think its backwards

try

SELECT user.userid
FROM user JOIN 
  subscriptionlog sl1 ON 
 (user.userid = sl.userid AND sl.subscriptionid = 6 AND sl.status = 0 AND user.usergroupid = 2) LEFT JOIN
  subscriptionlog sl2 ON 
 (user.userid = sl2.userid AND sl2.subscriptionid = 1 AND sl.status = 0 AND user.usergroupid = 2)
WHERE sl2.userid IS NULL

[Falcon Capt]

I think its backwards

try

SELECT user.userid
FROM user JOIN 
  subscriptionlog sl1 ON 
 (user.userid = sl.userid AND sl.subscriptionid = 6 AND sl.status = 0 AND user.usergroupid = 2) LEFT JOIN
  subscriptionlog sl2 ON 
 (user.userid = sl2.userid AND sl2.subscriptionid = 1 AND sl.status = 0 AND user.usergroupid = 2)
WHERE sl2.userid IS NULL

I get the following error:

An error occurred while attempting to execute your query. The following information was returned.
error number: 1054
error desc: Unknown column 'sl.userid' in 'on clause'

[Falcon Capt]

Ok, I THINK I've got it!!!

The above needed a little modification, but it appears to work now using the following query!

[FONT=Courier New]SELECT user.username[/FONT]
[FONT=Courier New]FROM user JOIN [/FONT]
[FONT=Courier New]subscriptionlog sl1 ON [/FONT]
[FONT=Courier New](user.userid = sl1.userid AND sl1.subscriptionid = 6 AND sl1.status = 0 AND user.usergroupid = 2) LEFT JOIN[/FONT]
[FONT=Courier New]subscriptionlog sl2 ON [/FONT]
[FONT=Courier New](user.userid = sl2.userid AND sl2.subscriptionid = 1 AND sl2.status = 0 AND user.usergroupid = 2)[/FONT]
[FONT=Courier New]WHERE sl2.userid IS NULL[/FONT]

Thanks to everyone who offered up their help!

[Falcon Capt]

Ok guys, need a tad more help... The above query did what I needed and returned everyone who had one subscription (subscriptionid = 6) but not the other (subscriptionid = 1).

Now I need a query of everyone who is in Usergroupid = 2 but has not had either of the above subscriptions. (basically their userid isn't included in the subscriptionlog at all)...

I tried the following but it didn't work...

SELECT user.username
FROM user
JOIN subscriptionlog sl1 ON 
(user.userid = sl1.userid AND sl1.subscriptionid = 6 AND sl1.status = 0 AND user.usergroupid = 2)
WHERE sl1.userid IS NULL 
LEFT JOIN subscriptionlog sl2 ON 
(user.userid = sl2.userid AND sl2.subscriptionid = 1 AND sl2.status = 0)
WHERE sl2.userid IS NULL