• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1035
  • Last Modified:

SQL : add WHERE clause to SELECT with INNER JOIN and UNION

I want to add a WHERE clause to result of the following SQL (I use MySQL):

SELECT 'Marcheur' AS 'type',
    transactions.no_transaction,
    CONCAT(marcheurs.civilite, ' ', marcheurs.prenom, ' ', marcheurs.nom) AS 'nom',
    marcheurs.courriel,
    transactions.montant
FROM transactions
INNER JOIN marcheurs ON marcheurs.no_marcheur = transactions.no_marcheur
UNION
SELECT 'Parrain' AS 'type',
    transactions.no_transaction,
    CONCAT(parrains.civilite, ' ', parrains.prenom, ' ', parrains.nom) AS 'nom',
    parrains.courriel,
    transactions.montant
FROM transactions
INNER JOIN parrains ON parrains.no_parrain = transactions.no_parrain
UNION
SELECT 'Don simple' AS 'type',
    transactions.no_transaction,
    CONCAT(dons_simples.civilite, ' ', dons_simples.prenom, ' ', dons_simples.nom) AS 'nom',
    dons_simples.courriel,
    transactions.montant
FROM transactions
INNER JOIN dons_simples ON dons_simples.no_don = transactions.no_don

I tried but I always get:
Every derived table must have its own alias

The WHERE clause I need is something like
WHERE no_transaction = 1 AND 'type' = "foo" AND nom = "bar" AND courriel = "a@a.com"
0
mychel_normandeau
Asked:
mychel_normandeau
1 Solution
 
ee_rleeCommented:
try this
SELECT * 
FROM
(SELECT 'Marcheur' AS 'type',
    transactions.no_transaction,
    CONCAT(marcheurs.civilite, ' ', marcheurs.prenom, ' ', marcheurs.nom) AS 'nom',
    marcheurs.courriel,
    transactions.montant
FROM transactions
INNER JOIN marcheurs ON marcheurs.no_marcheur = transactions.no_marcheur
UNION
SELECT 'Parrain' AS 'type',
    transactions.no_transaction,
    CONCAT(parrains.civilite, ' ', parrains.prenom, ' ', parrains.nom) AS 'nom',
    parrains.courriel,
    transactions.montant
FROM transactions
INNER JOIN parrains ON parrains.no_parrain = transactions.no_parrain
UNION
SELECT 'Don simple' AS 'type',
    transactions.no_transaction,
    CONCAT(dons_simples.civilite, ' ', dons_simples.prenom, ' ', dons_simples.nom) AS 'nom',
    dons_simples.courriel,
    transactions.montant
FROM transactions
INNER JOIN dons_simples ON dons_simples.no_don = transactions.no_don) T
WHERE no_transaction = 1 AND 'type' = "foo" AND nom = "bar" AND courriel = "a@a.com"

Open in new window

0
 
mychel_normandeauAuthor Commented:
Damn when I tried I used
AS T
instead of only
T

:)
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Tackle projects and never again get stuck behind a technical roadblock.
Join Now