pull row if ID exists in another table

Question

I built this query to pull all the data from two tables wp_sshow_sales+tickets and live_15 and it works great for that. But I want it to only pull from wp_sshow_sales & tickets if the wp_sshow_sales.saleID is found in wp_sshow_verifys.verifyID

As you can see I've attempted with a IF and a LEFT JOIN, but I can't finger this one out. I getting a error somewhere around that IF statement.

SELECT 
    wp_sshow_sales.saleFirstName as first,
    wp_sshow_sales.saleLastName as last,
    wp_sshow_sales.saleEMail as email,
    wp_sshow_sales.salePPStreet as street,
    wp_sshow_sales.salePPCity as city,
    wp_sshow_sales.salePPState as state,
    wp_sshow_sales.salePPZip as zip,
    wp_sshow_tickets.ticketQty as qty
    IF (wp_sshow_verifys.verifyID IS NULL,TRUE,FALSE) verify
    FROM wp_sshow_sales, wp_sshow_tickets 
    WHERE wp_sshow_sales.saleID = wp_sshow_tickets.saleID
    LEFT JOIN verify ON wp_sshow_sales.saleID = wp_sshow_verifys.verifyID

    UNION ALL
    SELECT
    live_15.firstname as first,
    live_15.lastname as last,
    live_15.email as email,
    live_15.street as street,
    live_15.city as city,
    live_15.state as state,
    live_15.zip as zip,
    live_15.qty as qty
    FROM live_15

    ORDER BY $order ASC

I believe I have another issue with the ON statement... I keep getting a #1054 - Unknown column 'wp_sshow_sales.saleID' in 'on clause' I don't see where I messed that one up. — John Colombo, Aug 06 '15 at 19:21

score 0 · Answer 1 · edited May 23 '17 at 11:43

You have a syntax error on your IF statement because you are missing a comma before that:

SELECT 
    wp_sshow_sales.saleFirstName as first,
    ...
    wp_sshow_tickets.ticketQty as qty, <-----
    IF (wp_sshow_verifys.verifyID IS NULL,TRUE,FALSE) verify
    FROM wp_sshow_sales, wp_sshow_tickets 
    WHERE wp_sshow_sales.saleID = wp_sshow_tickets.saleID
    LEFT JOIN verify ON wp_sshow_sales.saleID = wp_sshow_verifys.verifyID

I guess you will also have an error on your WHERE clause, that should be written after your JOIN statements.

And if you want to pull from wp_sshow_sales & wp_sshow_tickets only if the saleID is found in wp_sshow_verifys, please use INNER JOIN instead of LEFT JOIN.

For more info regarding JOIN's, please check this SO question.

You'll also need to switch your `WHERE` and `LEFT JOIN` clause. It should look like: `FROM ... LEFT JOIN table ON ... WHERE ...` — KaeL, Aug 11 '15 at 01:30

pull row if ID exists in another table

1 Answers1