Why does this not have a Unique Table Alias?

simona6

Hi there. We are creating a website for people to contribute to advertising.
Part of this process is for companies to state by which means they will 'Pay' for the job to be done, be it via Samples, Vouchers etc.

So we have setup a table that lists all the Payment Method options.
Admin can select which of those to be assigned the the Campaign.
Users can select which of those they prefer to use (one or more).

When Admins select the Campaign, they are shown all the Users from whom to select to 'run' the campaign, but it needs only show those who have selected which one they want to use. ie. Campaign 1 is for Vouchers, but not all Users will have said they want to be paid in vouchers - so it will only show those who HAVE chosen it,.

This is where our problem begins. So I am doing an SQL query that INNER JOINs these tables, so that when it comes to finding the users.... it finds them and asks if they have selected those options and then shows or doesn't show them in the list.

Bit like a CSS display:none. The initial query is a bit of a beast, so it's easier to - once the users are rendering - ask (do we need to show you).

This is the query so far:

$querypaymethod = ("SELECT `interest-promotions.id`, `interest-promotions.promotion` AS promotion, `promotion-influencers.userid`, `promotion-influencers.promoid` FROM `promotion-influencers` INNER JOIN `promotion-influencers` ON `promotion-influencers.promoid` = `interest-promotions.id` WHERE `promotion-influencers.userid` =:userid");

The problem is, I'm getting the infamous: #1066 - Not unique table/alias: 'promotion-influencers'.

Any ideas?

(Added [code]...[/code] tags ~ MOD)

NogDog

I think you meant to join on interest-promotions rather than promotion-influencers (i.e. joining to itself)?

PS: I'm a big fan of newlines in your SQL...so much easier to read. 🙂

SELECT
  `interest-promotions.id`,
  `interest-promotions.promotion` AS promotion,
  `promotion-influencers.userid`,
  `promotion-influencers.promoid`
FROM `promotion-influencers`
INNER JOIN `interest-promotions` ON `promotion-influencers.promoid` = `interest-promotions.id`
WHERE `promotion-influencers.userid` =:userid"

simona6

We are writing another one now, and the same issue with different tables. I've tried it every which way. What does that error mean tho?

SELECT users.id AS id, users.usertype AS usertype, users.username AS username, users.firstname AS firstname, users.lastname AS lastname, users.instagram AS instagram, users.facebook AS facebook, users.twitter AS twitter, users.pinterest AS pinterest, users.youtube AS youtube, gigsassigned.userid AS userid, gigsassigned.gigid AS gigid FROM users INNER JOIN users users.id = gigsassigned.userid WHERE users.usertype = 'influencer'

NogDog

simona6
Same thing, you are selecting from users and then joining on users when you meant to join on gigsassigned. Therefore the ON clause of the join (for which you omitted the "ON") doesn't make sense to the parser, since neither of the joined tables is named gigassigned.